Policy Scanning

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

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.

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

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?

@tyler_schmidtke

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

1 Like