Hi everybody. I’m looking to create a report that would show me systems with vulnerabilities
That are over 45 days since discovery
Have a cvss score of 7 or higher.
Ideally it would be nice if it could be formatted in such a way that it would show the hostname and ip address of each system and then list the vulnerabilities on the system that meet the criteria above
I’m being asked to provide a report on this weekly and there really isn’t a good way to do this with the built in reports. Anyone have any experience doing something similar to this? Thanks
Here’s a query that pulls vulnerabilities with the criteria you mentioned, along with the IP + hostname of the affected assets. I think that date filter should work.
SELECT da.ip_address, da.host_name, dv.nexpose_id, dv.title AS "Vulnerabilty Title",
dv.cvss_score, fava.first_discovered
FROM dim_asset da
JOIN fact_asset_vulnerability_finding favf
ON da.asset_id = favf.asset_id
JOIN fact_asset_vulnerability_age fava
ON favf.vulnerability_id = fava.vulnerability_id
JOIN dim_vulnerability dv
ON favf.vulnerability_id = dv.vulnerability_id
WHERE dv.cvss_score >= 7
AND NOW() - INTERVAL '45 Days' > fava.first_discovered
GROUP BY da.ip_address, da.host_name, dv.nexpose_id, dv.title, dv.cvss_score,
fava.first_discovered
ORDER BY first_discovered
The fact_asset_vulnerability_age table is where we’re getting the date of first discovery so we can pull vulns that were found 45+ days ago. I’m not sure which vulnerability details you’re looking to have in the report, but I added title, ID, and CVSS for now.
The vulnerability proof is stored in the fact_asset_vulnerability_instance table, so we can pull that in. I updated the query to add that:
SELECT da.ip_address, da.host_name, dv.nexpose_id, dv.title AS "Vulnerabilty Title",
dv.cvss_score, fava.first_discovered, htmltotext(favi.proof) AS "Proof"
FROM dim_asset da
JOIN fact_asset_vulnerability_instance favi
ON da.asset_id = favi.asset_id
JOIN fact_asset_vulnerability_age fava
ON favi.vulnerability_id = fava.vulnerability_id
JOIN dim_vulnerability dv
ON favi.vulnerability_id = dv.vulnerability_id
WHERE dv.cvss_score >= 7
AND NOW() - INTERVAL '45 Days' > fava.first_discovered
GROUP BY da.ip_address, da.host_name, dv.nexpose_id, dv.title, dv.cvss_score,
fava.first_discovered, favi.proof
ORDER BY fava.first_discovered
For reference, I used this doc to figure out which table to pull the proof from:
Hi @holly_wilsey
Can you show me where you get the fact_asset_vulnerability_age table? I can not find this table in Dimensional Data Warehouse Schema. I have a similar problem as the creator of this thread.
I have a PowerBI dashboard and would like to provide an additional filter where, for example, you can only display vulnerabilities that have been present on the systems for longer than 90 days. This dashboard should also show the “proof”, which I can only get from the fact_asset_vulnerability_instance table. Accordingly, I have created a Saved View, which I then access via PowerBI. In this Saved View, however, I am still missing the exact date on which this vulnerability was first found on the respective asset. As you can see in my query, I get the information on which date I last saw the vulnerability from the table dim_asset (last_assessed_for_vulnerabilities). But the missing value for “first_seen” is in the Table fact_asset_vulnerability_finding (date).
Select
fasvi.asset_id, fasvi.vulnerability_id, fasvi.date, fasvi.proof, fasvi.service, fasvi.port, fasvi.protocol, dv.title, dv.nexpose_id, dv.description, dv.date_published, dv.severity, da.host_name, da.os_description, Host(da.ip_address) AS ip, da.last_assessed_for_vulnerabilities AS last_seen
FROM
fact_asset_vulnerability_instance fasvi
JOIN
dim_vulnerability dv USING(vulnerability_id)
JOIN
dim_asset da USING(asset_id)
WHERE
Host(da.ip_address) NOT LIKE ALL(ARRAY[’’])
So the reporting data model and the data warehouse actually have some differences between their schemas. In this case, I believe the fact_asset_vulnerability_age table is available in the reporting data model, but not the data warehouse.
If you’re looking to filter for vulnerabilities that are over 90 days old, you should be able to use the “date” field from the fact_asset_vulnerability_finding that you mentioned. It would mean using some Postgres functions to go from that date → exact age, but this post provides a good example of how to do that. It would likely end up looking like this:
ROUND(age(favf.date, 'days'))::integer
The post I linked explains what each of the functions used above is actually doing. Hopefully that gets you the 90 day filter you’re looking for.
