SQL Query to include "Tags"

I need a report that can run the following SQL query export but include “custom tags” instead of “Site”.

Existing SQL Query:

SELECT da.ip_address AS “Address”, da.host_name AS “Name”, da.sites AS “Site”, dos.description AS “Operating System”, fa.exploits AS “Exploits”, fa.malware_kits AS “Malware”, fa.vulnerabilities AS “Vulnerabilities”,

to_char(round(fa.riskscore::numeric,0),‘999G999G999’) AS “Risk”, CAST(da.last_assessed_for_vulnerabilities AS DATE) AS “Last Scan”, ‘True’ AS “Assessed”

FROM dim_asset da

JOIN fact_asset fa USING (asset_id)

JOIN dim_operating_system dos USING (operating_system_id)

ORDER BY da.host_name

Headings are currently:
Address Name Site Operating System Exploits Malware Vulnerabilities Risk Last Scan Assessed

Any help would be greatly appreciated!

1 Like

This should get you what you are looking for, though results will duplicated if you have more than 1 custom tag applied to your assets.

WITH tags AS (
	SELECT asset_id, tag_name 
	FROM dim_tag_asset
	JOIN dim_tag USING(tag_id)
	WHERE tag_type = 'CUSTOM'
)

SELECT 
da.ip_address AS "Address", 
da.host_name AS "Name", 
--da.sites AS "Site",
t.tag_name AS "Tag Name",
dos.description AS "Operating System", 
fa.exploits AS "Exploits", 
fa.malware_kits AS "Malware", 
fa.vulnerabilities AS "Vulnerabilities",

to_char(round(fa.riskscore::numeric,0),'999G999G999') AS "Risk", 
CAST(da.last_assessed_for_vulnerabilities AS DATE) AS "Last Scan", 
'True' AS "Assessed"

FROM dim_asset da
JOIN fact_asset fa USING (asset_id)
LEFT JOIN tags t USING(asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
ORDER BY da.host_name
5 Likes

This works perfectly! Thank you so much for your help!

1 Like