Table: turbot_resource

Resources in Turbot represent cloud configuration items such as users, networks, servers, etc.

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

Examples

List all AWS IAM Roles

select
  id,
  title,
  create_timestamp,
  metadata,
  data
from
  turbot_resource
where
  resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role';

List all S3 buckets with a given Owner tag

select
  id,
  title,
  tags
from
  turbot_resource
where
  resource_type_uri = 'tmod:@turbot/aws-s3#/resource/types/bucket'
  and tags ->> 'Owner' = 'Jane';

Get a specific resource by ID

select
  id,
  title,
  create_timestamp,
  metadata,
  data
from
  turbot_resource
where
  id = 216005088871602;

Filter for resources using Turbot filter syntax

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

Search for AWS IAM Roles by name (Turbot side)

This query will ask Turbot to filter the resources down to the given filter, limiting the results by name.

select
  id,
  title,
  create_timestamp,
  metadata,
  data
from
  turbot_resource
where
  resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
  and filter = 'admin';

Search for AWS IAM Roles by name (Steampipe side)

This query gathers all the AWS IAM roles from Turbot and then uses Postgres level filters to limit the results.

select
  id,
  title,
  create_timestamp,
  metadata,
  data
from
  turbot_resource
where
  resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
  and title ilike '%admin%';

Search for console logins within 7 days

select
  id,
  title,
  data ->> 'email' as email,
  array_to_string(
    regexp_matches(trunk_title, '^Turbot > (.*) >'),
    ' '
  ) as "directory",
  trunk_title,
  to_char(
    (data ->> 'lastLoginTimestamp') :: timestamp,
    'YYYY-MM-DD HH24:MI'
  ) as "last_login"
from
  turbot_resource
where
  filter = 'resourceTypeId:"tmod:@turbot/turbot-iam#/resource/types/profile" $.lastLoginTimestamp:>=T-7d';

Search for resources created within 7 days, join with count of controls in alarm state

select
  r.id,
  r.title,
  r.trunk_title,
  r.resource_type_uri,
  to_char(r.create_timestamp, 'YYYY-MM-DD HH24:MI') as create_timestamp,
  count(c.*) as alarm_count
from
  turbot_resource as r
  left join turbot_control as c on r.id = c.resource_id
  and c.state = 'alarm'
where
  r.filter = 'notificationType:resource timestamp:>=T-7d'
group by
  r.id,
  r.title,
  r.trunk_title,
  r.resource_type_uri,
  r.create_timestamp
order by
  r.create_timestamp desc;

Extract all resources 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_resource;

Schema for turbot_resource

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
akas	jsonb		AKA (also known as) identifiers for the resource.
create_timestamp	timestamp with time zone		When the resource was first discovered by Turbot. (It may have been created earlier.)
data	jsonb		Resource data.
filter	text	=	Filter used for this resource list.
id	bigint	=	Unique identifier of the resource.
metadata	jsonb		Resource custom metadata.
parent_id	bigint		ID for the parent of this resource. For the Turbot root resource this is null.
path	jsonb		Hierarchy path with all identifiers of ancestors of the resource.
resource_type_id	bigint	=	ID of the resource type for this resource.
resource_type_uri	text	=	URI of the resource type for this resource.
tags	jsonb		Tags for the resource.
timestamp	timestamp with time zone		Timestamp when the resource was last modified (created, updated or deleted).
title	text		Title of the resource.
trunk_title	text		Title with full path of the resource.
update_timestamp	timestamp with time zone		When the resource was last updated in Turbot.
version_id	bigint		Unique identifier for this version of the resource.
workspace	text		Specifies the workspace URL.