How to Create SQL Query That will list all items over X Risk Score

InsightVM SQL Queries
1

Let me start with, I have no idea how to do SQL. So I will need a lot of landholding while I learn.

I would like to create a report that generates the following information:

  • For the last 30 days, find all systems that have a risk score of XYZ for more than 14 days.
    • Count number of systems in this group
    • List each system that has a had a risk score of XYZ for 14 days

I have a working asset group for this that obviously tracks the information in a graph but I can’t pull historic data or export the systems over time from this. I would like to create a report every week that generates a list of systems that every week with a count and list of systems.

Can someone help me with this to give me a jumping off point?

2

You can use this for starters to show every asset having a risk score greater than XYZ:

SELECT da.host_name, ROUND(fa.riskscore::numeric, 0) AS “Risk Score”
FROM dim_asset da
LEFT OUTER JOIN fact_asset fa USING (asset_id)
WHERE ROUND(fa.riskscore::numeric, 0) > XYZ

Here is the github repo by Rapid7 containing most of the tables and a few examples for your reference: GitHub - rapid7/insightvm-sql-queries: InsightVM helpful SQL queries

1 Like