# Scan status report

**URL:** <https://discuss.rapid7.com/t/scan-status-report/14040>\
**Category:** SQL Queries\
**Tags:** InsightVM, sql\
**Created:** [July 28, 2022, 9:14pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040 "2022-07-28T21:14:37Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [July 28, 2022, 9:14pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/1 "2022-07-28T21:14:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [July 29, 2022, 2:29am UTC](https://discuss.rapid7.com/t/scan-status-report/14040/2 "2022-07-29T02:29:58Z")

</div>

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](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/e/e22e2cdb401d5fc0518471fac44d3a816eeb2bcf.png)

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](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/f/f92a77b3ee21b9ea174433b23b707ac7f8ac054b.png)  
 ![Screen Shot 2022-07-28 at 10.25.09 PM](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/6/6f053adb38662de693027f228ab62472a49083fc.png)

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.

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [July 29, 2022, 12:59pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/3 "2022-07-29T12:59:41Z")

</div>

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](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/6/6eb45f04d020509d5371ce4ef1ee3e49cbd4d8c2.png)

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [July 29, 2022, 1:03pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/4 "2022-07-29T13:03:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [July 29, 2022, 1:38pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/5 "2022-07-29T13:38:18Z")

</div>

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

> **[SQL example - asset authentication access level | InsightVM Documentation](https://docs.rapid7.com/insightvm/sql-example-asset-authentication-access-level/)**
>
> This query returns asset credential statuses in terms of authentication access level.

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [August 1, 2022, 1:25pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/6 "2022-08-01T13:25:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 1, 2022, 1:56pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/7 "2022-08-01T13:56:38Z")

</div>

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.

> [@timtekk](#):
>
> I am also looking for a query to report on scan status of all assets. complete/failed/partial success etc.

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? 🤔

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 1, 2022, 2:08pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/8 "2022-08-01T14:08:40Z")

</div>

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:

> [@Querying information from my last scan](https://discuss.rapid7.com/t/querying-information-from-my-last-scan/8166):
>
> Hello, I am looking to see if within my sql query I could just run the query against the very last scan that was run on the asset group / asset / whatever is best. Basically I’d like to query the last scan for any vulnerabilities with the word ‘exploit’ or ‘remote’ or ‘malware’ (for examples) in the title. Thank you.

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [August 1, 2022, 2:18pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/9 "2022-08-01T14:18:42Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 1, 2022, 2:41pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/10 "2022-08-01T14:41:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [August 12, 2022, 5:29pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/11 "2022-08-12T17:29:36Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 12, 2022, 6:54pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/12 "2022-08-12T18:54:55Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 12, 2022, 6:55pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/13 "2022-08-12T18:55:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![timtekk](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/timtekk/32/931_2.png) [@timtekk](https://discuss.rapid7.com/u/timtekk)\
**Post date:** [August 12, 2022, 7:29pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/14 "2022-08-12T19:29:24Z")

</div>

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](https://docs.rapid7.com/insightvm/creating-reports-based-on-sql-queries/)

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

---

<div class="post-metadata">

**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\
**Post date:** [August 12, 2022, 7:46pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/15 "2022-08-12T19:46:57Z")

</div>

So underneath that section is these two pages:

> **[Understanding the reporting data model: Facts | InsightVM Documentation](https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts/)**
>
> Fact tables provide access to information allowed by the configuration of the report.

[https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions](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.

---

<div class="post-metadata">

**Author:** ![prasanna\_m](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/p/b38774/32.png) [@prasanna\_m](https://discuss.rapid7.com/u/prasanna_m)\
**Post date:** [August 21, 2024, 8:58pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/16 "2024-08-21T20:58:52Z")

</div>

Hi @john_hartman  
Could you please help share the sql query for to pull ip, hostname, OS, port & credential status (failed/success/partial success)

Thanks much in advance

---

<div class="post-metadata">

**Author:** ![aadm-oconnka1](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/a/4bbf92/32.png) [@aadm-oconnka1](https://discuss.rapid7.com/u/aadm-oconnka1)\
**Post date:** [July 28, 2025, 10:54am UTC](https://discuss.rapid7.com/t/scan-status-report/14040/17 "2025-07-28T10:54:59Z")

</div>

> [@timtekk](#):
>
> How can I put this all together into one query? If possible?
> 
> Thank you!

Hi John, I’m also trying to retrieve scan status per asset. Did you open a product request for that and Is it possible to retrieve that metric now ?

Thanks you,  
Kalei

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [September 2, 2025, 6:14pm UTC](https://discuss.rapid7.com/t/scan-status-report/14040/18 "2025-09-02T18:14:39Z")

</div>

this would be by service:

```auto
SELECT
  ds.name AS "Site Name",
  da.ip_address AS "IP Address",
  da.host_name AS "Host Name",
  dos.description AS "Operating System",
  fass.port AS "Port",
  dcs.credential_status_description AS "Credential Status"
FROM dim_asset da
JOIN dim_site_asset dsa ON da.asset_id = dsa.asset_id
JOIN dim_site ds ON dsa.site_id = ds.site_id
JOIN fact_asset_scan_service fass ON da.asset_id = fass.asset_id
JOIN dim_service dsvc ON fass.service_id = dsvc.service_id
JOIN dim_protocol dp ON fass.protocol_id = dp.protocol_id
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
JOIN dim_credential_status dcs ON fass.credential_status_id = dcs.credential_status_id
ORDER BY ds.name, da.ip_address;

```

This would be by asset:

```auto
SELECT
  ds.name AS "Site Name",
  da.ip_address AS "IP Address",
  da.host_name AS "Host Name",
  dos.description AS "Operating System",
  dacs.aggregated_credential_status_description AS "Credential Status"
FROM dim_asset da
JOIN dim_site_asset dsa ON da.asset_id = dsa.asset_id
JOIN dim_site ds ON dsa.site_id = ds.site_id
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
JOIN fact_asset fa ON da.asset_id = fa.asset_id
JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id = dacs.aggregated_credential_status_id
ORDER BY ds.name, da.ip_address;

```