SQL query to identify assets where specific CIS policy rules are failing

InsightVM SQL Queries
1

Hi! Has someone developed a query to display in a csv for which assets specific CIS policies are failing?

2

in the Warehouse or Console SQL Report?

In the warehouse you can do something like this but it will pull EVERYTHING.

So you may want to limit the query to SITEID or an IP to start, would just need to add that logic.

SELECT
    da.asset_id,
    da.ip_address,
    da.host_name,
    da.sites AS site_name,
    dp.policy_name,
    dpr.rule_name,
    dpr.title AS rule_title,
    fap.status,
    fap.date_tested
FROM
    fact_asset_policy_rule fap
JOIN
    dim_policy dp ON fap.policy_id = dp.policy_id
JOIN
    dim_policy_rule dpr ON fap.rule_id = dpr.rule_id
JOIN
    dim_asset da ON fap.asset_id = da.asset_id
WHERE
    fap.status ILIKE 'fail%'
    AND dp.policy_name ILIKE '%CIS%'
ORDER BY
    da.asset_id,
    dp.policy_name,
    dpr.rule_name;
1 Like