Find all credential problems

The previous pages give a list of individual credential failure categories for Unix targets, Windows targets and for Database Applications.

For a smaller project, or one with comparatively few credential failures, the following query might be useful. OutcomeID 3 is Credential Failure

Find all credential failures

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 = 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)

Successful Login attempts

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