Nexpose SQL Query Help

Hello - I’m looking for help with an SQL Query on nexpose. This is for on-prem and not InsightVM. I was sent here by support as they won’t even bother with it.

I’m having a difficult time pulling credential_status from dim_asset_service. Basically, I want to see this type of output.

This is my query, I’m missing Assessed, Last Scan, Scan Status, Scan Engine, Authentication.
SELECT da.ip_address AS “Address”, da.host_name AS “Name”, da.sites AS “Site”, AS “Operating System”, fa.exploits AS “Exploits”, fa.malware_kits AS “Malware”, fa.vulnerabilities AS “Vulnerabilities”, fa.riskscore AS “Risk”, to_char(da.last_assessed_for_vulnerabilities, ‘DD/MM/YYYY’)AS “Last Scan”
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN fact_asset fa USING (asset_id)

Hi @jay,

The following is probably the closest that you’ll be able to get with a SQL query. The scan engine listed is the default for the site, not the individual one that scanned the asset during the scan. Hope it helps!

WITH max_scan AS (
    SELECT fa.asset_id,
           MAX(das.scan_id) AS scan_id,
    FROM fact_asset fa
             JOIN dim_asset_scan das on fa.asset_id = das.asset_id
    GROUP BY fa.asset_id, fa.exploits, fa.malware_kits, fa.vulnerabilities, fa.riskscore,
             fa.aggregated_credential_status_id, das.scan_finished

SELECT da.asset_id,
       da.ip_address                                                   AS "Address",
       da.host_name                                                    AS "Name",
       da.sites                                                        AS "Site",                                                        AS "Operating System",
       ms.exploits                                                     AS "Exploits",
       ms.malware_kits                                                 AS "Malware",
       ms.vulnerabilities                                              AS "Vulnerabilities",
       ms.riskscore                                                    AS "Risk",
       to_char(da.last_assessed_for_vulnerabilities, 'DD / MM / YYYY') AS "Last Scan",
       dacs.aggregated_credential_status_description                   AS "Credential Status",                                                        AS "Site Name",
       ms.scan_id                                                      AS "Last Scan ID",
       ms.scan_finished                                                AS "Scan Finished Time",
       dss.description                                                 AS "Scan Status",                                                        AS "Default Scan Engine Name",
       dse.address                                                     AS "Default Scan Engine Address"
FROM max_scan ms
         JOIN dim_asset da ON ms.asset_id = da.asset_id
         JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
         JOIN dim_scan ds ON ms.scan_id = ds.scan_id
         JOIN dim_scan_status dss ON ds.status_id = dss.status_id
         JOIN dim_site_scan dss2 ON ms.scan_id = dss2.scan_id
         JOIN dim_site ds2 ON dss2.site_id = ds2.site_id
         JOIN dim_site_scan_config dssc ON dss2.site_id = dssc.site_id
         JOIN dim_scan_engine dse ON dssc.scan_engine_id = dse.scan_engine_id
         JOIN dim_aggregated_credential_status dacs
              ON dacs.aggregated_credential_status_id = ms.aggregated_credential_status_id

Tyler - Thank you. This is great and it got me what I was looking for. I can definitely expound on it. I appreciate this.

1 Like