Skip to content
Telemetry
Security and audit SQL recipe

Detect suspicious authentication bursts

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

Advancedauthentication_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 authentication windows show concentrated failure activity?

A burst view separates ordinary mistyped credentials from concentrated automated attempts. It uses privacy-safe actor and network buckets rather than raw credentials or full IP addresses.

Event schema

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampAuthentication outcome time.
actor_idUtf8Privacy-safe actor identifier.
network_bucketUtf8Approved coarse network or risk bucket.
authentication_methodUtf8Controlled authentication method.
outcomeUtf8success or failed.
DataFusion SQL

Copy the query

sql
SELECT
  date_trunc('hour', timestamp_utc) AS hour,
  network_bucket,
  authentication_method,
  COUNT(*) AS attempts,
  SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END) AS failures,
  COUNT(DISTINCT actor_id) AS targeted_actors
FROM authentication_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY date_trunc('hour', timestamp_utc), network_bucket, authentication_method
HAVING SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END) >= 20
ORDER BY failures DESC, targeted_actors 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

Authentication failures by network bucket

The proxy-risk bucket targets far more identities and has the largest failure volume.

hournetwork_bucketauthentication_methodattemptsfailurestargeted_actors
2026-07-28 18:00risk-proxypassword480451206
2026-07-28 19:00unknown-datacenterpassword12210411

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

Authentication failures by network bucket: static chart of synthetic failures values from the Detect suspicious authentication bursts 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. 1Hour, network bucket, and method create an investigation unit.
  2. 2Distinct actors separates broad spraying from repeated attempts against one identity.
  3. 3The threshold is a review candidate, not an automatic proof of abuse.

Edge cases to check

  • Privacy and security owners must approve network bucketing.
  • Shared corporate networks can create benign concentration.
  • Do not log passwords, tokens, cookies, or full IP addresses.

Recommended dashboard

  • Bars: failures by network bucket
  • Trend: targeted_actors by hour
  • Table: reviewed bursts and response outcome

Alert guidance

Route high-volume bursts to the security response process only after volume and actor thresholds are approved.

Read alert setup

Set up the events this query needs

Related instrumentation and guides

Security audit analyticsAuthentication failure rateSensitive-data controls

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