turbot/planetscale
steampipe plugin install planetscale

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
actiontextShort action for this audit record, e.g. created.
actor_display_nametextDisplay name of the actor.
actor_idtextUnique ID of the actor.
actor_typetextType of the actor, e.g. User.
audit_actiontextFull action for this audit record, e.g. deploy_request.created.
auditable_display_nametextDisplay name of the resource for this audit entry, e.g. test_branch.
auditable_idtextUnique ID for the resource type of the audit entry.
auditable_typetextResource type the audit entry is for, e.g. Branch.
created_attimestamp with time zoneWhen the audit record was created.
idtextUnique ID of the log entry.
locationtextGeographic location the action was requested from.
metadatajsonbMetadata for the audit record.
organization_nametext=, !=, ~~, ~~*, !~~, !~~*The name of the organization.
remote_iptextIP address the action was requested from.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
target_display_nametextDisplay name for the target resoruce, e.g. test_db.
target_idtextID of the resource type for this audit record.
target_typetextResource type for this audit record, e.g. Database.
typetextType of log entry, e.g. AuditLogEvent.
updated_attimestamp with time zoneWhen 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