Knowledge Base

Ask A Question

Questions

2

SQL severity count

Looking for help on a SQL query. I am looking to get a count of critical, severe and moderate vulns on an asset as well as total vulns. I have been successful with the getting the count of total vulns with a simple count vulnerability_id from dim_vulnerability however the severity is a problem. Since the severity is stores in dim_vulnerability table severity I can not find a way to break out a count of each specific criticality. Below is my attempt at a SQL query but I feel like it may be way too jacked up for fixing. WITH critical_vuln_count AS ( SELECT COUNT dv.severity FROM fact_asset_date('2018-09-01', current_date, INTERVAL '1 year')fad LEFT OUTER JOIN dim_asset as da on fad.asset_id = da.asset_id LEFT OUTER JOIN fact_asset_vulnerability_age as fava on fava.asset_id = da.asset_id LEFT OUTER JOIN dim_vulnerability as dv on dv.vulnerability_id = fava.vulnerability_id WHERE now() - dv.date_published > INTERVAL '30 days' AND dv.severity = 'Critcal' ) SELECT da.ip_address as "IP Address", da.host_name as "Host Name", dos.description AS "Operating System", fad.day as "Date of Summary", COUNT (dv.title) as "Total_Vulnerabilities", cvc.critical_vuln_count FROM fact_asset_date('2018-09-01', current_date, INTERVAL '1 year')fad LEFT OUTER JOIN dim_asset as da on fad.asset_id = da.asset_id LEFT OUTER JOIN dim_operating_system as dos on da.operating_system_id = dos.operating_system_id LEFT OUTER JOIN fact_asset_vulnerability_age as fava on fava.asset_id = da.asset_id LEFT OUTER JOIN dim_vulnerability as dv on dv.vulnerability_id = fava.vulnerability_id LEFT OUTER JOIN critical_vuln_count as cvc using (severity) WHERE now() - dv.date_published > INTERVAL '30 days' GROUP BY "IP Address", "Host Name", "Operating System", "Date of Summary", "critical_vuln_count" I know that there is a table that naturally has this data but in my experience it looks like those are raw values and and are not subject to where parameters such as date published > 30 days.

Posted by Robert DeBellis about a year ago