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

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
actor_avatar_url	text	The avatar of the user who executed the query.
actor_display_name	text	The display name of the user who executed the query.
actor_handle	text	The handle of the user who executed the query.
actor_id	text	The unique identifier for the user who executed the query.
created_at	text	The time when the db log record was generated.
duration	double precision	The duration of the query in milliseconds(ms).
id	text	The unique identifier for a db log.
log_timestamp	timestamp with time zone	The time when the log got captured in postgres.
query	text	The query that was executed in the workspace.
workspace_handle	text	The handle of the workspace on which the query was executed.
workspace_id	text	The 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