Policy Scanning

InsightVM SQL Queries
1

Hello All,

In returning information about our policy scanning I cannot find something in particular in the datawarehouse schema. I would like to return to the specific policy that we tested on such as password policy and so on… All I can find is that it returns an integer and I would like the actual name itself. I was thinking it would be in the dim_policy_group, but sub_groups only has an integer value and not a name value. Any help with this would be appreciated.

Thanks,

Jacob

2

Hi @jacob_horning,

Are you using the fact_asset_policy_rule and dim_policy_rule tables. It seems like they may get you the data that you’re looking for. The fact_asset_policy_rule table has the actual asset -> rule association.

3

@tyler_schmidtke

Yes, I looked more into it and I can get the specific check. I was hoping I could get something like Account Policy, or Local policies - like the way they have on the interface.

4

Gotcha. I think that may be the parent_group_id on dim_policy_rule. The tricky part here is that the parent groups can be nested (rather deep) so to get the root you need to join from the base down to the level for that particular rule.

The subquery in the WITH statement here is what gets the full tree for each particular group. The coalesce is used to get the first group_id that isn’t null, going backwards so that we get the most nested one (and the one to which the specific rule applies for our join). That subquery is then joined on the policy rules.

I tested the subquery to see how deeply nested things where, there are at most seven levels of group nesting so this should cover it.

WITH policy_groups AS (
    SELECT coalesce(dpg7.group_id, dpg6.group_id, dpg5.group_id, dpg4.group_id, dpg3.group_id, dpg2.group_id,
                    dpg1.group_id) AS group_id,
           dpg1.title              AS first_group,
           dpg2.title              AS second_group,
           dpg3.title              AS third_group,
           dpg4.title              AS fourth_group,
           dpg5.title              AS fifth_group,
           dpg6.title              AS sixth_group,
           dpg7.title              AS seventh_group
    FROM dim_policy_group dpg1
             JOIN dim_policy_group dpg2 ON dpg1.group_id = dpg2.parent_group_id
             LEFT JOIN dim_policy_group dpg3 ON dpg2.group_id = dpg3.parent_group_id
             LEFT JOIN dim_policy_group dpg4 ON dpg3.group_id = dpg4.parent_group_id
             LEFT JOIN dim_policy_group dpg5 ON dpg4.group_id = dpg5.parent_group_id
             LEFT JOIN dim_policy_group dpg6 ON dpg5.group_id = dpg6.parent_group_id
             LEFT JOIN dim_policy_group dpg7 ON dpg6.group_id = dpg7.parent_group_id
)
select da.asset_id,
       da.ip_address,
       pg.first_group,
       pg.second_group,
       pg.third_group,
       pg.fourth_group,
       pg.fifth_group,
       pg.sixth_group,
       pg.seventh_group,
       dpr.title,
       fapr.status,
       fapr.date_tested
FROM dim_asset da
         JOIN fact_asset_policy_rule fapr ON da.asset_id = fapr.asset_id
         JOIN dim_policy_rule dpr ON fapr.policy_id = dpr.policy_id
         JOIN policy_groups pg ON dpr.parent_group_id = pg.group_id;

Can you give that a go?

5

@tyler_schmidtke

Yes, This was exactly what I was looking for thank you so much!

1 Like
6

@tyler_schmidtke
Hello,

I was hoping there was a possibility to get these same exact results in the console SQL query. Looking at the schema I have no idea to connect dim_policy_result_status for a specific asset_id and get all of the policies we scanned against. I know in the query above that fact_asset_policy_rule connects everything, but it doesn’t seem like that table exists. Thank you so much for your help.

7

@jacob_horning, you are correct. the table structure is a bit different in the reporting data model. This SHOULD be the equivalent query. There’s a status_id and associated status table added as the third join.

WITH policy_groups AS (
    SELECT coalesce(dpg7.group_id, dpg6.group_id, dpg5.group_id, dpg4.group_id, dpg3.group_id, dpg2.group_id,
                    dpg1.group_id) AS group_id,
           dpg1.title              AS first_group,
           dpg2.title              AS second_group,
           dpg3.title              AS third_group,
           dpg4.title              AS fourth_group,
           dpg5.title              AS fifth_group,
           dpg6.title              AS sixth_group,
           dpg7.title              AS seventh_group
    FROM dim_policy_group dpg1
             JOIN dim_policy_group dpg2 ON dpg1.group_id = dpg2.parent_group_id
             LEFT JOIN dim_policy_group dpg3 ON dpg2.group_id = dpg3.parent_group_id
             LEFT JOIN dim_policy_group dpg4 ON dpg3.group_id = dpg4.parent_group_id
             LEFT JOIN dim_policy_group dpg5 ON dpg4.group_id = dpg5.parent_group_id
             LEFT JOIN dim_policy_group dpg6 ON dpg5.group_id = dpg6.parent_group_id
             LEFT JOIN dim_policy_group dpg7 ON dpg6.group_id = dpg7.parent_group_id
)
select da.asset_id,
       da.ip_address,
       pg.first_group,
       pg.second_group,
       pg.third_group,
       pg.fourth_group,
       pg.fifth_group,
       pg.sixth_group,
       pg.seventh_group,
       dpr.title,
       dprs.description AS status,
       fapr.date_tested
FROM dim_asset da
         JOIN fact_asset_policy_rule fapr ON da.asset_id = fapr.asset_id
         JOIN dim_policy_rule dpr ON fapr.policy_id = dpr.policy_id
         JOIN dim_policy_result_status dprs ON dprs.status_id = fapr.status_id
         JOIN policy_groups pg ON dpr.parent_group_id = pg.group_id;
8

@tyler_schmidtke

Thank you so much it works perfectly. Also, my other post on here was regarding this I forgot we already had a thread going so you can ignore that.

1 Like