Home›AWS Detection & IR›AWS Query Lab

Northgate Engineering / AWS estate Athena-style SQL · 8 log sources

AWS Query Lab

Write Athena-style SQL against the Northgate AWS corpus: CloudTrail, VPC flow, GuardDuty, S3 access logs, IAM, Config, Route 53 resolver and ALB access logs. Each drill hands you a blank editor and a noisy starting point, and you earn the discriminator that turns a noisy query into a detection. The result is graded against the corpus, not against a model answer.

21Drills

2Scenarios

5hOf work

8Log sources

35KRows

Read the prompt→ Write SQL→ Run→ Graded

Open the free-run console →

Practitioner Drills

Single-detection exercises, write the query, run it against the corpus, get graded. Start here.

Practitioner Live

Read the Field CloudTrail Buries

The identifiers you investigate with are buried in nested request JSON, not top-level columns. Pull the S3 bucket out of the request parameters and count object-level activity per bucket, the extraction skill every later detection depends on.

cloudtrail_logs~10 min

Practitioner Live

Catch the Reconnaissance

A stolen developer key enumerates the account. Surface the recon and only the recon: many distinct read-only calls from one external source. Breadth alone is noisy, earn the discriminator.

cloudtrail_logs~12 min

Practitioner Live

A Role Used From Outside the Network

Stolen instance credentials used away from their workload. Detect the role assumed from a non-internal source, without knowing which role in advance.

cloudtrail_logs~12 min

Practitioner Live

An Identity Being Manufactured

An escalated attacker mints a new identity to persist on. Detect any actor touching multiple identity-creation verbs, with no foreknowledge of the principal.

cloudtrail_logs~12 min

Practitioner Live

Dismantling the Monitoring

Stop the trail, delete the detector, switch off Config. Detect one principal disabling multiple security services, the deliberate teardown, not a routine change.

cloudtrail_logs~12 min

Practitioner Live

Measure the Exfiltration

Quantify what left an S3 bucket from the server access logs: the external source that pulled the most data, in objects and bytes.

s3_access_logs~10 min

Practitioner Live

Who Opened the Bucket

Find the control-plane change that exposed a bucket, the policy or public-access call, and who made it and when.

cloudtrail_logs~10 min

Practitioner Live

Data Leaving as DNS

A host smuggles data out one DNS query at a time, encoding it into subdomains. Catch it by name cardinality, not query volume, the tunnel never repeats a name.

route53_resolver_logs~12 min

Practitioner Live

The Request That Asked for Credentials

An SSRF turns the web tier into a credential thief, pointing it at the instance metadata service. Every request is external, the signal is the destination it was told to fetch.

alb_access_logs~10 min

Practitioner Live

Crossing the Account Boundary

Find the identity that assumed the organization admin role from the wrong side. The detection gates on the target role plus the legitimacy of the actor, not the AssumeRole call itself.

cloudtrail_logs~12 min

Practitioner Live

Bulk Data Leaving the Network

Exfiltration shows up as bytes on the wire. Scope the flow logs to external egress, threshold by volume, and read the one destination that moved megabytes.

vpc_flow_logs~10 min

Practitioner Live

Knocking on Locked Doors

Before escalating, an attacker probes, and most calls come back AccessDenied. Treat the denial trail as signal: the principal racking up failures is mapping its own permissions.

cloudtrail_logs~10 min

Practitioner Live

Exposed in the Config State

CloudTrail records who changed a bucket; Config records the state left behind. Read the public-access flags from the nested config and find the bucket whose state says exposed.

config_items~10 min

Practitioner Live

The Backups Disappearing

Cloud ransomware often deletes rather than encrypts. Separate the concentrated delete burst from one external source from the steady drip of lifecycle expiry.

cloudtrail_logs~10 min

Advanced Drills

Detections built on window functions, conditional aggregation, cross-source joins, and time arithmetic.

Find the Heartbeat

C2 malware phones home on a schedule. Measure the gap between consecutive DNS queries with a window function and find the domain whose interval barely varies, regularity, not volume.

route53_resolver_logs~15 min

Internal and External in One Row

A stolen instance role keeps doing internal work with a tiny external trickle. Split each role into internal and external counts with conditional aggregation so the leak shows.

cloudtrail_logs~15 min

Ask IAM What the Role Is For

External role use mixes legitimate CI roles with a stolen EC2 instance role. Join CloudTrail to the IAM trust policy and keep only ec2-trust roles seen from outside.

cloudtrail_logs + iam_principals~15 min

How Long Was the Door Open

Quantify the incident timeline: compute the span between the first and last use of a backdoor key with date_diff over the aggregate bounds.

cloudtrail_logs~12 min

The Identity That Just Showed Up

Collapse the log to each IAM user to its first-ever action with ROW_NUMBER, then surface the accounts whose debut falls inside the incident window, identities with no past.

cloudtrail_logs~15 min

Put a Name to the Alert

GuardDuty gives you findings, not culprits. Join the nested remote IP in the finding to CloudTrail and attribute the alerts to the IAM identity behind them.

guardduty_findings + cloudtrail_logs~15 min

Rank the Loudest Strangers

Rank external sources by call volume with a window function so the busiest actors rise and the flat legitimate background ties below, the rank numbers draw the line.

cloudtrail_logs~12 min

Guided Scenarios

Multi-phase incidents worked end to end, triage, investigation, containment, documentation. Included with any paid plan.

Stolen Instance Credentials

A GuardDuty finding says an EC2 role is being used from outside the VPC. Work it from alert to attribution and containment: the SSRF entry, the role abuse, the recon, the exfil, and the C2 channel.

Multi-phase~30 min

The Backdoor and the Wipe

Backups are being deleted from outside the account. Work back from the destruction to the persistence and evasion behind it, attribution, dwell time, monitoring teardown, the wipe, and the manufactured identity at its origin.

Multi-phase~30 min