I am trying to run a report on our top 25 non window vulnerabilities. I do not see any way to customize a template.
Disclaimer: I’m fairly green when it comes to InsightVM, especially the console. I also made a few assumptions. The first being that “non windows vulnerabilities” does not mean OS, however this query can easily be modified for that condition. Another assumption is the dim_vulnerability_category table can accurately identify windows vulnerabilites (NOT LIKE ‘%Microsoft%’, NOT LIKE ‘%Microsoft Patch%’) in the context you are referencing. If not, then building a list using nexpose_id OR title might be an option.
At any rate, here are the steps I took in my environment.
-
Used the following documentation creating-reports-based-on-sql-queries to create a custom report.
-
Used the following query to pull the data. You can also take advantage of scoping if that makes sense.
WITH cte AS (
SELECT
dv.nexpose_id
,dv.title
,COUNT(dv.vulnerability_id) AS vulnerability_instance
,SUM(dv.riskscore) AS risk_score
FROM
fact_asset_vulnerability_instance fav
JOIN
dim_vulnerability dv ON fav.vulnerability_id = dv.vulnerability_id
JOIN
dim_asset da ON fav.asset_id = da.asset_id
GROUP BY
dv.nexpose_id
,dv.title
), category_list AS (
SELECT
dv.nexpose_id
,STRING_AGG(DISTINCT dvc.category_name, ', ') AS vulnerability_category_list
FROM
dim_vulnerability dv
JOIN
cte ON dv.nexpose_id = CTE.nexpose_id
JOIN
dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
GROUP BY
dv.nexpose_id
), top_risk AS (
SELECT
cte.nexpose_id
,cte.title
,cl.vulnerability_category_list
,cte.vulnerability_instance
,cte.risk_score
,'Top 25 By Risk' AS Note
FROM
cte
JOIN
category_list cl ON cte.nexpose_id = cl.nexpose_id
WHERE
cl.vulnerability_category_list NOT LIKE '%Microsoft%'
ORDER BY
CTE.risk_score DESC
LIMIT 25
), top_instance AS (
SELECT
CTE.nexpose_id
,CTE.title
,cl.vulnerability_category_list
,CTE.vulnerability_instance
,CTE.risk_score
,'Top 25 By Instance' AS Note
FROM
CTE
JOIN
category_list cl ON cte.nexpose_id = cl.nexpose_id
WHERE
vulnerability_category_list NOT LIKE '%Microsoft%'
ORDER BY
CTE.vulnerability_instance DESC
LIMIT 25
)
SELECT
*
FROM top_risk
UNION
SELECT *
FROM top_instance
Hope this helps!
To scope a report to non-windows assets I would recommend the following:
-
Create a dynamic asset group searching for OS does not contain microsoft and OS is not empty
-
Use this new asset group to scope the report
- Under the Scope heading in the Create a Report tab, click on “Select Sites, Assets, Asset Groups or Tags”
- In the drop-down box, select Asset Groups and check the box next to the new asset group
- Click Done at the bottom
The report will now only show information on the assets contained within that asset group.