Turbot Pipes Query Filters

Many Turbot Pipes APIs support a filter syntax that allows you to search and filter lists using SQL-like grammar. APIs that support this capability will have a where parameter that allows you to pass in a where clause for the filter:

export PIPES_TOKEN=tpt_c6rnjt8afakemj4gha10_svpnmxqfaketokenad431k

curl -H "Authorization: Bearer ${PIPES_TOKEN}" \
https://pipes.turbot.com/api/latest/user/johnsmyth/workspace/myworkspace/snapshot?where="dashboard_name='aws_insights.dashboard.aws_account_report'"

The console also supports this query syntax:

Note that the query filter is parsed and processed by the Steampipe API and not passed to a backend database. There's no need to worry about SQL injection. In fact, the schema of the query reflects the API output and is entirely abstracted from the backend storage implementation.

Syntax

The where argument syntax supports a subset of the Postgres where clause syntax.

You can do simple equality operations:

dashboard_title = 'AWS Account Report'

Or inequalities (using != or <>):

visibility != 'workspace'

visibility <> 'workspace'

You can use like (or not like) to do wildcard matching

dashboard_name like 'aws_insights%'

dashboard_name not like '%.benchmark.%'

Or ilike for case-insensitive wildcard matching

dashboard_name ilike 'aws_insights%'

You can use standard comparison operators (>, >=, <, <=) \:

dashboard_title > 'S'

created_at < '2022-08-10T18:32:09Z'

You can even use now() and interval to do relative date/time filters:

created_at > now() - interval '26 hr'

created_at > now() - interval '2 days'

created_at > now() - interval '1 week'

created_at > now() - interval '1 month'

You can use in() to compare against multiple values:

dashboard_title in ('AWS Account Report', 'Shared Access')

Or not in() to do the inverse:

dashboard_title not in ('AWS Account Report', 'Shared Access')

You can check for null:

tags is null

Or not null values:

inputs is not null

You can use JSON arrow operators for JSON fields, with the usual string, numeric, and boolean operators:

inputs ->> 'input.vpc_id' = 'vpc-11111111'

tags ->> 'Name' is null

inputs ->> 'volume_arn' like '%:123456789012:%'

You can even do complex compound statements with and and or:

inputs ->> 'input.vpc_id' = 'vpc-11111111' and dashboard_title = 'AWS VPC Detail'

(dashboard_title = 'Shared Access' or dashboard_title = 'AWS Account Report') and created_at > '2022-08-10T18:32:09Z'

Queryable Columns

The queryable columns come from the API results, but not all columns can be used in a where filter - Each API has specific columns allowed for querying.

For example, the snapshot API returns:

{
  "items": [
 {
      "id": "snap_cbpvpdmv4vji3f000000_3v1gvdufu9eez5cwpjgy8u80k",
      "identity_id": "u_01234567890123456789",
      "workspace_id": "w_01234567890123456789",
      "state": "available",
      "visibility": "workspace",
      "dashboard_name": "aws_insights.dashboard.aws_account_report",
      "dashboard_title": "AWS Account Report",
      "schema_version": "20220614",
      "inputs": null,
      "tags": null,
      "created_at": "2022-08-10T18:45:10Z",
      "created_by_id": "u_01234567890123456789",
      "created_by": {
        "id": "u_01234567890123456789",
        "handle": "johnsmyth",
        "display_name": "johnsmyth",
        "avatar_url": "https://avatars.githubusercontent.com/u/6843140?v=4",
        "status": "accepted",
        "version_id": 11,
        "created_at": "2021-11-23T18:20:15Z",
        "updated_at": "2022-08-10T14:06:10Z"
 },
      "version_id": 1
 }
 ]
}

But the snapshot API only allows you to filter on:

• created_at
• dashboard_name
• dashboard_title
• id
• inputs
• tags
• title
• visibility
• expires_at

Supported APIs & Columns

• /api/latest/{identity type}/{identity handle}/workspace/{workspace handle}/snapshot
  • created_at
  • dashboard_name
  • dashboard_title
  • id
  • inputs
  • tags
  • title
  • visibility
  • expires_at
• /api/latest/{identity type}/{identity handle}/workspace/{workspace handle}/pipeline
  • args
  • created_at
  • id
  • identity_id
  • pipeline
  • tags
  • title
  • updated_at
  • workspace_id
• /api/latest/{identity type}/{identity handle}/workspace/{workspace handle}/process
  • created_at
  • id
  • identity_id
  • pipeline_id
  • state
  • type
  • updated_at
  • workspace_id