Skip to content
Telemetry
Security and audit SQL recipe

Audit privileged actions with SQL

Summarize sensitive administrative actions, denied attempts, and review-required outcomes without collecting raw secrets or resource contents.

Beginnersecurity_audit_eventsReviewed 2026-07-28Tested with Apache DataFusion 45.2.0

Reviewed by the Telemetry product team on . We checked the SQL syntax, required event fields, sample results, and limits on using the query. Who reviews this page

Question answered

Which privileged actions fail or require human review most often?

Record the actor's role, action, resource class, and final result using defined categories. The query ranks actions by denied or failed activity and includes the total reviewed count.

Event schema

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampAction completion time in UTC.
actor_roleUtf8Bounded role class; avoid email addresses in broad reporting tables.
actionUtf8Controlled privileged-action name.
resource_typeUtf8Resource class without raw resource contents.
outcomeUtf8Terminal success, denied, or failed outcome.
requires_reviewBooleanWhether policy requires a human review.
environmentUtf8Deployment environment.
DataFusion SQL

Copy the query

sql
SELECT
  action,
  COUNT(*) AS actions,
  SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END) AS failed_actions,
  SUM(CASE WHEN requires_review THEN 1 ELSE 0 END) AS review_required,
  100.0 * SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS failure_rate_pct
FROM security_audit_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
  AND environment = 'production'
GROUP BY action
ORDER BY failed_actions DESC, failure_rate_pct DESC, action;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. We review the synthetic sample output separately. Check field types, thresholds, and counting rules against your own data. Read the testing methodology.

Query result

Privileged-action failure rate

Data exports have the highest denied or failed share in the synthetic review set.

actionactionsfailed_actionsreview_requiredfailure_rate_pct
data_exported21150
permissions_changed31233.33
api_key_created2010

Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.

Privileged-action failure rate: static chart of synthetic failure_rate_pct values from the Audit privileged actions with SQL example result
Download this SVG chart of the sample results for an article, runbook, or design review. Please credit Telemetry.

Reproduce the example

Download the sample data

The JSON bundle includes the event schema with field types, reproducible input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

Run in browserSample JSONExpected output CSVStatic SVG chart

How the SQL works

  1. 1Controlled action names make the report stable enough for policy reviews and alerts.
  2. 2Failure rate is shown beside absolute review volume so a rare action does not create false urgency.
  3. 3Role and resource class support investigation without putting credentials, object contents, or unrestricted identifiers in the reporting result.

Edge cases to check

  • Define whether policy-denied actions are expected controls or incidents before alerting.
  • Keep immutable source records and retention policy outside this aggregate query.
  • Use approved actor identifiers only in restricted drill-downs with access logging.

Recommended dashboard

  • Bars: failure_rate_pct by privileged action
  • Trend: denied and failed actions by day
  • Review queue: requires_review by action and actor_role

Alert guidance

Alert on a new action class, repeated denied attempts, or review backlog after the security owner approves thresholds.

Read alert setup

Set up the events this query needs

Related instrumentation and guides

Security audit analytics use caseSecurity audit SQL guideSensitive-data redaction

Continue the analysis

Analyze authentication failure rate

Measure authentication failures by method and reason while keeping traffic volume and affected identities visible.

Open recipe

Audit AI agent tool authorization decisions

Review allowed, denied, and approval-required agent tool decisions by risk class without collecting prompts, tool arguments, results, or credentials.

Open recipe

Detect suspicious authentication bursts

Find short authentication windows with repeated failures across many identities or coarse network sources.

Open recipe

Run it on your events

Create a table, adapt the fields, and save the result

Start free, send structured events, and use the query result as a chart, shared dashboard widget, or alert input.

Get an API key