Query To add in (Credentials Failed script)

Hello I was wondering if there was a query i can add into this query to make it so that the report doesnt show anything with agents. THis one i’m using is showing what i need it to show but its including assets with agents on the report and its false. I do have a custom tag called “assets with agents” on is there a query i can use with in this one that can leave out anything with this custom tag?

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 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

Hi Venessa,

A simple solution might be to add the following to your WHERE clause

da.sites NOT LIKE '%Rapid7 Insight Agents%'

I would recommend adding the [sites] field to your dataset first to do some vetting and ensure the results match your expectations.

This is great, but I have tried to embed this everywhere with with a site field to my data set but havent been able to get it to work. I have lost my work and dont have where i last tried it. I am not savvy with the queries when i have to make them my self but tried every which way i know…and nothing.

@holly_wilsey do you have script already made with for credential failed report like mine that excludes anything with an agent? Or even tell the report to not to show the asset if its certainty of 1 since thats how agents categorize authentication.

@holly_wilsey just posted this, which has a lot of queries under the sql-query-export folder. You might find something there…

Yes, i love these data warehouses i used them; they are saved in my favorites.

Just making sure you noticed the GitHub that contains queries. For example, there are these referencing credentials…

Aggregated-Credentail-Status.sql

List-assets-with-credential-status.sql

Credential-Fail.sql

I didnt see these but tried to use some of what they had and what i have on another question way back from april.

What am i doing wrong i keep getting assets with agents included in them. I added to my query in bold.

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

You probably need to move that where statement out of the temporary table and into the main query OR add it to the other temporary tables as well.