List hosts we can not log into (All version)
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.
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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 = '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.
| Warning |
|---|
These queries work with iQSonar releases up to and including Fu R3.
They do NOT work with iQSonar Gwynn R1 or later due to changes to the history.t_ConnectionHistory table. |
...
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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
|
Limit Show all the login failures, but this time limit to hosts we cannot log in to at all -:
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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 |
...