Scan status report

This request probably is very simple, but I struggle with IVM sql reports. I feel to pull any ‘advanced’ info I am forced to use sql queries, and I am just not good at them or the IVM data structure.

So, I am looking for a basic report to query assets and report on any that have not been scanned within X amount of time. Really 7 days is fine.

I am also looking for a query to report on scan status of all assets. complete/failed/partial success etc.

Is sql needed for my requests? Am I overthinking this and there is an easier way? I was even thinking maybe just a dashboard report, but I ran through one and it was not very customizable and spit out too much data.

Thank you.

For the first ask of assets that have not been scanned in the last 7 days you could just use the query builder and the query shown in the screenshot below. You could load this query into a Dashboard card, Remediation project, Goal,or set up a recurring report.
Screen Shot 2022-07-28 at 10.20.53 PM

For the credential portion you could also use the query builder to simply report on either credential success or failure but you would need two different queries to do so. There is no option for partial in this method.
Screen Shot 2022-07-28 at 10.25.16 PM
Screen Shot 2022-07-28 at 10.25.09 PM

However, if you actually want a report that spits out the Asset names all together with a column that specifically lists if their status was failed/success/partial success then SQL would be your best bet. There are a couple different dimension tables for authentication status though.

If the query builder isn’t sufficient let me know and I’ll see what I can work up for a SQL query to get you started.

Thank you John,

When I setup the first query with >= /Now - P7D/ it gave me results including assets that were scanned today?
I changed it to <= /Now - P7D/ and it now is giving me assets older than 7 days (the most recent being 7/21/22).

Not sure why, as the below statement to me reads: asset last scan time is less than today - 7 days ago. I would think the ‘more’ operand would be needed.

image

Now that I think of it this makes sense, less than today = past, more than today = future. All good this is working!

@john_hartman But I would also love to see an SQL query for this too, because I would like to see the various statuses for each asset i.e. failed/partial, etc.

1 Like

I think our example query in our documentation might get you exactly what you’re looking for:

Thank you John,
This query looks promising however it looks to be a query to pull authentication status. I was looking for a query to pull scan status of the asset. Is that possible via sql?

Sorry I’m a little confused on how you worded it originally saying complete/failed/partial success for a scan. To me that means authentication status.

What exactly do you mean by scan status? Are you referring to whether the asset has been discovered vs assessed? Or are you talking more about whether you ran a scan against a /24 for example and a specific asset came back as a dead asset although you know it was online? :thinking:

If you’re referring to the scan as a whole then you could use a similar query like the one mentioned here that Holly gave you:

In her query that she provided you could remove the WHERE statement and add that column to your query

SELECT site_id, MAX(scan_id) as scan_id, status_id
    FROM dim_site_scan
       JOIN dim_scan USING (scan_id)
    GROUP BY site_id, status_id

The status_id would be of the of the whole scan though of course and not specific to an asset. Not sure if this is what you were referring to.

I am ideally looking for the scan status of each asset (ideally from the most recent scan).
So was the asset scanned complete? Was the asset down (for example)? Was the asset scan partially complete (if that is even a thing).
Basically was the asset scanned or not?

Ahh ok that makes more sense. So yeah there is no partially complete status that I know of.

So unfortunately I don’t think we have anything in the SQL schema to report on those indicators per asset on a scan, only the scan as a whole. The only way that I know of for getting that type of data would be through the actual scan logs which will tell you if an asset was found as offline during the scan. There will be a specific log line of “DEAD ASSET”.

I can see how this extra metric could be useful though and it might be worthy of opening a product request for enhancement on.

So @john_hartman, can can I combine these to queries so they can be in one report?
This is my vulnerability query I use:
select da.ip_address, da.host_name, dv.nexpose_id, dv.title,
dv.cvss_score, dv.riskscore
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
where dv.cvss_score > 8
and dv.riskscore > 900
group by da.ip_address, da.host_name, dv.nexpose_id, dv.title,
dv.cvss_score, dv.riskscore
order by dv.cvss_score DESC

Then I have the authentication query you helped me with:
SELECT ds.name “Site”, da.ip_address as “IP Address”, da.host_name as “Host Name”, dacs.aggregated_credential_status_description “Access Level”
FROM dim_asset da
JOIN fact_asset fa using (asset_id)
JOIN dim_site_asset dsa using (asset_id)
JOIN dim_site ds using (site_id)
JOIN dim_aggregated_credential_status dacs using (aggregated_credential_status_id)
GROUP BY ds.name, da.ip_address, da.host_name, dacs.aggregated_credential_status_description
ORDER BY da.ip_address DESC

How can I put this all together into one query? If possible?

Thank you!

You could try this and see how it looks for you.

SELECT 
ds.name AS "Site", 
da.ip_address AS "IP Address", 
da.host_name AS "Host Name", 
dv.nexpose_id AS "Nexpose ID", 
dv.title AS "Vulnerability",
dv.cvss_score AS "CVSS Score", 
dv.riskscore AS "Risk Score",
dacs.aggregated_credential_status_description as "Asset Access Level"

FROM fact_asset fa

JOIN dim_asset da ON fa.asset_id=da.asset_id
JOIN dim_site_asset dsa ON fa.asset_id=dsa.asset_id 
JOIN dim_site ds ON dsa.site_id=ds.site_id
JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id=dacs.aggregated_credential_status_id
JOIN fact_asset_vulnerability_finding favf ON fa.asset_id=favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id

ORDER BY ds.name DESC, da.ip_address DESC, dv.title DESC

That will give a new line for every vulnerability per asset per site and ordered by Site, then IP, then Vulnerability.

This is awesome John, thank you. I really need to tune my skills with the query writing for the reports.
I know stuff like this exists, Creating reports based on SQL queries | InsightVM Documentation

Any other recommendations you have for tuning up sql basic query skills with R7 IVM?

So underneath that section is these two pages:

https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions

The dimension and fact tables that exist within our database. Theres even another page after that full of example queries.

Past those two documents just know that our database uses postgresql so any time you have a question in logic or anything just google away and make sure its psql specific.

Alot of it just comes with experience. The more queries you write the easier it gets. By now I mostly reuse a lot of the same stuff. And ofcourse always feel free to start a new topic here in the discuss forums anytime you get stuck.

1 Like