steampipe plugin install pipes

Table: pipes_workspace_snapshot - Query Pipes Workspace Snapshots using SQL

Pipes Workspace Snapshots are a feature within the Pipes service that allow you to capture and store the state of your workspace at a specific point in time. This is useful for tracking changes, recovering data, and maintaining a historical record of your workspace. It provides a way to manage and monitor the evolution of your workspace over time.

Table Usage Guide

The pipes_workspace_snapshot table provides insights into Workspace Snapshots within Pipes. As a data analyst or DevOps engineer, explore snapshot-specific details through this table, including snapshot creation time, workspace ID, and associated metadata. Utilize it to track changes over time, recover lost data, and maintain a historical record of your workspace.

Important Notes

  • This table supports optional quals. Queries with optional quals in the where clause are optimised to use Turbot Pipes filters.

  • Optional quals are supported for the following columns:

    • created_at
    • dashboard_name
    • dashboard_title
    • id
    • query_where - Allows use of query filters. For a list of supported columns for snapshots, please see Supported APIs and Columns. Please note that any query filter passed into the query_where qual will be combined with other optional quals.
    • visibility

Examples

Basic info

Explore which workspaces are in use and their current state to manage resources effectively. This information can also provide insights into the visibility and the versioning of the schema used, along with any associated tags for better organization and tracking.

select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot;
select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot;

List snapshots for a specific workspace

Identify snapshots related to a specific workspace to gain insights into its state, visibility, and associated dashboard details. This can assist in managing and analyzing the workspace's configuration and evolution over time.

select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
workspace_handle = 'dev';
select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
workspace_handle = 'dev';

List public snapshots for the AWS Tags Limit benchmark dashboard across all workspaces

Explore which public snapshots are available for the AWS Tags Limit benchmark dashboard across all workspaces. This can be useful to assess the current configuration and visibility settings, providing insights into the overall state and benchmarking of AWS tags.

select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
dashboard_name = 'aws_tags.benchmark.limit'
and visibility = 'anyone_with_link';
select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
dashboard_name = 'aws_tags.benchmark.limit'
and visibility = 'anyone_with_link';

List snapshots for the AWS Compliance CIS v1.4.0 dashboard executed in the last 7 days

Explore the status and visibility of snapshots from the AWS Compliance CIS v1.4.0 dashboard taken in the past week. This can help in tracking compliance history and identifying changes or anomalies over time.

select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
dashboard_name = 'aws_compliance.benchmark.cis_v140'
and created_at >= now() - interval '7 days';
select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
dashboard_name = 'aws_compliance.benchmark.cis_v140'
and created_at >= datetime('now', '-7 days');

Get the raw data for a particular snapshot

Explore the raw snapshot data related to a specific user and workspace. This can be particularly useful for auditing changes or troubleshooting issues within a particular workspace environment.

select
data
from
pipes_workspace_snapshot
where
identity_handle = 'myuser'
and workspace_handle = 'dev'
and id = 'snap_cc1ini7m1tujk0r0oqvg_12fie4ah78yl5rwadj7p6j63';
select
data
from
pipes_workspace_snapshot
where
identity_handle = 'myuser'
and workspace_handle = 'dev'
and id = 'snap_cc1ini7m1tujk0r0oqvg_12fie4ah78yl5rwadj7p6j63';

List snapshots for the AWS Tags Limit benchmark dashboard executed in the last 7 days using query filter

This example helps to identify the snapshots taken for the AWS Tags Limit benchmark dashboard in the last week. This can be useful for assessing recent performance metrics and understanding changes over time.

select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
query_where = 'dashboard_name = ''aws_tags.benchmark.limit'' and created_at >= now() - interval ''7 days''';
select
id,
identity_handle,
workspace_handle,
state,
visibility,
dashboard_name,
dashboard_title,
schema_version,
tags
from
pipes_workspace_snapshot
where
query_where = 'dashboard_name = ''aws_tags.benchmark.limit'' and created_at >= date(' now ',' -7 days ')';

List all controls in alarm for a benchmark snapshot

This query helps you identify the controls that are in an alarm state for a specific benchmark snapshot. This is useful for pinpointing areas of concern within your system and addressing them proactively.

with unnest_panels as (
select
key as panel_name,
value -> 'data' -> 'rows' as panel_data
from
pipes_workspace_snapshot as s
cross join lateral jsonb_each(data -> 'panels')
where
-- modify with your snapshot ID
s.id = 'snap_ckkeot9pveta5example_04czp8qosu9ir318e29example'
),
unnest_rows as (
select
panel_name,
jsonb_array_elements(panel_data) as control
from
unnest_panels
)
select
panel_name,
control
from
unnest_rows
where
control ->> 'status' = 'alarm';
with unnest_panels as (
select
key as panel_name,
json_extract(value, '$.data.rows') as panel_data
from
pipes_workspace_snapshot as s,
json_each(s.data, '$.panels')
where
-- modify with your snapshot ID
s.id = 'snap_ckkeot9pveta5example_04czp8qosu9ir318e29example'
),
unnest_rows as (
select
panel_name,
json_each(panel_data) as control
from
unnest_panels
)
select
panel_name,
control
from
unnest_rows
where
json_extract(control.value, '$.status') = 'alarm';

List all controls for a benchmark snapshot

Determine the areas in which specific controls are set to 'alarm' status for a given snapshot. This is useful for identifying potential areas of concern or risk within your workspace.

with unnest_panels as (
select
key as panel_name,
value -> 'data' -> 'rows' as panel_data
from
pipes_workspace_snapshot as s
cross join lateral jsonb_each(data -> 'panels')
where
-- modify with your snapshot ID
s.id = 'snap_ckkeot9pveta5example_04czp8qosu9ir318e29example'
),
unnest_rows as (
select
panel_name,
jsonb_array_elements(panel_data) as control
from
unnest_panels
)
select
panel_name,
control
from
unnest_rows
where
control ->> 'status' = 'alarm';
with unnest_panels as (
select
key as panel_name,
json_extract(value, '$.data.rows') as panel_data
from
pipes_workspace_snapshot as s,
json_each(s.data, '$.panels')
where
-- modify with your snapshot ID
s.id = 'snap_ckkeot9pveta5example_04czp8qosu9ir318e29example'
),
unnest_rows as (
select
panel_name,
json_each(panel_data) as control
from
unnest_panels
)
select
panel_name,
control.value
from
unnest_rows
where
json_extract(control.value, '$.status') = 'alarm';

List all controls in alarm for multiple snapshots

This query is used to identify and analyze the controls that are in an alarm state across multiple snapshots. It's useful for monitoring and managing system health, allowing you to quickly pinpoint potential issues and take corrective action.

with unnest_panels as (
select
s.id as snapshot_id,
s.created_at as snapshot_created_at,
s.dashboard_name as dashboard_name,
key as panel_name,
value -> 'data' -> 'rows' as panel_data
from
pipes_workspace_snapshot as s
cross join lateral jsonb_each(data -> 'panels')
where
-- Update or remove constraints for your workspace and goals
s.identity_handle = 'example-community'
and s.workspace_handle = 'tracker'
and s.dashboard_name = 'github_tracker.benchmark.organization_checks'
and s.created_at > current_timestamp - interval '3 days'
),
unnest_rows as (
select
up.snapshot_id,
up.snapshot_created_at,
up.dashboard_name,
up.panel_name,
jsonb_array_elements(panel_data) as control
from
unnest_panels as up
)
select
*
from
unnest_rows
where
control ->> 'status' = 'alarm';
with unnest_panels as (
select
s.id as snapshot_id,
s.created_at as snapshot_created_at,
s.dashboard_name as dashboard_name,
key as panel_name,
json_extract(value, '$.data.rows') as panel_data
from
pipes_workspace_snapshot as s,
json_each(s.data, '$.panels')
where
-- Update or remove constraints for your workspace and goals
s.identity_handle = 'example-community'
and s.workspace_handle = 'tracker'
and s.dashboard_name = 'github_tracker.benchmark.organization_checks'
and s.created_at > datetime('now', '-3 days')
),
unnest_rows as (
select
up.snapshot_id,
up.snapshot_created_at,
up.dashboard_name,
up.panel_name,
json_each(up.panel_data) as control
from
unnest_panels as up
)
select
*
from
unnest_rows
where
json_extract(control.value, '$.status') = 'alarm';

Schema for pipes_workspace_snapshot

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
created_attimestamp with time zone>, >=, =, <, <=The time when the snapshot was created.
created_byjsonbInformation about the user who created the snapshot.
created_by_idtextThe unique identifier of the user who created the snapshot.
dashboard_nametext=, !=The mod-prefixed name of the dashboard this snapshot belongs to.
dashboard_titletext=, !=The title of the dashboard this snapshot belongs to.
datajsonbThe data for the snapshot.
expires_attimestamp with time zoneThe time when the snapshot will expire.
idtext=, !=The unique identifier for the snapshot.
identity_handletext=The handle of the identity.
identity_idtextThe unique identifier of the identity to which the snapshot belongs to.
identity_typetextThe type of identity, can be org/user.
inputsjsonbThe inputs used for this snapshot.
query_wheretext=The query where expression to filter snapshots.
schema_versiontextThe schema version of the underlying snapshot.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetextThe current state of the snapshot.
tagsjsonbThe tags for the snapshot.
updated_attimestamp with time zoneThe time when the snapshot was last updated.
updated_byjsonbInformation about the user who last updated the snapshot.
updated_by_idtextThe unique identifier of the user who last updated the snapshot.
user_idtext=, !=, ~~, ~~*, !~~, !~~*The unique identifier for the user.
version_idbigintThe current version ID for the snapshot.
visibilitytext=, !=The visibility of the snapshot.
workspace_handletext=The handle of the workspace.
workspace_idtextThe unique identifier for the workspace.

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)" -- pipes

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

steampipe_export_pipes --config '<your_config>' pipes_workspace_snapshot