Ask A Question

Questions

1

Trying to export "clean" assets

Hi, I am trying to export data via SQL, everything seems to work fine except for the "clean" assets which always outputs to 0. Does severity level = Clean exist?. Please find below my SQL export query: WITH site_asset_vulns AS ( SELECT site_id, asset_id, vulnerability_id FROM fact_asset_vulnerability_finding RIGHT OUTER JOIN dim_asset USING (asset_id) JOIN dim_site_asset USING (asset_id) ORDER BY asset_id ), site_unique_vulns AS ( SELECT site_id, COUNT(DISTINCT vulnerability_id) AS unique_vulnerabilities FROM site_asset_vulns GROUP BY site_id ), site_asset_severity AS ( SELECT site_id, asset_id, severity AS max_severity FROM site_asset_vulns LEFT OUTER JOIN dim_vulnerability USING (vulnerability_id) GROUP BY site_id, asset_id, severity ORDER BY asset_id ), site_assets_by_severity AS ( SELECT site_id, SUM(CASE WHEN max_severity = 'Critical' THEN 1 ELSE 0 END) AS critical_assets, SUM(CASE WHEN max_severity = 'Severe' THEN 1 ELSE 0 END) AS severe_assets, SUM(CASE WHEN max_severity = 'Moderate' THEN 1 ELSE 0 END) AS moderate_assets, SUM(CASE WHEN max_severity = 'Clean' THEN 1 ELSE 0 END) AS clean_assets FROM site_asset_severity GROUP BY site_id ) SELECT ds.name AS "Site Name", assets AS "Assets", vulnerabilities AS "Total Vulns", uv.unique_vulnerabilities AS "Total Unique Vulns", critical_vulnerabilities AS "High", severe_vulnerabilities AS "Medium", moderate_vulnerabilities AS "Low", critical_assets AS "Nodes by Severity High", severe_assets AS "Nodes by Severity Medium", moderate_assets AS "Nodes by Severity Low", clean_assets AS "Clean Assets" FROM fact_site JOIN site_unique_vulns uv USING (site_id) JOIN site_assets_by_severity USING (site_id) JOIN dim_site ds USING (site_id) ORDER BY ds.name ASC

Posted by eden 4 days ago