Find all credential problems

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.

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