...
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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.
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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
|