steampipe plugin install turbot

Table: turbot_control

Controls in Turbot represent the state of a given check (control type) against a resource. For example, is encryption at rest enabled for an AWS EBS Volume.

It is recommended that queries to this table should include (usually in the where clause) at least one of these columns: id, control_type_id, control_type_uri, resource_type_id, resource_type_uri, state or filter.

Examples

Control summary for AWS > IAM > Role > Approved

Simple table:

select
state,
count(*)
from
turbot_control
where
control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved'
group by
state
order by
count desc;

Or, if you prefer a full view of all states:

select
control_type_uri,
sum(
case
when state = 'ok' then 1
else 0
end
) as ok,
sum(
case
when state = 'tbd' then 1
else 0
end
) as tbd,
sum(
case
when state = 'invalid' then 1
else 0
end
) as invalid,
sum(
case
when state = 'alarm' then 1
else 0
end
) as alarm,
sum(
case
when state = 'skipped' then 1
else 0
end
) as skipped,
sum(
case
when state = 'error' then 1
else 0
end
) as error,
sum(
case
when state in ('alarm', 'error', 'invalid') then 1
else 0
end
) as alert,
count(*) as total
from
turbot_control as c
where
control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved'
group by
control_type_uri
order by
total desc;

Control summary for all AWS > IAM controls

select
state,
count(*)
from
turbot_control
where
filter = 'controlTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
state
order by
count desc;

Or, if you prefer a full view of all states:

select
control_type_uri,
sum(
case
when state = 'ok' then 1
else 0
end
) as ok,
sum(
case
when state = 'tbd' then 1
else 0
end
) as tbd,
sum(
case
when state = 'invalid' then 1
else 0
end
) as invalid,
sum(
case
when state = 'alarm' then 1
else 0
end
) as alarm,
sum(
case
when state = 'skipped' then 1
else 0
end
) as skipped,
sum(
case
when state = 'error' then 1
else 0
end
) as error,
sum(
case
when state in ('alarm', 'error', 'invalid') then 1
else 0
end
) as alert,
count(*) as total
from
turbot_control as c
where
filter = 'controlTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
control_type_uri
order by
total desc;

List controls for AWS > IAM > Role > Approved

select
timestamp,
state,
reason,
resource_id,
control_type_uri
from
turbot_control
where
filter = 'controlTypeId:"tmod:@turbot/aws-iam#/control/types/roleApproved" controlTypeLevel:self'
order by
timestamp desc;

Query the most recent 10 controls

Note: It's more efficient to have Turbot limit the results to the last 10 (filter = 'limit:10'), rather than using limit 10 which will pull all rows from Turbot and will then filter them afterwards on the Steampipe side.

select
timestamp,
state,
reason,
resource_id,
control_type_uri
from
turbot_control
where
filter = 'limit:10'
order by
timestamp desc;

Control & Resource data for for AWS > IAM > Role > Approved

select
r.trunk_title,
r.data ->> 'Arn' as arn,
r.metadata -> 'aws' ->> 'accountId' as account_id,
c.state,
c.reason
from
turbot_control as c,
turbot_resource as r
where
-- Filter to the control type
c.control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved' -- Filter to the resource type as well, reducing the size of the join
and r.resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
and r.id = c.resource_id
order by
r.trunk_title;

Extract all controls from Turbot

WARNING - This is a large query and may take minutes to run. It is not recommended and may timeout. It's included here as a reference for those who need to extract all data.

select
*
from
turbot_control;

Schema for turbot_control

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
control_type_idbigint=ID of the control type for this control.
control_type_trunk_titletextFull title (including ancestor trunk) of the control type.
control_type_uritext=URI of the control type for this control.
create_timestamptimestamp with time zoneWhen the control was first discovered by Turbot. (It may have been created earlier.)
detailsjsonbDetails associated with this control state.
filtertext=Filter used for this control list.
idbigint=Unique identifier of the control.
reasontextReason for this control state.
resource_idbigintID of the resource this control is associated with.
resource_trunk_titletextFull title (including ancestor trunk) of the resource.
resource_type_idbigint=ID of the resource type for this control.
resource_type_uritext=URI of the resource type for this control.
statetext=State of the control.
timestamptimestamp with time zoneTimestamp when the control was last modified (created, updated or deleted).
update_timestamptimestamp with time zoneWhen the control was last updated in Turbot.
version_idbigintUnique identifier for this version of the control.
workspacetextSpecifies the workspace URL.