Query needed to leave out assets that have authentcation through the agent

InsightVM SQL Queries
1

Hi there, i have report i run for several departments that notifiy them if authentication has failed, expired, etc. I have several systems now that have the agent running which coincides with the this report cause it takes it as no credentials added. This is the report i just need sql query that says if it has agent running on it skip it dont report it because of course the agent works at system level and gets certainty of 1 always. The report has been working great but has no reported in my eyes false positives to admins.

Any help would be great.

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(Please Check Password Expiration)’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘No Ports Opened to Authenticate (Please check 135,139,445 on local firewall)’
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

@holly_wilsey do you have any suggestions?

3

One way you could do this is by checking the dim_software table, since the Rapid7 Insight Agent should be listed there as software. So in your case, you could join with dim_software to get the ID of the Agent, and then join with dim_asset_software to see which assets there have the Agent listed as software. That should enable you to skip over assets that have the Agent on it.

4

I tried this and with the other topic-- not sure what i’m doing wrong.

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_tag AS (
SELECT ds.name AS "Software_Name"
** FROM dim_asset_software das**
JOIN dim_software ds USING(software_id)
JOIN dim_asset da ON da.asset_id = das.asset_id
WHERE da.sites NOT LIKE '%Rapid7 Insight Agents%'
),
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(Please Check Password Expiration)’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘No Ports Opened to Authenticate (Please check 135,139,445 on the Local Firewall)’
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