...
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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.
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | 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 |