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”, dos.name 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)
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,
fa.exploits,
fa.malware_kits,
fa.vulnerabilities,
fa.riskscore,
fa.aggregated_credential_status_id,
MAX(das.scan_id) AS scan_id,
das.scan_finished
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",
dos.name 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",
ds2.name AS "Site Name",
ms.scan_id AS "Last Scan ID",
ms.scan_finished AS "Scan Finished Time",
dss.description AS "Scan Status",
dse.name 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