turbot/guardrails
steampipe plugin install guardrails

Table: guardrails_control - Query Guardrails Controls using SQL

Guardrails is a governance, risk management, and compliance service that provides a set of predefined controls to help organizations manage their security and compliance posture. These controls are designed to be easily integrated into existing security and compliance processes and can be customized to meet specific organizational needs. Guardrails controls are categorized into various standards and categories to provide a comprehensive and structured approach to security and compliance management.

Table Usage Guide

The guardrails_control table provides insights into the controls within Guardrails. As a security analyst, explore control-specific details through this table, including control status, control category, and control standard. Utilize it to uncover information about controls, such as their compliance status, the categories they fall under, and the standards they adhere to.

Important Notes

  • When querying this table, we recommend using at least one of these columns (usually in the where clause):
    • id
    • control_type_id
    • control_type_uri
    • resource_type_id
    • resource_type_uri
    • state
    • filter

Examples

Control summary for AWS > IAM > Role > Approved

Explore the status of approved roles in AWS IAM by counting their occurrence. This allows you to identify potential issues and understand the overall health of your IAM roles. Simple table:

select
state,
count(*)
from
guardrails_control
where
control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved'
group by
state
order by
count desc;
select
state,
count(*)
from
guardrails_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
guardrails_control as c
where
control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved'
group by
control_type_uri
order by
total desc;
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
guardrails_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

This query helps in assessing the security posture of your AWS Identity and Access Management (IAM) controls. It aids in identifying the number of controls in different states, allowing you to quickly pinpoint areas that might need attention or remediation, thereby enhancing your overall security management.

select
state,
count(*)
from
guardrails_control
where
filter = 'controlTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
state
order by
count desc;
select
state,
count(*)
from
guardrails_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
guardrails_control as c
where
filter = 'controlTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
control_type_uri
order by
total desc;
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
guardrails_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

Explore the history of changes related to approved roles in AWS IAM. This can help in understanding the compliance status and identifying any unauthorized or accidental modifications.

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

Query the most recent 10 controls

Explore the latest 10 guardrail controls to understand their current state and the reasons behind it, which is crucial for maintaining system integrity and security. Note: It's more efficient to have Turbot Guardrails limit the results to the last 10 (filter = 'limit:10'), rather than using limit 10 which will pull all rows from Turbot Guardrails and will then filter them afterwards on the Steampipe side.

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

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

This query is used to gain insights into the status and reasons for approval of IAM roles in AWS. It helps in managing access controls by identifying roles that are approved, providing a better understanding of the security posture.

select
r.trunk_title,
r.data ->> 'Arn' as arn,
r.metadata -> 'aws' ->> 'accountId' as account_id,
c.state,
c.reason
from
guardrails_control as c,
guardrails_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;
select
r.trunk_title,
json_extract(r.data, '$.Arn') as arn,
json_extract(r.metadata, '$.aws.accountId') as account_id,
c.state,
c.reason
from
guardrails_control as c,
guardrails_resource as r
where
c.control_type_uri = 'tmod:@turbot/aws-iam#/control/types/roleApproved'
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 Guardrails

Discover the segments that fall under Turbot Guardrails' controls. This can provide a comprehensive overview, aiding in the efficient management and review of security measures. 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
guardrails_control;
select
*
from
guardrails_control;

Schema for guardrails_control

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
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.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
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.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- guardrails

You can pass the configuration to the command with the --config argument:

steampipe_export_guardrails --config '<your_config>' guardrails_control