SELECT da.host_name, da.ip_address, dos.family, dos.description, dv.title, dv.nexpose_id, round(dv.cvss_score::numeric, 2) AS cvss_score,
dv.severity, fapr.proof, ds.summary, dvc.category_name
FROM dim_asset da
LEFT JOIN dim_operating_system dos USING(operating_system_id)
JOIN fact_asset_vulnerability_finding favf ON da.asset_id=favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id=dv.vulnerability_id
JOIN fact_asset_policy_rule fapr ON da.asset_id=fapr.asset_id
JOIN dim_asset_vulnerability_best_solution dvbs ON favf.asset_id=dvbs.asset_id AND favf.vulnerability_id=dvbs.vulnerability_id
JOIN dim_solution ds USING(solution_id)
ORDER BY “cvss_score” DESC
So far so good but I can’t figure out how to input the vuln category, I am trying to pull it form dim_vulnerability_category but I simply don’t understand how to pull category_name out of it.
It looks like you’re on the right track with your query. I believe what you’d need to do is add a JOIN statement for the dim_vulnerability_category table, and then that’ll allow you to select the dvc.category_name that you already have up top. It could be something like:
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
It can also be good to add WHERE clauses to try to limit the vulnerabilities being returned with reports like these. Filtering by something like severity can help make sure you’re not hit with an overwhelming number of them.
Thank you @holly_wilsey ! That was perfect. I am still learning the structure here for SQL queries, I was basically digging through my previous colleagues report and trying to figure it out on my own.
Simply question, is there an OVERDUE field to report on?
I hear you about limiting the data, I am talking to the owner now to see what she wants to report on - as of right now it’s set to all vulnerabilities with no exceptions. I would imagine over 10K assets that would take a long time to run.
I’m not aware of an overdue field offhand, but there may be another way to get what you’re looking for. What were you looking to do with it in the query exactly?
If you want to do some searching through the tables that exist, you can check out the reporting data model here and here. These are all the tables and fields that you can use in your queries. It’s helpful if you’re trying to figure out what data is available.
It seems the report own wants to know if the vuln is overdue from a scheduling perspective. I.E. we create a goal to address Vuln#1 by DateX and if we were successful - or if it wasn’t successful therefore Overdue.
Seems complex and I feel R7 isn’t the best way to report this so I don’t feel it’s a road I will go down.
Nonetheless, thank you @holly_wilsey !
I see what you mean. I’m not aware of a way to retrieve that for a custom SQL report, but I know you can do a CSV export of assets + vulnerabilities under a specific goal. If you go that route, the report contains details about the vuln along with the affected hosts, as well as whether it’s currently compliant. I don’t believe that export contains the due date, but it might be little closer to what you’re looking for.
Best way to determine if something is “overdue” is to measure the age and compare to your internal SLA requirements. You can do this from either the date field in fact_asset_vulnerability_finding table or date_published/date_modified in the dim_vulnerability table. Using the age function outputs the days as measured from the time the query was run, i.e. now.
round(age(fa.date, 'days'::text), 0) AS vulnerability_age
FROM fact_asset_vulnerability_finding
You can then add your sla_targets and compare those to the age to get your “overdue” or past SLA findings.
CASE
WHEN severity = 'Medium'::text AND os_system = 'CentOS Linux'::text THEN 120
WHEN severity = 'High'::text AND os_system = 'CentOS Linux'::text THEN 90
WHEN severity = 'Critical'::text AND os_system = 'CentOS Linux'::text THEN 60
WHEN severity = 'Medium'::text THEN 90
WHEN severity = 'High'::text THEN 60
WHEN severity = 'Critical'::text THEN 30
ELSE NULL::integer
END AS sla_target
CASE
WHEN vulnerability_age <= sla_target::numeric THEN 'Within SLA'::text
WHEN vulnerability_age > sla_target::numeric THEN 'Past SLA'::text
ELSE 'No SLA'::text
END AS risk_based_sla
These are just snippets from some of my queries. I might be able to add them to your original query when I get some time later. Hope this helps though.
