turbot/azure_insights

Query: activedirectory_group_owners

Usage

powerpipe query azure_insights.query.activedirectory_group_owners

SQL

-- Activedirectory Users
select
u.display_name as "Display Name",
'User' as "Member Type",
u.account_enabled as "Account Enabled",
u.id as "ID",
'/azure_insights.dashboard.activedirectory_user_detail?input.user_id=' || lower(u.id) as link
from
azuread_group as g,
jsonb_array_elements(owner_ids) as m
left join azuread_user as u on u.id = (trim((m :: text), '""'))
where
u.id is not null
and g.id = $1 -- Service Principal
union all
select
s.display_name as "Display Name",
'Service Principal' as "Member Type",
s.account_enabled as "Account Enabled",
s.id as "ID",
null as link
from
azuread_group as g,
jsonb_array_elements(owner_ids) as m
left join azuread_service_principal as s on s.id = (trim((m :: text), '""'))
where
s.id is not null
and g.id = $1
order by
"Display Name";

Dashboards

The query is used in the dashboards: