Investigating Incidents with SQL Queries

With CloudTrail and Flow Logs cataloged, an investigation becomes a series of SQL queries that filter billions of events down to the few that matter. The skill is knowing what to ask: who did what, from where, and when. This lesson covers the query patterns investigators rely on.

When a credential is suspected compromised, you query all actions by that principal. Filtering CloudTrail on userIdentity.arn or access key ID reveals every API call it made, in order. This timeline of the attacker's actions is often the single most valuable output of an investigation.

Filtering on sourceIPAddress shows everything done from a given address. If you have one suspicious IP from a GuardDuty finding, this surfaces every call it made across services, helping you scope the blast radius and spot which resources the attacker touched.

Querying for events where errorCode is AccessDenied or UnauthorizedOperation reveals reconnaissance: an attacker testing what a stolen credential can do. A burst of denials from one principal followed by a successful call shows them finding a permission that works, a clear escalation signal.

Watch for sensitive IAM events such as CreateUser, AttachUserPolicy, CreateAccessKey, or PutUserPolicy. An attacker often tries to establish persistence by creating new identities or keys. Querying for these event names quickly reveals attempts to deepen a foothold beyond the initial entry.

Powerful investigations join data sources: match a CloudTrail principal's actions against VPC Flow Logs from the same instance, or against ALB logs for the requests it served. Correlating control-plane and network activity reconstructs the full attack story rather than isolated fragments.

Always constrain queries to a time window using the event timestamp. This focuses the investigation on the relevant period and, when combined with partitions, dramatically reduces data scanned and cost. Starting broad in time then narrowing is a natural rhythm as the picture sharpens.

Aggregations expose patterns: GROUP BY source IP to find top talkers, count distinct event names per principal to gauge breadth of activity, or tally errors over time to find a spike. These summaries turn raw rows into the trends that point you toward the real incident.

The same SQL approach applies to Flow Logs in S3: find large outbound transfers, count rejected ports per source, or list external IPs an instance contacted. Pairing CloudTrail (what was done in the API) with Flow Logs (what moved on the network) gives both halves of the story.

Save proven investigation queries as named queries or views so your team can rerun them fast during the next incident. A library of vetted queries, like "all actions by an access key" or "denied calls in the last hour," turns ad-hoc effort into a repeatable response capability.

Queries produce evidence; that evidence drives response: revoke the key, isolate the instance, remove the rogue IAM user. A disciplined query process, principal first, then IP, then escalation events, then correlation, gives you the confident, complete understanding needed to contain an incident properly.

Test your investigation querying.

Investigations are SQL queries over cataloged logs. Start with a principal or source IP, hunt denied calls for recon, watch IAM events like CreateAccessKey for escalation, and time-bound everything. Group and count to find patterns, correlate CloudTrail with Flow Logs, and save vetted named queries so evidence-gathering becomes a repeatable response capability.