...
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 | ||||||
|---|---|---|---|---|---|---|
| ||||||
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
-- ===================================================== -- |
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 | ||||||
|---|---|---|---|---|---|---|
| ||||||
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 = N
-- ----------------------------------------------- ----
ORDER BY
fd.FoundDeviceID
,ch.AttemptDate |
...