[azure compliance mod] Scan All Subscriptions

[pansorgeUSD]

Hi,
whenever I run a scan via steampipe check benchmark.cis_v150 or the same scan via dashboard, it always scans only the AD of the directory and the default subscription. Is there a parameter I can pass to scan all supscriptions of a tenat?

Phillip

[e-gineer]

Hey @pansorgeUSD ... thanks for using Steampipe!

You can definitely scan many subscriptions (individually, or all at the same time). The key is to:

• Create a connection per subscription; and then
• Setup an aggregator to combine them together

Hopefully that gets you going!

PS: I've created an issue to improve our docs for this (common) setup.

[Pragmatisch11]

Hi, may i open this discussion once again @e-gineer :)

I encounterd a problem with this solution, using the aggregator for the Azure Compliance Mod in combination with the CIS-Benchmark. When aggregating multiple subscriptions, each AzureAD-Check is also performed multiple times. For example, each guest user will be checked x times (x=number of aggregated subscriptions). However, each guest-user is the same in all subscriptions. There is no difference. Is there a way to get rid of the duplicated AzureAD-Checks? It would be great if every AzureAD-Check only gets evaluated once in the report.

Is there a need to open an issue or is there an option which I do not have on my radar?

Thanks,
Nils

[khushboo9024]

Hi @pansorgeUSD Thanks for raising this issue. We looked into the problem you reported and successfully reproduced the issue.

During our investigation, we identified the root cause of the problem is the left join with table azure_tenant .

This table is returning duplicate rows a certain number of times, corresponding to the number of aggregated subscriptions.

To address this issue, we've implemented a temporary workaround for one of the affected controls, specifically cis_v150_1_4 - Ensure Guest Users Are Reviewed on a Regular Basis.

Can you please modify the query ad_guest_user_reviewed_monthly given as below and run the control (steampipe check control.cis_v150_1_4 ).

query "ad_guest_user_reviewed_monthly" {
  sql = <<-EOQ
    with distinct_tenant as (
      select
        distinct tenant_id
      from
        azure_tenant
    )
    select
      u.display_name as resource,
      case
        when not account_enabled then 'alarm'
        when u.created_date_time::timestamp <= (current_date - interval '30' day) then 'alarm'
        else 'ok'
      end as status,
      case
        when not account_enabled then 'Guest user ''' || u.display_name || ''' inactive.'
        else 'Guest user ''' || u.display_name || ''' was created ' || extract(day from current_timestamp - u.created_date_time::timestamp) || ' days ago.'
      end as reason,
      t.tenant_id
    from
      azuread_user as u
      left join distinct_tenant as t on t.tenant_id = u.tenant_id
    where
      u.user_type = 'Guest';
  EOQ
}

[Pragmatisch11]

Hi @khushboo9024 , thank you very much for the really fast response to my question.

The workaround seems to work perfectly fine. Thanks for that!

When do you plan to fix the other CIS-controls as well, for example 1.2.6 Ensure Multi-factor Authentication is Required for Azure Management, 1.14 Ensure That 'Users Can Register Applications' Is Set to 'No' or 1.19 Ensure that 'Users can create security groups in Azure portals, API or PowerShell' is set to 'No'?

Thanks,
Nils

[rajlearner17]

@Pragmatisch11 Thanks for testing the same and confirm us. Appreciate this finding in the aggregator connection.

I would suggest you, please raise the issue here with detailed info. We will look into it and make sure the release comes soon.

FYI, the above change will be applied in a few queries as we re-validate them. Thank you

[Pragmatisch11]

I opened the issue here turbot/steampipe-mod-azure-compliance#227
Thanks!