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
Wow. This is exactly what I needed! Thank you so much! Beautiful!
It returns duplicated assets. How can you deal with last scan date to ensure there is no duplicate assets?