steampipe plugin install pipes

Table: pipes_workspace_db_log - Query Pipes Workspace Database Logs using SQL

Pipes is a service that provides a unified interface for querying, connecting, and managing data from different sources. The Workspace Database Logs in Pipes service offers a comprehensive log of all the activities and operations performed in the workspace database. It aids in tracking and monitoring the performance, errors, and other crucial metrics of the workspace database.

Table Usage Guide

The pipes_workspace_db_log table provides insights into the logs of the workspace database in the Pipes service. As a database administrator or a DevOps engineer, explore log-specific details through this table, including error messages, timestamps, and associated metadata. Utilize it to monitor the performance, troubleshoot issues, and ensure the smooth operation of your workspace database.

Examples

List db logs for an actor by handle

Explore the database logs associated with a specific user to gain insights into their activities and the duration of their actions, which can be useful for auditing or troubleshooting purposes.

select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
actor_handle = 'siddharthaturbot';
select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
actor_handle = 'siddharthaturbot';

List db logs for an actor by handle in a particular workspace

Explore the activity history of a specific user within a particular workspace. This query is useful in monitoring user actions, identifying patterns, and troubleshooting issues.

select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
actor_handle = 'siddharthaturbot'
and workspace_handle = 'dev';
select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
actor_handle = 'siddharthaturbot'
and workspace_handle = 'dev';

List queries that took more than 30 seconds to execute

Identify instances where certain queries have taken longer than usual to execute. This can help in pinpointing inefficient queries, thus enabling optimization and improving overall system performance.

select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
duration > 30000;
select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
duration > 30000;

List all queries that ran in my workspace in the last hour

Explore which queries have been executed in your workspace within the past hour. This can be particularly useful for tracking recent activities and identifying any unusual or unexpected operations.

select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
workspace_handle = 'dev'
and log_timestamp > now() - interval '1 hr';
select
id,
workspace_id,
workspace_handle,
duration,
query,
log_timestamp
from
pipes_workspace_db_log
where
workspace_handle = 'dev'
and log_timestamp > datetime('now', '-1 hour');

Schema for pipes_workspace_db_log

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
actor_avatar_urltextThe avatar of the user who executed the query.
actor_display_nametextThe display name of the user who executed the query.
actor_handletextThe handle of the user who executed the query.
actor_idtextThe unique identifier for the user who executed the query.
created_attextThe time when the db log record was generated.
durationdouble precisionThe duration of the query in milliseconds(ms).
idtextThe unique identifier for a db log.
log_timestamptimestamp with time zoneThe time when the log got captured in postgres.
querytextThe query that was executed in the workspace.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
user_idtext=, !=, ~~, ~~*, !~~, !~~*The unique identifier for the user.
workspace_handletextThe handle of the workspace on which the query was executed.
workspace_idtextThe unique identifier of the workspace on which the query was executed.

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_db_log