DWH Exceptions Reporting

InsightVM SQL Queries
1

Hi,

One of our queries which we run directly from our console for excluded findings uses the fact_asset_scan_vulnerability_instance_excluded table to calculate the total vulnerabilties that have been excluded.

Is there an equivaltent to fact_asset_scan_vulnerability_instance_excluded for the DWH Schema?

We tend to exclude using groups so the data from dim_vulnerability_exception is not going to work, as a vulneraiblity could be excluded based on assets which a team has but the team may have multple instances of server OS’s…

2

Hey Bruce,

I might be able to help, but need more clarity around “We tend to exclude using groups” and provide an example. Also, an outline (i.e., list the fields) of the data being returned by your original console query will be useful when converting to the DWH.

3

This is the script which on the console

WITH
custom_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘CUSTOM’
GROUP BY asset_id
)
,owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
)
SELECT
da.ip_address As “IP Address”,
UPPER(regexp_replace(da.host_name, ‘([.][\w.]+)’, ‘’, ‘g’)) AS “Host Name”,
dos.description AS “Operating System”,
CASE
WHEN ct.custom_tags LIKE ‘%EOL%’ THEN ‘Yes’
–ELSE ‘No’
END As “EOL Status”,
dv.nexpose_id As “Nexpose ID”,
dv.title as “Vulnerability”,
dv.severity,
dv.exploits,
round(dv.riskscore::numeric, 0) AS “Risk Score”,
– max(fasvie.scan_id) AS scan_id,
– max(extract(‘week’ from dsc.started)) as scan_week,
to_char(dve.expiration_date,‘DD/MM/YYYY’) As “Expiration Date”,
CASE
WHEN dve.reason_id LIKE ‘F’ THEN ‘False Positive’
WHEN dve.reason_id LIKE ‘C’ THEN ‘Compensating Control’
WHEN dve.reason_id LIKE ‘U’ THEN ‘Acceptable Use’
WHEN dve.reason_id LIKE ‘R’ THEN ‘Acceptable Risk’
WHEN dve.reason_id LIKE ‘O’ THEN ‘Other’
END As “Exception Reason”,
ot.owner_tags as “Owner”
FROM fact_asset_scan_vulnerability_instance_excluded fasvie

JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
JOIN dim_service ds USING (service_id)
JOIN dim_scan dsc USING (scan_id)
JOIN dim_vulnerability_exception dve USING (vulnerability_exception_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN custom_tags ct ON da.asset_id = ct.asset_id
JOIN owner_tags ot ON da.asset_id = ot.asset_id

GROUP BY dv.nexpose_id, dv.title, da.ip_address, da.host_name,dve.expiration_date,da.asset_id,dv.riskscore,dv.severity,dv.exploits,dos.description,dve.reason_id,ot.owner_tags,ct.custom_tags

ORDER BY da.ip_address, dv.nexpose_id DESC;

I need to translate this in to the DWH Version, but fact_asset_scan_vulnerability_instance_excluded doesn’t exist in DWH.