SQL help for Report to leave out a whole custom tag or asset group

InsightVM SQL Queries
1

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
FROM dim_tag
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

2

My initial thought here is that the custom tag you created for these assets may not have been applied to every single asset you want to exclude. So if you run this query and look at the assets that are wrongfully appearing, do they have the custom tag you created?

If that all looks good, you could potentially try changing up how you exclude those tags your query. I see right now you’re using custom_tags AS to define that group of tags to exclude. What if instead of that, you joined dim_tag and dim_tag_asset in your main query and had a condition like:

AND tag_name NOT LIKE '%Tag Name%'
3

Hi Holly,
Yes i see that custom tag so thats how i was able to know that it wasnt working quite as expected. Let me see if i can figure out to join the two; if i join both on the first line is this the one you were referring too?

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
4

I was thinking something along these lines, where you remove the custom_tags AS and you have a query similar to:

SELECT da.host_name AS "Host Name",
       da.ip_address AS "IP Address", dt.tag_name, dt.tag_type, 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 dim_tag_asset dta ON dta.asset_id = da.asset_id
JOIN dim_tag dt ON dt.tag_id = dta.tag_id

WHERE (fa.aggregated_credential_status_id = 1 OR
fa.aggregated_credential_status_id = 2 OR
fa.aggregated_credential_status_id = -1)
AND (dt.tag_name NOT LIKE '%Tag Name%')

Here we’re joining with dim_tag and dim_tag_asset, and I’ve added that statement at the very bottom to exclude assets with the specified tag name. I just did some testing locally and it appeared to exclude assets as expected.

1 Like