SQL addition for last 7 days authenticated

InsightVM SQL Queries
1

I have this sql that shows authentication status, but the problem is i cant get it give me the status for the last 7 days so that it doesnt catch the scans that dont get authentication. I have credentials for one site but not another so it some assets get authentication status “creds failed” on some but they have already authenticated in the next scan which may be few days later…

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

2

Are you referring to having the credential status for that particular asset over the past 7 days, for any number of scans during that period? Or do you mean filtering your existing query to only show status info for assets that have been scanned in the past 7 days?

I’m not aware if there’s a way to get past or historical authentication status info, but if you’re looking to just filter your query to 7 days worth of info, you could do it by scan_finished. Your WHERE clause would be something like:

WHERE (fa.aggregated_credential_status_id = 1 OR
fa.aggregated_credential_status_id = 2 OR
fa.aggregated_credential_status_id = -1)
AND (fa.scan_finished >= NOW() - INTERVAL '7 Days')
1 Like