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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
control_type_id	bigint	=	ID of the control type for this control.
control_type_trunk_title	text		Full title (including ancestor trunk) of the control type.
control_type_uri	text	=	URI of the control type for this control.
create_timestamp	timestamp with time zone		When the control was first discovered by Turbot. (It may have been created earlier.)
details	jsonb		Details associated with this control state.
filter	text	=	Filter used for this control list.
id	bigint	=	Unique identifier of the control.
reason	text		Reason for this control state.
resource_id	bigint		ID of the resource this control is associated with.
resource_trunk_title	text		Full title (including ancestor trunk) of the resource.
resource_type_id	bigint	=	ID of the resource type for this control.
resource_type_uri	text	=	URI of the resource type for this control.
state	text	=	State of the control.
timestamp	timestamp with time zone		Timestamp when the control was last modified (created, updated or deleted).
update_timestamp	timestamp with time zone		When the control was last updated in Turbot.
version_id	bigint		Unique identifier for this version of the control.
workspace	text		Specifies the workspace URL.