Zach Turpen

Nexpose SQL Query - Hostnames in Site/Scan with Number of Vulnerabilities By Severity

Discussion created by Zach Turpen on Aug 25, 2015
Latest reply on Aug 25, 2015 by Zach Turpen

After searching the forums for this query and coming up short, I thought I would post my solution in case it helps someone else.  The very simple SQL query below (I'm definitely no expert) will output a list of host names and display the sum of each machine's critical, severe, and moderate vulnerabilities for a given scope.  The scope could be site(s)/scan(s) depending on the scope given within the reporting setup.

 

SELECT da.host_name, fa.critical_vulnerabilities, fa.severe_vulnerabilities, fa.moderate_vulnerabilities
FROM fact_asset fa, dim_asset da
WHERE fa.asset_id = da.asset_id;

 

Sample Output:

 

host_namecritical_vulnerabilitiessevere_vulnerabilitiesmoderate_vulnerabilities

host1

3

6

13

host2

1

5

8

 

Please let me know if this does or does not work for you, again I'm no SQL expert and appreciate the feedback.

Outcomes