# Querying vulnerability data from my last scan

**URL:** <https://discuss.rapid7.com/t/querying-vulnerability-data-from-my-last-scan/26343>\
**Category:** SQL Queries\
**Created:** [August 15, 2023, 6:26pm UTC](https://discuss.rapid7.com/t/querying-vulnerability-data-from-my-last-scan/26343 "2023-08-15T18:26:15Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ezpeezy75](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@ezpeezy75](https://discuss.rapid7.com/u/ezpeezy75)\
**Post date:** [August 15, 2023, 6:26pm UTC](https://discuss.rapid7.com/t/querying-vulnerability-data-from-my-last-scan/26343/1 "2023-08-15T18:26:15Z")

</div>

Hello,  
I am trying to pull the most recent scan data from our environment using the **latest scan results** of the targeted assets. For some reason, the query below is pulling data from the assets’ scan history and this includes vulnerabilities that were already remediated which we do not want. I went through the GitHub repo and did not see a way to pull solely the latest/most recent vulnerabilities from completed scans for our assets. I found this ticket here of someone having the same issue to me however, I am having trouble integrating this into my existing query.

**Reference Forum:**

> [@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.

**SQL Query**  
WITH os\_business\_units AS (  
SELECT DISTINCT dta.asset\_id, string\_agg(dt.tag\_name, ', ') AS “tag\_name”  
FROM dim\_tag dt  
JOIN dim\_tag\_asset dta ON dt.tag\_id = dta.tag\_id  
WHERE dt.tag\_name iLIKE ‘OS Business Unit:%’  
GROUP BY dta.asset\_id  
),  
os\_owner AS (  
SELECT DISTINCT dta.asset\_id, string\_agg(dt.tag\_name, ', ') AS “tag\_name”  
FROM dim\_tag dt  
JOIN dim\_tag\_asset dta ON dt.tag\_id = dta.tag\_id  
WHERE dt.tag\_name iLIKE ‘OS Owner:%’  
GROUP BY dta.asset\_id  
),  
os\_leadership AS (  
SELECT DISTINCT dta.asset\_id, string\_agg(dt.tag\_name, ', ') AS “tag\_name”  
FROM dim\_tag dt  
JOIN dim\_tag\_asset dta ON dt.tag\_id = dta.tag\_id  
WHERE dt.tag\_name iLIKE ‘OS Leadership:%’  
GROUP BY dta.asset\_id  
),  
last\_scan\_for\_site AS (  
SELECT site\_id, MAX(scan\_id) AS “scan\_id”  
FROM dim\_site\_scan  
JOIN dim\_scan USING (scan\_id)  
WHERE status\_id = ‘C’  
GROUP BY site\_id  
)

SELECT  
da.host\_name AS “Asset Name”,  
da.ip\_address AS “IP Address”,  
da.sites AS “Site”,  
dos.description AS “Operating System”,  
CASE WHEN dv.title iLIKE ‘%CVE%’ THEN substring(dv.title, ‘CVE-\d{4}-\d{4,7}’)  
ELSE CASE WHEN dv.title iLIKE ‘MS%’ THEN substring(dv.title, 1, 8)  
ELSE ‘N/A’  
END  
END AS “CVE ID”,  
dv.title AS “Vulnerability”,  
proofAsText(fasvi.proof) AS “Proof”,  
dv.severity AS “Severity”,  
ds.summary AS “Remediation Information”,  
min(fasvi.date) :: date AS “Discovery Date”,  
NOW() :: date - min(fasvi.date) :: date AS “Days Open”,  
SUBSTRING(da.last\_assessed\_for\_vulnerabilities::varchar(255), 1, 10) AS “Last Scan”,  
ob.tag\_name AS “Business Unit”,  
ol.tag\_name AS “Leadership”,  
oo.tag\_name AS “Remediation Owner”,  
NULL AS “Jira ID”

FROM  
fact\_asset\_scan\_vulnerability\_instance fasvi  
JOIN dim\_asset da ON fasvi.asset\_id = da.asset\_id  
JOIN dim\_vulnerability dv ON dv.vulnerability\_id = fasvi.vulnerability\_id  
JOIN dim\_vulnerability\_category dvc ON dv.vulnerability\_id = dvc.vulnerability\_id  
JOIN dim\_operating\_system dos ON dos.operating\_system\_id = da.operating\_system\_id  
JOIN dim\_vulnerability\_solution dvs ON dvs.vulnerability\_id = dv.vulnerability\_id  
JOIN dim\_tag\_asset dta ON da.asset\_id = dta.asset\_id  
LEFT JOIN dim\_tag dt ON dt.tag\_id = dta.tag\_id  
LEFT JOIN os\_business\_units ob ON da.asset\_id = ob.asset\_id  
LEFT JOIN os\_leadership ol ON da.asset\_id = ol.asset\_id  
LEFT JOIN os\_owner oo ON da.asset\_id = oo.asset\_id  
LEFT JOIN last\_scan\_for\_site lsfs ON dsi.site\_id = ds.site\_id  
LEFT JOIN dim\_solution ds ON dvs.solution\_id = ds.solution\_id

WHERE  
dv.date\_published \< (NOW() - INTERVAL ‘60 days’)  
AND  
((dv.cvss\_v3\_score IS NULL AND dv.cvss\_v2\_score \>= 7)  
OR  
(dv.cvss\_v3\_score \>= 7)  
)  
AND dos.asset\_type NOT iLike ‘Workstation’  
AND dos.family NOT iLIKE ‘Windows’  
AND da.last\_assessed\_for\_vulnerabilities \>= (NOW() - INTERVAL ‘30 days’)

Group BY  
da.host\_name,  
da.ip\_address,  
da.sites,  
dos.description,  
dv.title,  
dv.severity,  
fasvi.proof,  
ds.summary,  
ds.applies\_to,  
da.last\_assessed\_for\_vulnerabilities,  
ob.tag\_name,  
oo.tag\_name,  
ol.tag\_name