SQL example - group by CVSS severity and split hostname
In cases where you need to present vulnerabilities grouped by CVSS severity, this example query can serve that purpose. It also splits the hostname.
sql
1SELECT23da.ip_address AS "IP",45split_part(da.host_name, ',', 1) AS "Name 1",67split_part(da.host_name, ',', 2) AS "Name 2",89split_part(da.host_name, ',', 3) AS "Name 3",1011dos. NAME AS "OS",1213dv.title AS "Title",1415CASE1617WHEN dv.cvss_score = 10 THEN1819'Critical'2021WHEN dv.cvss_score BETWEEN 72223AND 9.9 THEN2425'High'2627WHEN dv.cvss_score BETWEEN 6.92829AND 4 THEN3031'Medium'3233WHEN dv.cvss_score BETWEEN 3.93435AND 1 THEN3637'Low'3839WHEN dv.cvss_score = 0 THEN4041'Informational'4243END AS "Severity",4445dv.description AS "Description",4647ds.estimate AS "Time to Fix",4849ds.fix AS "Solution",5051dvr.reference AS "Reference",5253fasvi.proof AS "Proof",5455fava.age_in_days AS "Age In Days"5657FROM5859fact_asset_vulnerability_instance AS fasvi6061JOIN fact_asset_vulnerability_age AS fava ON fasvi.vulnerability_id = fava.vulnerability_id6263JOIN dim_vulnerability AS dv ON fasvi.vulnerability_id = dv.vulnerability_id6465JOIN dim_asset AS da ON fasvi.asset_id = da.asset_id6667JOIN dim_vulnerability_reference AS dvr ON dv.vulnerability_id = dvr.vulnerability_id6869JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id7071JOIN dim_asset_vulnerability_solution AS davs ON davs.asset_id = da.asset_id7273JOIN dim_solution AS ds ON ds.solution_id = davs.solution_id
Did this page help you?