In a similar vein to queries to finding/diagnosing Windows Servers and Unix servers, the following queries may help a customer to find/diagnose ESX servers that have not been scanned.
List all unscanned ESX servers
SELECT TargetName
, HostnameOrIP
, SuspectedOrScannedOS
, FoundDeviceID
, Reason
, TargetSet
FROM [history].[v_DiagnosticsDeviceList]
WHERE SuspectedOrScannedOS='ESX'
AND Status='Unscanned'
A slightly more complex query to list ESX servers discovered by a specific project, and the credentials which failed to log in to them follows. As with other scripts - by modifying the "ProjectID" clause, you can filter per project.
This query work with iQSonar releases up to and including Fu R3.
It does NOT work with iQSonar Gwynn R1 or later due to changes to the history.t_ConnectionHistory table.
Unscanned ESX servers per project
WITH unscanned AS (
SELECT DISTINCT [HostnameOrIP]
FROM [history].[v_DiagnosticsDeviceList]
WHERE Status = 'Unscanned'
-- === Change this line ==== --
AND ProjectID = 1
-- ========================= --
AND SuspectedOrScannedOS='ESX' -- explicitly include only targets we think are running ESX
)
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.OutcomeID = 3
AND ch.IPAddress IN (SELECT HostnameOrIP FROM unscanned)
-- === Change this line ==== --
AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC