...
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 These queries 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 tableChanges 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. |
| 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 |
...