turbot/azure_insights

Query: resource_groups_for_activedirectory_group

Usage

powerpipe query azure_insights.query.resource_groups_for_activedirectory_group

SQL

select
r.id as resource_group_id
from
azuread_group as g
left join azure_role_assignment as a on a.principal_id = g.id
left join azure_role_definition as d on d.id = a.role_definition_id
left join azure_resource_group as r on r.name = split_part(a.scope, '/', 5)
where
(a.scope like '%/resourceGroups/%')
and d.id is not null
and r.subscription_id = d.subscription_id
and g.id = split_part($1, '/', 1)
and g.tenant_id = split_part($1, '/', 2);