Query: subscription_role_definitions_for_activedirectory_group

select
  d.id || '/' || d.subscription_id as role_definition_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
where
  (a.scope like '/subscriptions/%' and a.scope not like '%/resourceGroups/%')
  and (a.scope like '/subscriptions/%' and a.scope not like '%/resourcegroups/%')
  and  d.id is not null
  and g.id = split_part($1, '/', 1)
  and g.tenant_id = split_part($1, '/', 2);