Okay so i have this sql query that lets me know if credentials have failed or if they are non-authenticated. Ive notice recently some of the assets that have agents on them are included in this list; the “some” have also been ran on adhoc manual scans in the past but the credentials are deactivated until needed. what query can i use to leave out the ones that have the agent already on them cause of course agent assets dont use credentials which is the appeal to my org., but i have to send this report to higher ups and i dont want admins getting trouble for something that is already being done which would mean that asset has agent on them so no creds needed? Also need to make sure that it only has asset listed only once in the report so dedup query would be great.
Hope this makes sense.
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’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘None of the applicable services was discovered in the scan’
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
ORDER BY fa.aggregated_credential_status_id DESC, ot.owner_tags DESC