I have this sql going to some important people and in the csv i have titles of columns with the following:
DIvision, ITCS or Dept, Host Name, IP Address, Vulnerabilities #, Asset owner (owner tags)
I need one that grabs the asset group title in which it came from and titles the next column Department Name so the person knows what department the ip is located.
My asset groups are set up with specific department names for example asset group “nursing department” thats what i need under department name so the person knows where that ip is located or which department…
WITH asset_count AS (
SELECT DISTINCT fava.asset_id, COUNT (*) AS asset_count
FROM fact_asset_vulnerability_age fava
JOIN dim_vulnerability dv USING (vulnerability_id)
WHERE dv.cvss_v2_score >= 8 AND fava.age_in_days > 60
GROUP BY fava.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
),
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 CASE
WHEN ct.custom_tags LIKE ‘%ITCS%’ THEN ‘ITCS’
ELSE ‘DEPT’
END AS “ITCS or Dept”,
da.host_name AS “Host Name”,
da.ip_address AS “IP Address”,
ac.asset_count AS “Vulnerabilities”,
ot.owner_tags AS “Asset Owner”
FROM asset_count ac
LEFT JOIN dim_asset da USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN custom_tags ct USING (asset_id)
WHERE ot.owner_tags NOT LIKE ‘%jane doe%’
AND ot.owner_tags NOT LIKE ‘%Johnny smith%’
AND ot.owner_tags NOT LIKE ‘%Jill smith%’
ORDER BY ac.asset_count DESC