Skip to content
Telemetry
Security and audit SQL recipe

Analyze access-policy denials

Rank denied actions by policy, resource class, actor role, and affected identities.

Beginnerauthorization_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 access policies deny the most actors and actions?

A denied action may mean the policy worked, a rollout broke access, or someone tried something suspicious. Record the policy version and approved actor fields to help distinguish those cases.

Event schema

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampAuthorization decision time.
policy_nameUtf8Stable policy name and version.
actionUtf8Controlled attempted action.
resource_typeUtf8Resource class without contents.
actor_roleUtf8Bounded actor role.
actor_idUtf8Restricted privacy-safe actor identifier.
outcomeUtf8allowed or denied.
DataFusion SQL

Copy the query

sql
SELECT
  policy_name,
  action,
  resource_type,
  actor_role,
  COUNT(*) AS denied_actions,
  COUNT(DISTINCT actor_id) AS affected_actors
FROM authorization_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND outcome = 'denied'
GROUP BY policy_name, action, resource_type, actor_role
ORDER BY affected_actors DESC, denied_actions DESC;

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

Actors affected by policy denials

The workspace export policy affects the broadest actor set.

policy_nameactionresource_typeactor_roledenied_actionsaffected_actors
workspace-export-v3exportworkspacemember21486
billing-admin-v2updatesubscriptionadmin4831

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

Actors affected by policy denials: static chart of synthetic affected_actors values from the Analyze access-policy denials 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, illustrative input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

Sample JSONExpected output CSVStatic SVG chart

How the SQL works

  1. 1Policy name and version connect the result to a change owner.
  2. 2Affected actors estimates rollout blast radius.
  3. 3Resource class supports review without exposing resource contents.

Edge cases to check

  • Expected denials and suspicious attempts need different response rules.
  • Restrict drill-down actor access and audit it.
  • Keep a version mapping when you rename policies.

Recommended dashboard

  • Bars: affected_actors by policy
  • Trend: denied_actions by release
  • Table: reviewed denial groups

Alert guidance

Alert when a new policy version causes a broad denial increase or a sensitive action is repeatedly denied.

Read alert setup

Set up the events this query needs

Related instrumentation and guides

Security audit analyticsPrivileged-action auditIncident response with SQL

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

Audit privileged actions with SQL

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

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