Query, given an asset (or set of assets), what is the breakdown of risk score by category of vulnerability

InsightVM SQL Queries
1

I have been pulling individual assets, exporting vulnerabilities, and throwing a pivottable for the risk attributable to each “category” of vulnerability. Example:

Workstation123:

  • Total Risk
  • Java Risk
  • Flash Risk
  • Adobe Reader Risk
  • Chrome Risk
  • FireFox Risk
  • Edge RIsk
  • Microsoft Patching Risk

(except these are columns in the spreadsheet, but you get the idea)

The idea is take a list of our “dirtiest” assets, and some guidance about what to do about them, rinse repeat. This sheet is our “working” sheet - AssetABC is missing MS patches, AssetCBD needs Adobe upgraded, etc.

Is there any way to pull this sort of information in a SQL report for an asset (or, ideally, a group of assets whose total risk exceeds N risk score)?

2

Here is logic based on your example.

SELECT 

 favf.asset_id
,da.host_name
,SUM(favf.risk_score) AS total_risk
,SUM(favf.risk_score) FILTER (WHERE java.category_name = 'Java' ) AS java_risk
,SUM(favf.risk_score) FILTER (WHERE flash.category_name = 'Adobe Flash' ) AS flash_risk
,SUM(favf.risk_score) FILTER (WHERE reader.category_name = 'Adobe Acrobat/Reader' ) AS adobe_reader_risk
,SUM(favf.risk_score) FILTER (WHERE chrome.category_name = 'Google Chrome' ) AS chrome_risk
,SUM(favf.risk_score) FILTER (WHERE firefox.category_name = 'Mozilla Firefox' ) AS firefox_risk
,SUM(favf.risk_score) FILTER (WHERE edge.category_name = 'Microsoft Edge' ) AS edge_risk
,SUM(favf.risk_score) FILTER (WHERE patch.category_name = 'Microsoft Patch' ) AS microsoft_patch_risk

FROM
	fact_asset_vulnerability_finding favf
JOIN
	dim_asset da ON favf.asset_id = da.asset_id
JOIN
	dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
LEFT JOIN
	dim_vulnerability_category java ON dv.vulnerability_id = java.vulnerability_id AND java.category_name = 'Java'
LEFT JOIN
	dim_vulnerability_category flash ON dv.vulnerability_id = flash.vulnerability_id AND flash.category_name = 'Adobe Flash'
LEFT JOIN
	dim_vulnerability_category reader ON dv.vulnerability_id = reader.vulnerability_id AND reader.category_name = 'Adobe Acrobat/Reader'
LEFT JOIN
	dim_vulnerability_category chrome ON dv.vulnerability_id = chrome.vulnerability_id AND chrome.category_name = 'Google Chrome'
LEFT JOIN
	dim_vulnerability_category firefox ON dv.vulnerability_id = firefox.vulnerability_id AND firefox.category_name = 'Mozilla Firefox'
LEFT JOIN
	dim_vulnerability_category edge ON dv.vulnerability_id = edge.vulnerability_id AND edge.category_name = 'Microsoft Edge'
LEFT JOIN
	dim_vulnerability_category patch ON dv.vulnerability_id = patch.vulnerability_id AND patch.category_name = 'Microsoft Patch'
-- WHERE
-- 	SUM(favf.risk_score) > /* Can be used as a threshold for risk_score */
GROUP BY
	favf.asset_id
	,da.host_name
ORDER BY
	SUM(favf.risk_score) DESC
-- LIMIT 50; /* Can be used to grab the TOP n assets by total risk-score  */
2 Likes
3

WOW - I can’t wait to go try this, this is so much more than I was expecting!

4

Happy to help and feel free to mark it as a solution if it such. - Bob

5

Thanks, getting “query invalid” but let me work through it, I’m sure it’s something easy.

6

Solved - all of the ‘favf.risk_score’ (which appears not to exist) need changed to ‘dv.riskscore’ and it works like a charm!

2 Likes
7

I’ve been looking for something very similar (break down by asset categories - servers/workstations/etc.) and by category (Java, Flash, Chrome, etc.). The earlier responses are really helpful examples to consider.

I also wanted to see trends over time, so I’ve been using
fact_asset_date(‘2021-01-01’, CURRENT_DATE, INTERVAL ‘1 month’)
to try something similar. However, it didn’t take care of the vulnerability_category,in a single query (I had to run multiple reports with different filters and combine in PowerBI) so I hope I can figure out how to combine these concepts.

8

Glad you were able to troubleshoot.

It is frustrating the DW and Security Console do not share the same backend schema. The script I wrote was in the DW and I confirmed all the tables existed but overlooked possible variances in column names.

@john_hartman - Out of curiosity are there any plans to eventually make this 1:1?

9

Final Scripts

Security Console:

Note: The SC does not like comments

SELECT 

 favf.asset_id
,da.host_name
,SUM(dv.riskscore) AS total_risk
,SUM(dv.riskscore) FILTER (WHERE java.category_name = 'Java' ) AS java_risk
,SUM(dv.riskscore) FILTER (WHERE flash.category_name = 'Adobe Flash' ) AS flash_risk
,SUM(dv.riskscore) FILTER (WHERE reader.category_name = 'Adobe Acrobat/Reader' ) AS adobe_reader_risk
,SUM(dv.riskscore) FILTER (WHERE chrome.category_name = 'Google Chrome' ) AS chrome_risk
,SUM(dv.riskscore) FILTER (WHERE firefox.category_name = 'Mozilla Firefox' ) AS firefox_risk
,SUM(dv.riskscore) FILTER (WHERE edge.category_name = 'Microsoft Edge' ) AS edge_risk
,SUM(dv.riskscore) FILTER (WHERE patch.category_name = 'Microsoft Patch' ) AS microsoft_patch_risk

FROM
	fact_asset_vulnerability_finding favf
JOIN
	dim_asset da ON favf.asset_id = da.asset_id
JOIN
	dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
LEFT JOIN
	dim_vulnerability_category java ON dv.vulnerability_id = java.vulnerability_id AND java.category_name = 'Java'
LEFT JOIN
	dim_vulnerability_category flash ON dv.vulnerability_id = flash.vulnerability_id AND flash.category_name = 'Adobe Flash'
LEFT JOIN
	dim_vulnerability_category reader ON dv.vulnerability_id = reader.vulnerability_id AND reader.category_name = 'Adobe Acrobat/Reader'
LEFT JOIN
	dim_vulnerability_category chrome ON dv.vulnerability_id = chrome.vulnerability_id AND chrome.category_name = 'Google Chrome'
LEFT JOIN
	dim_vulnerability_category firefox ON dv.vulnerability_id = firefox.vulnerability_id AND firefox.category_name = 'Mozilla Firefox'
LEFT JOIN
	dim_vulnerability_category edge ON dv.vulnerability_id = edge.vulnerability_id AND edge.category_name = 'Microsoft Edge'
LEFT JOIN
	dim_vulnerability_category patch ON dv.vulnerability_id = patch.vulnerability_id AND patch.category_name = 'Microsoft Patch'
-- WHERE
-- 	SUM(dv.riskscore) > /* Can be used as a threshold for risk_score */
GROUP BY
	 favf.asset_id
	,da.host_name
ORDER BY
	SUM(dv.riskscore) DESC
-- LIMIT 50; /* Can be used to grab the TOP n assets by total risk-score  */

Data Warehouse:

SELECT 

 favf.asset_id
,da.host_name
,SUM(favf.risk_score) AS total_risk
,SUM(favf.risk_score) FILTER (WHERE java.category_name = 'Java' ) AS java_risk
,SUM(favf.risk_score) FILTER (WHERE flash.category_name = 'Adobe Flash' ) AS flash_risk
,SUM(favf.risk_score) FILTER (WHERE reader.category_name = 'Adobe Acrobat/Reader' ) AS adobe_reader_risk
,SUM(favf.risk_score) FILTER (WHERE chrome.category_name = 'Google Chrome' ) AS chrome_risk
,SUM(favf.risk_score) FILTER (WHERE firefox.category_name = 'Mozilla Firefox' ) AS firefox_risk
,SUM(favf.risk_score) FILTER (WHERE edge.category_name = 'Microsoft Edge' ) AS edge_risk
,SUM(favf.risk_score) FILTER (WHERE patch.category_name = 'Microsoft Patch' ) AS microsoft_patch_risk

FROM
	fact_asset_vulnerability_finding favf
JOIN
	dim_asset da ON favf.asset_id = da.asset_id
JOIN
	dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
LEFT JOIN
	dim_vulnerability_category java ON dv.vulnerability_id = java.vulnerability_id AND java.category_name = 'Java'
LEFT JOIN
	dim_vulnerability_category flash ON dv.vulnerability_id = flash.vulnerability_id AND flash.category_name = 'Adobe Flash'
LEFT JOIN
	dim_vulnerability_category reader ON dv.vulnerability_id = reader.vulnerability_id AND reader.category_name = 'Adobe Acrobat/Reader'
LEFT JOIN
	dim_vulnerability_category chrome ON dv.vulnerability_id = chrome.vulnerability_id AND chrome.category_name = 'Google Chrome'
LEFT JOIN
	dim_vulnerability_category firefox ON dv.vulnerability_id = firefox.vulnerability_id AND firefox.category_name = 'Mozilla Firefox'
LEFT JOIN
	dim_vulnerability_category edge ON dv.vulnerability_id = edge.vulnerability_id AND edge.category_name = 'Microsoft Edge'
LEFT JOIN
	dim_vulnerability_category patch ON dv.vulnerability_id = patch.vulnerability_id AND patch.category_name = 'Microsoft Patch'
-- WHERE
-- 	SUM(favf.risk_score) > /* Can be used as a threshold for risk_score */
GROUP BY
	favf.asset_id
	,da.host_name
ORDER BY
	SUM(favf.risk_score) DESC
-- LIMIT 50; /* Can be used to grab the TOP n assets by total risk-score  */
10

There are no plans to make these 1:1

The data warehouse schema does have slight performance improvements to the way that the tables are created and things of that nature. I see no improvements coming to the console itself as most of those efforts are geared more towards moving to the cloud native approach.

1 Like
11

Exactly what I was looking for as well. Thank you.