Querying Steampipe SQLite Extensions

Your Steampipe extension adds virtual tables to your SQLite installation. Typically, these tables are prefixed with the plugin name. You can run pragma module_list; to get a list of virtual tables, or refer to the documentation for the plugin in the Steampipe Hub. The Hub also contains sample queries for each table.

You can use standard SQLite syntax to query the tables:

select
  name,
  is_private,
  owner_login
from
  github_my_repository

It is often useful to use limit to discover what columns are available for a table without fetching too much data:

select * from aws_iam_access_key limit 1

The normal Steampipe guidance applies:

Select only the columns that you need.
Limit results with a where clause on key columns when possible.
Be aware that some tables require a where or join clause.

SQLite Data Types

Unlike Postgres, SQLite does not have native data types for Date/Time, Boolean, JSON, or IP addresses, so these columns are represented as TEXT or NUMBER. While the data types are not supported as native storage types, SQLite does provide functions to manipulate these types of data.

Boolean

Boolean values are stored as integers: 0 (false) and 1 (true):

select
  name,
  bucket_policy_is_public
from
  aws_s3_bucket
where
  bucket_policy_is_public = 1;

As a result, implicit boolean comparisons work as you would expect:

select
  name,
  bucket_policy_is_public
from
  aws_s3_bucket
where
  bucket_policy_is_public;

SQLite version 3.23.0 also recognizes the keywords TRUE and FALSE. They are essentially just aliases for 1 and 0:

select
  name,
  bucket_policy_is_public
from
  aws_s3_bucket
where
  bucket_policy_is_public = TRUE;

Date/Time

Steampipe SQLite extensions store date time fields as text in RFC-3339 format. You can use SQLite date and time functions to work with these columns.

select
  access_key_id,
  user_name,
  status,
  create_date,
  julianday('now') - julianday(create_date) as age_in_days
from
  aws_iam_access_key
where
  age_in_days > 30;

JSON

Steampipe SQLite extensions store JSON fields as JSONB-formatted text. You can use SQLite JSON functions and operators to work with this data.

You can extract data with json_extract:

select
  name,
  json_extract(acl, '$.Owner') as owner
from
  aws_s3_bucket;

But SQLite version 3.38.0 and later support the -> and ->> operators, which are usually simpler:

select
  name,
  acl -> 'Owner' ->> 'ID'  as owner
from
  aws_s3_bucket;

You can use the json_each table-valued function to treat JSON arrays as rows and use them to join tables:

select
  i.instance_id,
  vol.volume_id,
  vol.size
from
  aws_ebs_volume as vol,
  json_each(vol.attachments) as att
  join aws_ec2_instance as i on i.instance_id = att.value ->> 'InstanceId'
order by
  i.instance_id;

INET/CIDR

Currently, SQLite does not include any functions for IP addresses or CIDR data. There are multiple 3rd party extensions you can install that provide functions for working with IP address data.