Table: planetscale_audit_log - Query PlanetScale Audit Logs using SQL

PlanetScale's Audit Log is a feature that records all actions performed in your database. It provides a comprehensive view of the activities and changes within your database, including who made the changes, when they were made, and what exactly was changed. This feature is crucial for maintaining the security and integrity of your database.

Table Usage Guide

The planetscale_audit_log table provides insights into actions performed within your PlanetScale database. As a database administrator, explore details through this table, including the actor, the action performed, and the timestamp of the action. Utilize it to uncover information about database activities, such as who made changes, when they were made, and what exactly was changed.

Examples

Most recent actions for the account

Analyze the most recent activities in your account to understand and track changes made by users. This can be useful for auditing purposes, to ensure accountability and transparency in account operations.

select
  created_at,
  audit_action,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
order by
  created_at desc
limit
  10;

select
  created_at,
  audit_action,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
order by
  created_at desc
limit
  10;

Find all branch creation events

Explore the historical timeline of branch creation events within your database. This can help you understand project progression, user activity, and potential areas of focus.

select
  created_at,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
where
  audit_action = 'branch.created'
order by
  created_at desc;

select
  created_at,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
where
  audit_action = 'branch.created'
order by
  created_at desc;

Find events targeting a specific database

Explore events specifically aimed at a particular database to gain insights into actions performed, who performed them, and when they occurred. This can be particularly useful for auditing purposes, allowing you to track any changes or activities related to your database.

select
  created_at,
  audit_action,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
where
  target_type = 'Database'
  and target_display_name = 'my_db'
order by
  created_at desc;

select
  created_at,
  audit_action,
  actor_display_name,
  auditable_display_name,
  target_display_name
from
  planetscale_audit_log
where
  target_type = 'Database'
  and target_display_name = 'my_db'
order by
  created_at desc;

Most common actions

Determine the most frequent actions in your system's audit log to understand user behavior patterns and system usage. This can help in optimizing resources and identifying potential security issues.

select
  audit_action,
  count(*)
from
  planetscale_audit_log
group by
  audit_action
order by
  count desc;

select
  audit_action,
  count(*)
from
  planetscale_audit_log
group by
  audit_action
order by
  count(*) desc;

Most common actors

Explore which actors are most frequently logged in the audit log to understand system usage patterns and identify potential security concerns.

select
  actor_id,
  actor_display_name,
  count(*)
from
  planetscale_audit_log
group by
  actor_id,
  actor_display_name
order by
  count desc;

select
  actor_id,
  actor_display_name,
  count(*)
from
  planetscale_audit_log
group by
  actor_id,
  actor_display_name
order by
  count(*) desc;

Schema for planetscale_audit_log

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
action	text		Short action for this audit record, e.g. created.
actor_display_name	text		Display name of the actor.
actor_id	text		Unique ID of the actor.
actor_type	text		Type of the actor, e.g. User.
audit_action	text		Full action for this audit record, e.g. deploy_request.created.
auditable_display_name	text		Display name of the resource for this audit entry, e.g. test_branch.
auditable_id	text		Unique ID for the resource type of the audit entry.
auditable_type	text		Resource type the audit entry is for, e.g. Branch.
created_at	timestamp with time zone		When the audit record was created.
id	text		Unique ID of the log entry.
location	text		Geographic location the action was requested from.
metadata	jsonb		Metadata for the audit record.
organization_name	text	=, !=, ~~, ~~, !~~, !~~	The name of the organization.
remote_ip	text		IP address the action was requested from.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
target_display_name	text		Display name for the target resoruce, e.g. test_db.
target_id	text		ID of the resource type for this audit record.
target_type	text		Resource type for this audit record, e.g. Database.
type	text		Type of log entry, e.g. AuditLogEvent.
updated_at	timestamp with time zone		When the audit record was updated.

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

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

steampipe_export_planetscale --config '<your_config>' planetscale_audit_log