| Warning |
|---|
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 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.
...
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.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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
|
...