...
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
|
...