Skip to content
Telemetry
Security and audit SQL recipe

Analyze Authentication Failure Rate

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

Intermediateauthentication_eventsReviewed 2026-07-28Tested with Apache DataFusion 45.2.0

Reviewed by the Telemetry product team on . SQL compatibility, event contract, synthetic output, and operational caveats. Review standards and ownership

Question answered

Which authentication methods and failure reasons need investigation?

A raw failure count can reflect ordinary traffic growth. Failure rate, affected identities, and controlled reason categories help distinguish a broken login path from isolated user error without collecting credentials.

Event contract

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampAuthentication outcome time in UTC.
actor_idUtf8Privacy-safe stable identity or session identifier.
authentication_methodUtf8password, oauth, sso, passkey, or another controlled value.
outcomeUtf8success or failed.
failure_reasonUtf8Controlled category; never a password or raw provider response.
ip_countryUtf8Coarse country code derived under the product's privacy policy.
DataFusion SQL

Copy the query

sql
SELECT
  authentication_method,
  failure_reason,
  COUNT(*) AS attempts,
  COUNT(DISTINCT actor_id) AS affected_actors,
  SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END) AS failed_attempts,
  100.0 * SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS failure_rate_pct
FROM authentication_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY
  authentication_method,
  failure_reason
HAVING COUNT(*) >= 20
ORDER BY failed_attempts DESC, failure_rate_pct DESC;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. The deterministic sample output is synthetic and reviewed separately; validate field types, thresholds, and business definitions against your own data. Read the testing methodology.

Query result

Authentication failure rate by method

SSO provider timeouts affect many distinct actors and have a materially higher failure rate than expected credential errors.

authentication_methodfailure_reasonattemptsaffected_actorsfailed_attemptsfailure_rate_pct
ssoprovider_timeout2020840
passwordinvalid_credential2020525
passkeyuser_cancelled2020210

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

Authentication failure rate by method: static chart of synthetic failure_rate_pct values from the Analyze Authentication Failure Rate example result
Indexable SVG of the deterministic example output. Download it for an article, runbook, or design review with attribution.

Reproduce the example

Download the public fixture

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

Run in browserJSON fixtureExpected output CSVStatic SVG chart

How the SQL works

  1. 1A rolling 24-hour window makes a broken method or identity-provider incident visible without exposing individual credentials.
  2. 2Distinct privacy-safe actor IDs estimate blast radius separately from repeated attempts.
  3. 3A minimum-volume threshold prevents one unusual attempt from dominating the rate ranking.

Edge cases to decide

  • Do not log passwords, tokens, session cookies, full IP addresses, or raw identity-provider payloads.
  • Expected user cancellation and suspicious automated attempts need separate reason categories and response policies.
  • Country-level segmentation can be sensitive and should be collected only with a documented purpose and retention policy.

Recommended dashboard

  • Trend: failure_rate_pct by authentication method
  • Bars: affected_actors by controlled failure reason
  • Table: newest security events using privacy-safe identifiers

Alert guidance

Alert on a sustained method-level rate and affected-actor threshold; route suspicious behavior through the security response process.

Read alert setup

Put the recipe to work

Related instrumentation and guides

Security and audit-log analyticsIncident response with SQLSensitive-data controls

Define the source data

Event schemas for this analysis

authentication_attempt_completed

A terminal sign-in outcome with bounded method, result, and risk fields. It deliberately excludes credentials, tokens, raw IP addresses, and free-form provider errors.

Review schema

Continue the analysis

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

Detect Suspicious Authentication Bursts

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

Open recipe

Run it on real 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