SQL example - asset IP summary

This example query provides:

  • Asset IP Address
  • Asset Names
  • Asset Risk Score
  • Asset Criticality
  • Vulnerability Risk Score
  • Vulnerability Severity Level
  • Exploit Count
  • Malware Kit Count
SQL
1
SELECT da.asset_id AS "Asset ID", da.ip_address AS "IP Address", da.host_name AS "Host Name", dos.description AS "Operating System",fa.critical_vulnerabilities AS "Critical Vulnerabilities", fa.severe_vulnerabilities AS "Severe Vulnerabilities",
2
fa.moderate_vulnerabilities AS "Moderate Vulnerabilities", fa.vulnerabilities AS "Total Vulnerabilities",fa.malware_kits AS "Malware Kits", fa.exploits AS "Exploits",
3
to_char(round(fa.riskscore::numeric,0),'999G999G999') AS "Risk Score"
4
FROM dim_asset da
5
JOIN fact_asset fa USING (asset_id)
6
JOIN dim_operating_system dos USING (operating_system_id)
7
ORDER BY da.host_name