Find hosts with Windows login failures

Owned by Former user (Deleted)
Last updated: Sept 25, 2018

List Windows hosts which we can not log into

The following query gives a list of Windows hosts which we are unable to log in to. This should work with all versions of iQSonar.

Find windows hosts with credential errors
SELECT p.Name as Project
     , TargetName
	 , HostnameOrIP
	 , SuspectedOrScannedOS
	 , Status
	 , Reason
FROM [history].[v_DiagnosticsDeviceList] vDDL
  INNER JOIN config.t_Project p ON vDDL.ProjectID = p.ProjectID
WHERE Reason = 'Access Denied'
  AND vDDL.ProjectID in (1,2,3)
  AND SuspectedOrScannedOS Like '%Windows%'

Additional Details - show which credentials failed

The above query showed which hosts we cannot log in to. It does not show what the credentials we tried are. The following queries will show which credentials were tried per host.

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.


iQSonar uses multiple protocols to connect to targets running various versions of Windows. The different connection types can give different information about the target.

Note that this query will list ALL login failures for a project - helpful when determining if your credentials are in the wrong order as well as finding hosts you cannot log in to. Getting your credentials in the correct order (i.e. minimizing failed login attempts) will reduce the risk of locking out an account and also speed up the scan. To find hosts that can not be scanned for non-credential reasons, this query might help instead.

The connection types are as follows:

  • 3: WMI
  • 4: Remote Registry
  • 13: NetBIOS
  • 15: SMB (file sharing)
  • 16: WinRM
  • 17: Remote Process

Windows connection failure (Per Project)

Find windows connection failures
--Windows Login Failures
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 IN (3,4,13,15,16,17)
  AND ch.OutcomeID = 3 
-- === Change this line ==== --
   AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC

Show all the login failures, but this time limit to hosts we cannot log in to at all -:

Unscanned hosts windows login failures
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 IN (3,4,13,15,16,17)
  AND ch.OutcomeID = 3 
  AND ch.IPAddress IN (SELECT HostnameOrIP FROM unscanned)
-- === Change this line ==== --
   AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC