Find list of unscanned ESX servers

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.

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.

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