SQL Query Example: Group by CVSS Severity and Split Hostname

Document created by synapsr on Feb 6, 2015
Version 1Show Document
  • View in full screen mode

In cases where you need to present vulnerabilities grouped by CVSS severity, this SQL query can help.  It also splits the hostname.  Yeap, there is a query for that.

 

 

 

SELECT

da.ip_address AS "IP",

split_part(da.host_name, ',', 1) AS "Name 1",

split_part(da.host_name, ',', 2) AS "Name 2",

split_part(da.host_name, ',', 3) AS "Name 3",

dos. NAME AS "OS",

dv.title AS "Title",

CASE

WHEN dv.cvss_score = 10 THEN

'Critical'

WHEN dv.cvss_score BETWEEN 7

AND 9.9 THEN

'High'

WHEN dv.cvss_score BETWEEN 6.9

AND 4 THEN

'Medium'

WHEN dv.cvss_score BETWEEN 3.9

AND 1 THEN

'Low'

WHEN dv.cvss_score = 0 THEN

'Informational'

END AS "Severity",

dv.description AS "Description",

ds.estimate AS "Time to Fix",

ds.fix AS "Solution",

dvr.reference AS "Reference",

fasvi.proof AS "Proof",

fava.age_in_days AS "Age In Days"

FROM

fact_asset_vulnerability_instance AS fasvi

JOIN fact_asset_vulnerability_age AS fava ON fasvi.vulnerability_id = fava.vulnerability_id

JOIN dim_vulnerability AS dv ON fasvi.vulnerability_id = dv.vulnerability_id

JOIN dim_asset AS da ON fasvi.asset_id = da.asset_id

JOIN dim_vulnerability_reference AS dvr ON dv.vulnerability_id = dvr.vulnerability_id

JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id

JOIN dim_asset_vulnerability_solution AS davs ON davs.asset_id = da.asset_id

JOIN dim_solution AS ds ON ds.solution_id = davs.solution_id

1 person found this helpful

Attachments

    Outcomes