Finding failed authentications for assets accross all sites

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 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, 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, da.ip_address
1 Like

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?
Thank you