Hello, we are using on-prem Nexpose. We have a number of assets in a number of sites. They are configured to do authenticated vulnerability scans. Sometimes assets do not authenticate and there is no clear indication of this problem in the console. I’m trying to figure out how I can get a listing of all assets across several sites whom failed authentication in their last scan. Is there a way I can grab this from within Nexpose? Is it possible to generate a report? If not, can you point me in the right direction on how I can fetch this list programmatically?
Below is my favorite SQL query for verifying creds across multiple sites or DAGs. The SQL query option is an export report on the report tab, just paste in the following and see if the results suite your needs. Note: it will add a row based on the ports found, so most IP’s/Assets will show up more than once. Also note that the ‘Certainty’ is your best column for success, a 1.0 means 100% success, where a .85 would mean 85%, or usually means that you were able to log in with GUEST access:
SELECT dsite.name as site, da.ip_address, da.host_name, dos.description as OS, os.certainty_max, dcs.credential_status_description, dasc.port, TO_CHAR(fa.scan_started, 'MM-DD-YYYY') AS date, fa.last_scan_id, dos.asset_type,
CASE WHEN certainty_max = '1' then 'YES'
ELSE 'NO'
END AS authenticated_scan,
fa.vulnerabilities, fa.malware_kits, fa.exploits
FROM fact_asset AS fa
JOIN dim_asset da ON da.asset_id = fa.asset_id
JOIN (
SELECT asset_id, MAX(daos.certainty) as certainty_max
FROM dim_asset_operating_system AS daos
GROUP BY asset_id
) os ON fa.asset_id = os.asset_id
JOIN dim_operating_system as dos
ON da.operating_system_id = dos.operating_system_id
JOIN dim_site_asset as dsa
ON fa.asset_id = dsa.asset_id
JOIN dim_site as dsite ON dsa.site_id = dsite.site_id
JOIN dim_asset_service_credential dasc on fa.asset_id = dasc.asset_id
JOIN dim_credential_status dcs ON dcs.credential_status_id = dasc.credential_status_id
GROUP BY dsite.name, da.ip_address, da.host_name,dos.description, os.certainty_max, dcs.credential_status_description, dasc.port, fa.vulnerabilities, fa.malware_kits, fa.exploits, fa.scan_started, fa.last_scan_id, dos.asset_type
ORDER BY dsite.name, da.ip_address
2 Likes
Wow. This is exactly what I needed! Thank you so much! Beautiful!
1 Like
It returns duplicated assets. How can you deal with last scan date to ensure there is no duplicate assets?
Thank you
Hi,
This was a great find for us. But what does it mean if certainty is 100% but Credential Status is Failed. Does that mean it is 100% sure that the credentials failed. Is there a way of adding what credentials it tried to use to the list.
Thanks,
Nic