A "Found Device" is a device which the scan engine has been unable to fully scan. This can be due to credential problems or network problems.
This query will list all the devices which have been detected but not fully scanned, and provide additional information about them for troubleshooting.
List devices and summary info
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | List the devices we cannot scan |
|---|
|
SELECT [ProjectID]
,[TargetName]
,[HostnameOrIP]
,[SuspectedOrScannedOS]
,[Status]
,[Reason]
,[FoundDevices]
,[Devices]
,[FoundApplications]
,[Applications]
,[FoundDeviceID]
,[DeviceID]
,[Scancount]
,[TargetSet]
FROM [history].[v_DiagnosticsDeviceList]
WHERE Status = 'Unscanned'
-- == Change or comment out the ProjectID as required == --
AND ProjectID = 1
-- ===================================================== -- |
| Warning |
|---|
The two queries below work for Scan Engine versions up to Fu R3, but do not work for Gwynn R1 |
List all connection attempts
It lists multiple entries for each device (one for each attempted connection to the target), showing which connections succeeded and which failed.
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | List Found Devices (full details) |
|---|
|
SELECT
fd.FoundDeviceID
,fd.DeviceID
,fd.SuspectedOS
,fd.Reason
,prd.Name [product_type]
,fdP.Port
,fdP.Banner
,fdP.IPAddress
,fdL.FoundHostnameOrIP
,fdL.DetectedPorts
,fdL.ScanCount
,fdL.LastScanDate
,fdL.LatestOutcome
,fdL.ScannedDeviceName
,ch.AttemptDate
,ch.Port
,ch.[Message]
,ch.[RawMessage]
,ch.Connection
,oc.Name [Outcome]
,pro.[Name] [Protocol]
FROM model.t_FoundDevice fd
INNER JOIN model.t_product prd ON fd.ProductId = prd.ProductID
LEFT OUTER JOIN model.v_FoundDeviceTCPPort fdP ON fd.FoundDeviceID = fdP.FoundDeviceID
LEFT OUTER JOIN model.v_ProjectFoundDeviceList fdL ON fd.foundDeviceID = fdL.foundDeviceID
LEFT OUTER JOIN
(
SELECT
ah.ObjectId
,ah.JobID
FROM history.t_ArtifactHistory ah
WHERE
ah.objectType = N'FoundDevice'
)
[hst] ON fd.FoundDeviceID = [hst].ObjectID
LEFT OUTER JOIN history.t_ConnectionHistory ch ON [hst].JobId = ch.JobId
LEFT OUTER JOIN config.t_Outcome oc ON ch.OutcomeID = oc.OutcomeID
LEFT OUTER JOIN config.t_Protocol pro ON ch.ProtocolId = pro.protocolId
-- ---- If required, put a ProjectID selection HERE ----
-- WHERE fdL.ProjectID = 6N
-- ----------------------------------------------- ----
ORDER BY
fd.FoundDeviceID
,ch.AttemptDate |
List hosts where we could not log in
This omits any credential failure where a subsequent attempt on the same host succeeded.
| Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | List of devices we could not scan with reason |
|---|
|
WITH unscanned AS (
SELECT DISTINCT [HostnameOrIP]
FROM [history].[v_DiagnosticsDeviceList]
WHERE Status = 'Unscanned'
AND ProjectID = 1
)
SELECT jobProj.ProjectID
, p.Name AS Protocol
, o.Name AS Outcome
, ch.AttemptDate
, ch.IPAddress
, ch.Port
, c.label as Credential
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 |
