"Last Scan", agents, and reports

I’ve been trying to write a SQL report that uses fact_asset_discovery.last_discovered, but was surprised to find that it does not reflect contacts from the Rapid7 Insight Agent. The Last Scan column on the Assets page does, however. Is there anything analogous to the Last Scan value in the reporting schema? There seems to be hardly any mention of the agent in the reporting schema documentation.

I also tried querying for the max value from dim_asset_scan.scan_finished, but that was no help.

Without going into much more detail, at the heart of this problem is the fact that we have corporate laptops running the agent which haven’t connected to the network in, sometimes, months, so they have not been scanned but we know they’re alive. (The fact that they show up in InsightVM data with an IP address from an employee’s home network is another bother associated with these assets.)

I haven’t tested these in a query myself, but there are a couple other options that may get you what you’re looking for here.

The first one is “last_assessed_for_vulnerabilities” in dim_asset, which is a timestamp to denote when the asset was last scanned.

The second is “last_scan_id” in dim_site. This one may depend on how you schedule + scan your assets, but in this case you could join with dim_site_asset to get the associated assets, and dim_scan (using that scan ID) to get the “finished” timestamp for that scan.

I hope that helps some. I’d be interested in hearing if these work out for you for future query reference. :slightly_smiling_face:

1 Like

Thanks, Holly. Will let you know how it works when I have a chance to get back to it.

1 Like

Can you please help us with the SQL query for Last Scan and first Discovered