Overall Risk Trend

InsightVM SQL Queries
1

Hello all,
We’re working on building out some custom report cards for the company using SQL Query’s. I was able to figure out how to get the Overall Risk Score when I ran a SQL Query Report using the following query -
SELECT
SUM(fa.riskscore) AS overall_risk_score
FROM
dim_asset da
JOIN fact_asset fa ON da.asset_id = fa.asset_id

This is great, but it only gives me what the Overall Risk Score is right now. What I’d like to do, is go back in time and get the Overall Risk Score from different points in time, possibly to recreate the Overall Risk Trend that you typically see when you log in to InsightVM or on the out of the box Exec reports.

I haven’t had any luck looking through the GitHub repo or in the Discussion community so far.

Thank you!

2

Hi,

This doesn’t give you exactly what you want but should get you part way. This works per asset but could be edited to group it by the required fields, categories etc or you could open the file in Excel and do it that way.

It uses the fact_asset_date table - the first criteria field is the start date, the second is the end date and the third is the interval between those two dates to report on. The result is a table that shows the risk score, vuln count etc on each asset for each month back from today for a year.

SELECT
da.host_name,
dos.description,
fad.asset_id,
fad.day,
fad.riskscore,
fad.critical_vulnerabilities,
fad.severe_vulnerabilities,
fad.moderate_vulnerabilities,
fad.malware_kits,
fad.exploits,
fad.vulnerabilities_with_malware_kit,
fad.vulnerabilities_with_exploit
FROM fact_asset_date (current_date - 365, current_date, INTERVAL '1 month') fad
JOIN dim_asset da ON da.asset_id = fad.asset_id
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
3

Thank you so much. That helped quite a bit. This is what I ultimately ended up with. The problem is that I was only saving asset data for 2 months so the numbers are only accurently going back 6-8 weeks. I switched to saving asset data for 18 months now so hopefully that will help. I have to think that that historical overall score is stored somewhere for it to be displayed on the Exec Overall Trend Report and the Overall Risk Trend you see when you first login to the dashboard though. Again thanks for your help!

SELECT
week_of,
asset_count,
sum_vuln/asset_count AS “Vulns_Asset”,
sum_crit/asset_count AS “Crits_Asset”,
sum_exp/asset_count AS “Exp_Asset”,
sum_exp_mal/asset_count AS “Exp_Mal_Asset”,
sum_risk/asset_count AS “Total_Risk_Asset”
FROM (
SELECT
fact_asset_date.day AS week_of,
COUNT(DISTINCT asset_id) AS asset_count,
SUM(vulnerabilities) AS sum_vuln,
SUM(critical_vulnerabilities) AS sum_crit,
SUM(vulnerabilities_with_exploit) AS sum_exp,
SUM(vulnerabilities_with_exploit+vulnerabilities_with_malware_kit) AS sum_exp_mal,
SUM(riskscore) AS sum_risk
FROM fact_asset_date(‘2021-06-01’, CURRENT_DATE, INTERVAL ‘1 week’)
GROUP BY fact_asset_date.day
) AS fad

SELECT
week_of,
asset_count,
sum_vuln/asset_count AS “Vulns_Asset”,
sum_crit/asset_count AS “Crits_Asset”,
sum_exp/asset_count AS “Exp_Asset”,
sum_exp_mal/asset_count AS “Exp_Mal_Asset”,
sum_risk/asset_count AS “Total_Risk_Asset”
FROM (
SELECT
fact_asset_date.day AS week_of,
COUNT(DISTINCT asset_id) AS asset_count,
SUM(vulnerabilities) AS sum_vuln,
SUM(critical_vulnerabilities) AS sum_crit,
SUM(vulnerabilities_with_exploit) AS sum_exp,
SUM(vulnerabilities_with_exploit+vulnerabilities_with_malware_kit) AS sum_exp_mal,
SUM(riskscore) AS sum_risk
FROM fact_asset_date(‘2021-06-01’, CURRENT_DATE, INTERVAL ‘1 week’)
GROUP BY fact_asset_date.day
) AS fad