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