Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
themeMidnight
titleProtocol 2 is SSH
--SSH Login Failure
SELECT jobProj.ProjectID
      , p.Name AS Protocol
	  , o.Name AS Outcome
	  , ch.AttemptDate
	  , ch.IPAddress
	  , ch.Port
	  , c.label as Credential
	  , ch.Message
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.ProtocolID=2 
  AND ch.OutcomeID = 3 
-- === Change this line ==== --
   AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC

SSH Login Failures only for hosts we could not log in to

Code Block
languagesql
themeMidnight
titleSSH Failures for unscanned hosts
WITH unscanned AS (
	SELECT DISTINCT [HostnameOrIP]
	FROM [history].[v_DiagnosticsDeviceList]
	WHERE Status = 'Unscanned'
  -- === Change this line ==== --
	  AND ProjectID = 1
  -- ========================= --
)
SELECT jobProj.ProjectID
      , p.Name AS Protocol
	  , o.Name AS Outcome
	  , ch.AttemptDate
	  , ch.IPAddress
	  , ch.Port
	  , c.label as Credential
	  , ch.Message
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.ProtocolID =2
  AND ch.OutcomeID = 3 
  AND ch.IPAddress IN (SELECT HostnameOrIP FROM unscanned)
-- === Change this line ==== --
   AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC

...