Connection results per target

These queries work with iQSonar releases up to and including Fu R3.

Changes were made to some underlying tables in Gwynn R1 and Gwynn R2 to reduce the amount of history saved as the diagnostic information can grow. In the Gwynn R3 release the option to preserve or delete this diagnostic can be selected in the user interface. Instructions on how to keep the history data can be found on this page.

When attempting to diagnose why a specific target cannot be scanned, we can query the database to discover the results of connection attempts for that target. It is useful to filter on connection date as well as IP address if the target has had multiple scan attempts.
It may be useful (especially in the absence of Target Logs) when diagnosing a failure to scan a target, to combine the output of these two queries with the command history output query; this will show you all the results obtained from the commands that were attempted.

First, get the results of the Port Scan (where we attempt to find open ports on the target)

You will need to replace the IP address and date in the example query with a valid target IP address and a date on which the target was scanned.

Port Scan Results (By IP)

SELECT ch.IPAddress
	   , o.Name as Outcome
	   , p.Name as Protocol 
	   , ch.Port
	   , Message
	   , convert(date,ch.AttemptDate) as DateAttempted
FROM history.t_ConnectionHistory ch
     INNER JOIN config.t_Outcome o ON o.OutcomeID = ch.OutcomeID
	 INNER JOIN config.t_Protocol p ON ch.ProtocolID = p.ProtocolID
WHERE
-- Protocol 14 is called "TCP" and is the port scan
   p.Name = 'TCP'
   AND
-- == Customize These == --
   IPAddress ='192.168.0.1'
   AND
   CONVERT(date,ch.AttemptDate)='YYYY-MM-DD'
ORDER BY Port

Next, get the results for other connection attempts

Connection Results per IP address

SELECT   ch.IPAddress
	   , o.Name as Outcome
	   , p.Name as Protocol 
	   , ch.Port
	   , Message
	   , convert(date,ch.AttemptDate) as DateAttempted
FROM history.t_ConnectionHistory ch
     INNER JOIN config.t_Outcome o ON o.OutcomeID = ch.OutcomeID
	 INNER JOIN config.t_Protocol p ON ch.ProtocolID = p.ProtocolID
WHERE
-- Protocol 14 is called "TCP" and is the port scan
   p.Name != 'TCP'
   AND-- == Customize These == --
   IPAddress ='192.168.0.1'
   AND
   CONVERT(date,ch.AttemptDate)='YYYY-MM-DD'