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_namefrom planetscale_audit_logorder by created_at desclimit 10;
select created_at, audit_action, actor_display_name, auditable_display_name, target_display_namefrom planetscale_audit_logorder by created_at desclimit 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_namefrom planetscale_audit_logwhere audit_action = 'branch.created'order by created_at desc;
select created_at, actor_display_name, auditable_display_name, target_display_namefrom planetscale_audit_logwhere 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_namefrom planetscale_audit_logwhere 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_namefrom planetscale_audit_logwhere 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_loggroup by audit_actionorder by count desc;
select audit_action, count(*)from planetscale_audit_loggroup by audit_actionorder 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_loggroup by actor_id, actor_display_nameorder by count desc;
select actor_id, actor_display_name, count(*)from planetscale_audit_loggroup by actor_id, actor_display_nameorder 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