Connection Attempt Summary

These queries work with iQSonar releases up to and including Fu R3.
They do NOT work with iQSonar Gwynn R1 or later due to changes to the history.t_ConnectionHistory table.

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

A very detailed set of results showing the outcome (success/failure) for each credential broken down by protocol/ip address can be achieved with the following query. The date range helps narrow the results to a specific scan or set of scans.

Detailed breakdown for credential results

SELECT c.label as Credential
      , ch.IPAddress
	  , count(c.label) as attempts
	  , p.Name AS Protocol
      , o.Name AS Outcome
FROM history.t_connectionhistory ch
  INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
  INNER JOIN config.t_Protocol p ON ch.ProtocolID = p.ProtocolID
  INNER JOIN config.t_Outcome o ON o.OutcomeID = ch.OutcomeID
  INNER JOIN config.t_Credential c ON c.CredentialID = CH.CredentialID 
WHERE 
-- === Set the date range as needed ==== --
	( ch.AttemptDate >= 'YYYY-MM-DD'
	  AND
	  ch.AttemptDate <= 'YYYY-MM-DD'
	)
-- === ============================ ==== --
	AND
-- === Change this line ==== --
    jobProj.ProjectID = 1
-- ========================= --
GROUP BY IPAddress, c.label, p.Name, o.Name
ORDER BY IPAddress,Outcome