Connection Attempt Summary

When investigating why iQSonar was unable to connect to certain target hosts or applications, it can be important to know whether certain credentials worked or not, and also whether certain hosts responded to the login attempt at all, or if the connection attempt failed due to other reasons.

Connection Attempt Summary for project

WITH outcomes AS (
	select OutcomeID, Count(OutcomeID) as times
	FROM history.t_connectionhistory ch
	INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
	WHERE 
	-- === Change this line ==== --
	    jobProj.ProjectID = 1
	-- ========================= --
	GROUP BY OutcomeID
)
SELECT outcomes.OutcomeID, outcomes.times, o.Name
FROM 
    outcomes INNER JOIN config.t_Outcome o ON outcomes.OutcomeID = o.OutcomeID
ORDER BY outcomes.OutcomeID

A more detailed set of results, showing success/failure per credential can be obtained as follows:

Summary of outcomes per credential

WITH outcomes AS (
	select CredentialID, OutcomeID, Count(OutcomeID) as times
	FROM history.t_connectionhistory ch
	INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
	WHERE 
	-- === Change this line ==== --
	    jobProj.ProjectID = 1
	-- ========================= --
	GROUP BY CredentialID, OutcomeID
)
SELECT c.Label AS [Credential Name]	  
	  , o.Name as Outcome
	  , outcomes.times
FROM 
    outcomes INNER JOIN config.t_Outcome o ON outcomes.OutcomeID = o.OutcomeID
	         INNER JOIN config.t_Credential c ON outcomes.CredentialID = c.CredentialID
ORDER BY [Credential Name], outcomes.OutcomeID