Security Audit Analytics with SQL

Security reporting becomes useful when every privileged operation has a controlled action name, resource class, actor role, policy outcome, and review state. Free-form messages make those dimensions hard to compare and can accidentally include credentials or private resource contents.

Keep the reporting contract bounded

Emit an event when a privileged action reaches a terminal outcome. Suggested fields include action, resource_type, actor_role, outcome, requires_review, policy_version, environment, and timestamp_utc. General dashboards usually need a role class, not an email address. Put approved actor and resource identifiers in a restricted investigation path with access logging.

Never collect passwords, API-key values, session tokens, authorization headers, raw exports, unrestricted request bodies, or full policy inputs. Security telemetry is not automatically an immutable compliance archive; define evidence storage, retention, and access requirements with the security owner.

Review failures and review queues

The privileged-action audit SQL recipe ranks action classes by failed or denied share and keeps absolute review volume in the same output. Its deterministic fixture can be run locally in the SQL playground.

Before alerting, classify the result:

1. An expected policy denial can show a control working.
2. Repeated denials by the same approved actor class may require investigation.
3. A new privileged action can indicate an instrumentation or rollout change.
4. A growing requires_review queue is an operational ownership problem even when every action succeeded.

Use the authentication failure recipe for sign-in outcomes rather than mixing authentication attempts with administrative actions.

Make the query operational

Dashboards should show total actions, denied or failed actions, failure rate, review-required count, and policy version. Alerts should require reviewed conditions such as repeated denied attempts, a novel action class, or an overdue review queue.

The security audit analytics use case supplies an instrumentation prompt. Also apply the sensitive-data redaction guide at the producer boundary so unsafe values never enter the table.