Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

In Gilead we have a situation where the device diagnostic screens don't load correctly.


Following SQL query has been provided by Mike Wheelan to aid in target troubleshooting while we fix the underlying problem

This query is intended to be run against an Elcano R2 database


Target Remediation SQL
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
ORDER BY
     fd.FoundDeviceID
    ,ch.AttemptDate


  • No labels