SQL For Global Exception Tracking

I’m being tasked to track assets that have Global Exceptions applied to them for auditing purposes. One example is the inconclusive-host vulnerability. The item is marked Globally and does not return an asset_id if using something like:

SELECT * from
dim_vulnerability_exception as dve
WHERE Vulnerability_ID = 42489

Is there a way to report on which assets have the Globale Exception applied to them?

I think you could potentially join with the fact_asset_vulnerability_finding table to see which assets the exception applies to. Something like this:

SELECT da.asset_id, da.host_name, da.ip_address, dve.vulnerability_id
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da
ON favf.asset_id = da.asset_id
JOIN dim_vulnerability_exception dve
ON favf.vulnerability_id = dve.vulnerability_id
WHERE dve.vulnerability_id = 119787

You could also do a where clause to just pull everything that has an exception scope of global.

WHERE dve.scope_id = 'G'