turbot/guardrails_insights

Query: workspace_account_detail

Usage

powerpipe query guardrails_insights.query.workspace_account_detail

Steampipe Tables

SQL

select
accountables -> 'turbot' ->> 'id' as "id",
case
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/aws#/resource/types/account' then accountables -> 'data' ->> 'Id'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/azure#/resource/types/subscription' then accountables -> 'data' ->> 'subscriptionId'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/gcp#/resource/types/project' then accountables -> 'data' ->> 'projectId'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/servicenow#/resource/types/instance' then accountables -> 'data' ->> 'instance_id'
end as "External ID",
case
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/aws#/resource/types/account' then accountables -> 'data' ->> 'AccountAlias'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/azure#/resource/types/subscription' then accountables -> 'data' ->> 'displayName'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/gcp#/resource/types/project' then accountables -> 'data' ->> 'name'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/servicenow#/resource/types/instance' then accountables -> 'data' ->> 'instance_id'
end as "Name",
case
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/aws#/resource/types/account' then 'AWS'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/azure#/resource/types/subscription' then 'Azure'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/gcp#/resource/types/project' then 'GCP'
when accountables -> 'type' ->> 'uri' = 'tmod:@turbot/servicenow#/resource/types/instance' then 'ServiceNow'
end as "Provider",
workspace as "Workspace",
(
accountables -> 'descendants' -> 'metadata' -> 'stats' -> 'total'
) :: int as "Resources",
(
accountables -> 'policySettings' -> 'metadata' -> 'stats' ->> 'total'
) :: int as "Policy Settings",
(
accountables -> 'alerts' -> 'metadata' -> 'stats' ->> 'total'
) :: int as "Alerts",
(
accountables -> 'activeControls' -> 'metadata' -> 'stats' ->> 'total'
) :: int as "Active Controls"
from
guardrails_query,
jsonb_array_elements(output -> 'resources' -> 'items') as accountables
where
query = '{
resources(
filter: "resourceTypeId:tmod:@turbot/turbot#/resource/interfaces/accountable level:self limit:5000"
) {
metadata {
stats {
total
}
}
items {
data
turbot {
id
}
type {
uri
}
descendants {
metadata {
stats {
total
}
}
}
policySettings {
metadata {
stats {
total
}
}
}
alerts: controls(filter: "state:alarm,invalid,error") {
metadata {
stats {
total
}
}
}
activeControls: controls(filter: "state:active") {
metadata {
stats {
total
}
}
}
}
}
}'
order by
"Workspace",
"Resources" desc;

Dashboards

The query is used in the dashboards: