I have a report works off sql several actually this one of many. We have started a process to exclude whole assets from reports due to an exclusion process we have implemented for number of days; Insight vm has easy way of excluding vulnerabilities but not whole assets from reports.
I have created a whole custom tag and embedded it in an asset group so i can put in query in this sql so the report doesnt show whats in that custom tag or asset group. I just need this report not to show what is in the custom tag or what is in the asset group either one; whatever easier.
I was able to use the custom tag line at the bottom to leave out, but there are some assets still showing that are included. What am i doing wrong?
WITH owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
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
SELECT ot.owner_tags AS “Owner”, ct.custom_tags AS “Custom Tags”, da.host_name AS “Host Name”, da.ip_address AS “IP Address”, CASE
WHEN fa.aggregated_credential_status_id = 1 THEN ‘No Credentials Supplied’
WHEN fa.aggregated_credential_status_id = 2 THEN ‘All Credentials Failed(Please Check Password Expiration)’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘No Ports Opened to Authenticate (Please check 135,139,445 on the Local Firewall)’
END AS “Credential Status”,
to_char (fa.scan_finished, ‘mm/dd/yyyy’) AS “Date Scan Completed”
FROM fact_asset fa
JOIN dim_asset da USING (asset_id)
JOIN custom_tags ct USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
WHERE fa.aggregated_credential_status_id = 1 OR
fa.aggregated_credential_status_id = 2 OR
fa.aggregated_credential_status_id = -1
AND custom_tags NOT LIKE '%Custom tag-assets not needed to be reported%-Example of custom tag name
ORDER BY fa.aggregated_credential_status_id DESC, ot.owner_tags DESC