...
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.
| Warning |
|---|
This query work with iQSonar releases up to and including Fu R3. |
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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 |
...