SQL query for asset scan history

InsightVM SQL Queries
1

If I search for an asset it nicely shows the scan history in date and time, with the IPv4 as well as the hostname for each time it was scanned. I am trying to work out if I can pull this for all assets as an SQL query, instead of going into each asset and downloading the CSV equivalent for the scan history of said asset. I tried to combine a lot of other query statements together, and I’ll admit my SQL knowledge is somewhat poor as you’ll see, so my apologies upfront.

SELECT

scan.scan_id AS “Scan ID”,

scan.scan_name AS “Scan Name”,

scan.started AS “Time Scan Started”,

scan.finished AS “Time Scan Finished”,

status.description AS “Scan Completion Status”,

type.description AS “Scan Description”

FROM fact_asset AS fa

JOIN dim_asset AS da ON fa.asset_id = da.asset_id

da.ip_address AS “IP_Address”,

da.mac_address AS “MAC_Address”,

da.host_name AS “DNS_Hostname”

from dim_scan scan

join dim_scan_status status on status.status_id=scan.status_Id

join dim_scan_type type on type.type_id=scan.type_id

ORDER BY ds.name ASC

2

I think your main issue is that you’re trying to SELECT more columns after a FROM statement. You’re essentially trying to make two different queries with no separation.

Also the tables you’re querying I don’t think are going to give you what you want. You’re most likely looking for fact_asset_scan so your query would essentially be this

SELECT
ds.scan_id AS "Scan ID",
ds.scan_name AS "Scan Name",
ds.started AS "Time Scan Started",
ds.finished AS "Time Scan Finished",
dss.description AS "Scan Completion Status",
dst.description AS "Scan Description",
da.ip_address AS "IP_Address",
da.mac_address AS "MAC_Address",
da.host_name AS "DNS_Hostname"

FROM fact_asset_scan AS fas

JOIN dim_asset da ON fas.asset_id = da.asset_id
JOIN dim_scan ds ON fas.scan_id=ds.scan_id
JOIN dim_scan_status dss on ds.status_id=dss.status_Id
JOIN dim_scan_type dst on ds.type_id=dst.type_id

ORDER BY ds.scan_id ASC
3

This is the SQL query I have been looking for. Thanks. Is there a way to limit the query to the last 5 scans per asset?

EDIT:
I have also tested this query by trying to limit it to 1 IP by using a WHERE statement (as follows) but it is running for several hours:

SELECT
ds.scan_id AS "Scan ID",
ds.scan_name AS "Scan Name",
ds.started AS "Time Scan Started",
ds.finished AS "Time Scan Finished",
dss.description AS "Scan Completion Status",
dst.description AS "Scan Description",
da.ip_address AS "IP_Address",
da.mac_address AS "MAC_Address",
da.host_name AS "DNS_Hostname"

FROM fact_asset_scan AS fas

JOIN dim_asset da ON fas.asset_id = da.asset_id
JOIN dim_scan ds ON fas.scan_id=ds.scan_id
JOIN dim_scan_status dss on ds.status_id=dss.status_Id
JOIN dim_scan_type dst on ds.type_id=dst.type_id

WHERE da.ip_address IN ('x.x.x.x')

ORDER BY ds.scan_id ASC'

4

the only way I think to limit it would be to do something like

WHERE ds.started > current_date - interval ‘x days’ so depending on your scan schedule you could alter that interval to grab the last 5 weeks for example.

also for the IP I wouldn’t do an IN statement, you should just be able to say da.ip_address = 'x.x.x.x'

5

That is brilliant thinking on limiting the time. I used an IN statement in case I wanted to view more than one IP but point noted.

6

Does the IN statement give you more than one IPv4 per host? In case the host had multiple scan times?

I noticed on the fixed query that @john_hartman kindly helped with that it seemed to be just one consistent IPv4 (presuming that the query by design is just outputting what is expected).

7

The IN statement could be used if you wanted to search for multiple IPs as in multiple assets within that same query. Otherwise if you’re just looking for one asset I would just do the = instead of the IN. However it should essentially get you the same result. That statement was more on the effect of efficiency in the query and how fast it may return results.

When you’re asking about an asset having multiple IP addresses that could come into effect as well as the da.ip_address is looking at the primary IP of the asset. If the asset has multiple IPs that are not the primary from that scan then they would not be shown.

8

Thanks for getting back to me John. It sounds like the query would need to be adjusted to reflect multiple scans for any given asset that is done in the same day? The difficulty I’m having in translating to SQL is when I select an asset from an asset group, and then seeing that for its scan history it has multiple scans in one day but with different IPv4s.I’ll try and upload a screenshot the earliest I can (currently not working on this page)

9

I’ve tried to expand this out a bit to get the IPv4 associated at the time of the scan, however I’m getting a syntax error on the second SELECT

SELECT

ds.scan_id AS “Scan ID”,

ds.scan_name AS “Scan Name”,

ds.finished AS “Time Scan Finished”,

dss.description AS “Scan Completion Status”,

dst.description AS “Scan Description”,

da.ip_address AS “IP_Address”,

da.mac_address AS “MAC_Address”,

da.host_name AS “DNS_Hostname”

FROM fact_asset_scan AS fas

JOIN dim_asset da ON fas.asset_id = da.asset_id

JOIN dim_scan ds ON fas.scan_id=ds.scan_id

JOIN dim_scan_status dss on ds.status_id=dss.status_Id

JOIN dim_scan_type dst on ds.type_id=dst.type_id

(

SELECT daa.Primary AS “Primary”

FROM dim_asset_address AS daa

JOIN dim_asset da ON da.asset_id=dai.asset_id

)

WHERE finished > current_date -5 and da.ip_address like ‘10.%’

ORDER BY ds.scan_id ASC

I’m not entirely sure if because the daa.Primary is incorrect, which I grabbed from the dimensions and fact diagram

Dimensional Data Warehouse Schema (rapid7.com)

10

That schema is strictly for the Data Warehouse which is separate from the schema used within the InsightVM console when running a SQL Query export report.

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

Those two pages above are the ones to use for the Console schema. (Unfortunately there is no pretty map)