...
For a smaller project, or one with comparatively few credential failures, the following query might be useful. OutcomeID 3 is Credential Failure
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT jobProj.ProjectID
, p.Name AS Protocol
, o.Name AS Outcome
, ch.AttemptDate
, ch.IPAddress
, ch.Port
, c.label as Credential
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 ch.OutcomeID = 3
-- === Change this line ==== --
AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC |
Conversely, you may want to see which credentials succeeded for a given project. In this case you're interested in OutcomeID 1 (successful login)
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT jobProj.ProjectID , c.label as Credential , p.Name AS Protocol , o.Name AS Outcome , ch.IPAddress , ch.Port , ch.AttemptDate 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 ch.OutcomeID = 1 -- === Change this line ==== -- AND jobProj.ProjectID = 1 -- ========================= -- ORDER BY AttemptDate DESC |
...