Investigate a Security Breach with SQL

Use SQL queries to detect a brute-force attack and trace a data breach.

Introduction

30 Second Summary

When a company gets hacked, someone has to piece together what happened. Security analysts dig through millions of log entries looking for a trail: which account was compromised, how the attacker got in, and what they took.

In this project, you will investigate a simulated security breach using SQL queries against a PostgreSQL database filled with authentication logs and file access events. You will start with noisy raw data and progressively write more powerful queries until you uncover the full attack story hidden inside.

What You'll Build

By the end, you will run a single query and see a complete attack timeline printed in your terminal: the exact moment an attacker started probing, how fast they moved, which account they cracked, and which sensitive files they stole.

By the end of this project, you'll have:

  • A working security event database where you can query login patterns and instantly see which IP addresses are behaving maliciously.
  • A set of window function queries that reveal the second-by-second timing of a brute-force attack, proving it was automated.
  • A complete breach investigation report generated entirely through SQL, tracing the attacker's path from initial probe through data exfiltration by joining login and file access logs.
  • Secret Mission: Build an automated threat scoring system that ranks every user by risk level based on after-hours activity, failed access attempts, and sensitive file touches.

Are there any prerequisites?

You need PostgreSQL installed on your machine and VS Code ready to go. Step 1 walks you through creating the project database, so no prior SQL database setup is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Security engineers spend most of their time querying logs to find hidden attack patterns. This project puts you in their shoes with a real investigation scenario.

Set Up Your Security Database

You have committed to building a breach investigation. Now you need something to investigate.

In this step, you will create a PostgreSQL database and fill it with realistic security event data. The data contains a hidden attack story buried inside normal user activity. Your job in later steps is to uncover it using SQL.

In this step, get ready to:
  • Connect to PostgreSQL and create a dedicated project database.
  • Create three security event tables (users, login logs, file access logs).
  • Populate the tables with sample data that contains a hidden breach.
Connect to PostgreSQL and create the database

Every investigation needs a workspace. You will create a fresh database called security_lab to keep your security data isolated from anything else on your system.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows), typing Terminal (macOS) or Command Prompt (Windows), and pressing Enter.
  • Connect to PostgreSQL by running this command:
psql -U postgres
  • Enter your PostgreSQL password when prompted. This is the password you set during PostgreSQL installation.

Connection refused or password error?

  • Make sure the PostgreSQL service is running. On Windows, search for Services in the Start menu and confirm postgresql shows as Running. On macOS, try brew services start postgresql.
  • If you see a password authentication error, double-check you are using the password you set when you installed PostgreSQL.

Help me troubleshoot my psql connection

You should see a prompt ending in =# which means you are connected as a superuser.

  • Create the project database by running this command:
CREATE DATABASE security_lab;
  • Switch into the new database by running:
\c security_lab

Your prompt should change to security_lab=# confirming you are now working inside the new database.

What does \c do?

The \c command is a psql shortcut for "connect." It switches your active session to a different database without disconnecting and reconnecting.

Create the schema

Security event databases typically have three core tables: who the users are, when they logged in, and what files they touched. You will create all three now.

  • Switch to VS Code. Create a new file called setup.sql on your Desktop.
  • Add the following CREATE TABLE statements to setup.sql:
-- Create the security event tables

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE login_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    login_time TIMESTAMP NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    success BOOLEAN NOT NULL,
    user_agent VARCHAR(100)
);

CREATE TABLE file_access_logs (
    access_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    file_path VARCHAR(255) NOT NULL,
    access_time TIMESTAMP NOT NULL,
    action VARCHAR(20) NOT NULL,
    is_sensitive BOOLEAN NOT NULL,
    success BOOLEAN NOT NULL
);

What do these tables represent?

  • users stores the employee directory. Each person has a department and role.
  • login_logs records every authentication attempt. The success column tells you whether the attempt worked or failed.
  • file_access_logs tracks what files users opened, downloaded, or wrote to. The is_sensitive flag marks high-value targets.
  • Save setup.sql.
  • Switch back to your terminal where psql is running. Run the file by entering:
\i ~/Desktop/setup.sql
  • Verify the tables were created by running:
\dt

You should see a list of three tables: file_access_logs, login_logs, and users under the Schema column public.

Don't see three tables?

  • Check the file path matches where you saved setup.sql. If you saved it somewhere other than your Desktop, adjust the path in the \i command.
  • Make sure your prompt says security_lab=# (not postgres=#). If it does not, run \c security_lab first.

Help me fix my CREATE TABLE errors in psql

Populate with sample data

Empty tables cannot tell you anything. You need realistic data that mimics a real security incident. The data you are about to insert contains normal user behavior mixed with a hidden attack.

  • Return to setup.sql in VS Code. Add this INSERT statement below the CREATE TABLE statements to populate the users table:
-- Insert users
INSERT INTO users (username, department, role, created_at) VALUES
('alice',     'Engineering', 'Developer',  '2025-01-10 09:00:00'),
('bob',       'Finance',     'Analyst',    '2025-01-12 10:00:00'),
('charlie',   'HR',          'Manager',    '2025-01-15 11:00:00'),
('diana',     'Security',    'SecOps',     '2025-01-20 09:30:00'),
('temp_user', 'IT',          'Temp',       '2025-06-01 08:00:00');

Who are these users?

Four users represent normal employees going about their work. The fifth, temp_user, is a temporary IT account. Pay attention to this one later.

  • Next, add the login logs data below the users INSERT. This is the largest dataset and contains the hidden attack pattern:
-- Insert login logs (contains hidden attack story)
INSERT INTO login_logs (user_id, login_time, ip_address, success, user_agent) VALUES
-- Normal logins (alice)
(1, '2025-07-01 08:05:00', '198.51.100.10', TRUE,  'Chrome'),
(1, '2025-07-01 17:55:00', '198.51.100.10', TRUE,  'Chrome'),
(1, '2025-07-02 08:03:00', '198.51.100.10', TRUE,  'Chrome'),
-- Bob: a couple of failed attempts then success (normal typo)
(2, '2025-07-01 08:10:00', '198.51.100.11', FALSE, 'Chrome'),
(2, '2025-07-01 08:12:00', '198.51.100.11', TRUE,  'Chrome'),
(2, '2025-07-02 09:00:00', '198.51.100.11', TRUE,  'Chrome'),
-- Diana (SecOps): normal activity
(4, '2025-07-01 09:00:00', '198.51.100.20', TRUE,  'Edge'),
(4, '2025-07-01 21:30:00', '192.0.2.50',    TRUE,  'VPN-Client'),
-- temp_user: brute-force attack from 203.0.113.10 (rapid failures then success)
(5, '2025-07-02 02:00:00', '203.0.113.10', FALSE, 'Python-Requests'),
(5, '2025-07-02 02:01:00', '203.0.113.10', FALSE, 'Python-Requests'),
(5, '2025-07-02 02:02:00', '203.0.113.10', FALSE, 'Python-Requests'),
(5, '2025-07-02 02:03:00', '203.0.113.10', FALSE, 'Python-Requests'),
(5, '2025-07-02 02:04:00', '203.0.113.10', FALSE, 'Python-Requests'),
(5, '2025-07-02 02:05:00', '203.0.113.10', TRUE,  'Python-Requests'),
-- Suspicious IP probing other accounts
(1, '2025-07-02 02:10:00', '203.0.113.10', FALSE, 'Python-Requests'),
(2, '2025-07-02 02:11:00', '203.0.113.10', FALSE, 'Python-Requests'),
(3, '2025-07-02 02:12:00', '203.0.113.10', FALSE, 'Python-Requests'),
-- Charlie: one normal login
(3, '2025-07-01 10:00:00', '198.51.100.15', TRUE,  'Firefox'),
-- temp_user re-login after compromise (attacker returning)
(5, '2025-07-02 03:00:00', '203.0.113.10', TRUE,  'Python-Requests'),
-- Normal activity continues next day
(1, '2025-07-03 08:10:00', '198.51.100.10', TRUE,  'Chrome'),
(2, '2025-07-03 09:05:00', '198.51.100.11', TRUE,  'Chrome'),
(4, '2025-07-03 08:45:00', '198.51.100.20', TRUE,  'Edge'),
(3, '2025-07-03 10:15:00', '198.51.100.15', TRUE,  'Firefox');

Why does the data have inline comments?

The -- comments group each user's activity so you can read the raw data like a story. In a real security database, you would not have these labels. Your SQL queries will need to uncover the same groupings programmatically.

  • Finally, add the file access logs below the login_logs INSERT:
-- Insert file access logs (post-compromise activity)
INSERT INTO file_access_logs (user_id, file_path, access_time, action, is_sensitive, success) VALUES
-- Normal file access (alice)
(1, '/projects/webapp/index.html',   '2025-07-01 09:00:00', 'READ',     FALSE, TRUE),
(1, '/projects/webapp/config.yaml',  '2025-07-01 09:15:00', 'WRITE',    FALSE, TRUE),
-- Normal file access (bob)
(2, '/reports/q2_revenue.xlsx',      '2025-07-01 10:00:00', 'READ',     FALSE, TRUE),
-- Diana accessing sensitive (authorized)
(4, '/logs/auth_audit.log',          '2025-07-01 09:30:00', 'READ',     TRUE,  TRUE),
-- temp_user post-compromise: attacker accessing sensitive files
(5, '/data/customer_pii.csv',        '2025-07-02 02:10:00', 'READ',     TRUE,  TRUE),
(5, '/secrets/api_keys.env',         '2025-07-02 02:12:00', 'READ',     TRUE,  TRUE),
(5, '/data/employee_salaries.xlsx',  '2025-07-02 02:15:00', 'READ',     TRUE,  TRUE),
(5, '/backups/db_dump.sql',          '2025-07-02 02:20:00', 'DOWNLOAD', TRUE,  TRUE),
-- temp_user tries something denied
(5, '/admin/firewall_rules.conf',    '2025-07-02 02:25:00', 'READ',     TRUE,  FALSE),
-- Normal access next day
(1, '/projects/webapp/style.css',    '2025-07-03 08:30:00', 'WRITE',    FALSE, TRUE),
(3, '/hr/onboarding_checklist.pdf',  '2025-07-03 10:30:00', 'READ',     FALSE, TRUE);
  • Save setup.sql.
  • Switch back to your psql terminal. Run the INSERT statements by pasting them directly into psql. Copy everything from -- Insert users down to the last semicolon in setup.sql and paste it into your psql session.

You should see three confirmation messages: INSERT 0 5, INSERT 0 24, and INSERT 0 11.

What do the INSERT confirmations mean?

INSERT 0 5 means 5 rows were inserted successfully. The 0 is a legacy value that is always zero in modern PostgreSQL. Each number matches the table it populated: 5 users, 24 login events, and 11 file access records.

Before you move on, predict: how many rows do you think are in the login_logs table?

  • Confirm the data loaded correctly by running:
SELECT COUNT(*) FROM login_logs;

You should see the count 24 returned. That confirms all login events are loaded and your database is ready for investigation.

Seeing 0 rows or an error?

  • If you see 0 rows, the INSERT statements did not run. Copy them from setup.sql again and paste directly into psql. Make sure you include the semicolons at the end of each statement.
  • If you see a relation does not exist error, confirm your prompt reads security_lab=#. Run \c security_lab if it does not.
  • If you see a foreign key violation, make sure you ran the CREATE TABLE statements before the INSERT statements. Tables must exist before data can reference them.

Help me fix my INSERT statement errors

✔️ Awesome, I've got everything!

Great. Double check you have saved setup.sql with all three CREATE TABLE statements and all three INSERT statements.

ⓧ I'd like to double check the full code

Your setup.sql should contain these sections in order. Scroll up and confirm each chunk is present:

  • CREATE TABLE users (7 lines including columns).
  • CREATE TABLE login_logs (8 lines including columns).
  • CREATE TABLE file_access_logs (9 lines including columns).
  • INSERT INTO users with 5 rows.
  • INSERT INTO login_logs with 24 rows.
  • INSERT INTO file_access_logs with 11 rows.

Your security database is loaded and the hidden attack is waiting to be found. Next up, you will start querying the data to see if anything jumps out from the raw events.

Spot the Obvious - Failed Login Attempts

Your database is loaded with security events. A real investigation starts exactly like this: you look at the raw data, searching for anything unusual.

In this step, you'll query the login logs to find failed authentication attempts. You'll quickly discover that scanning raw rows is noisy. Can you confidently spot the attacker just by reading output?

In this step, get ready to:
  • Explore the shape of each table to understand the data.
  • Filter for failed login attempts using WHERE.
  • Attempt to identify the attacker from raw results alone.
Create investigate.sql and explore the data shape

Security engineers keep a log of every query they run during an investigation. You'll save your queries in a file so you have a record of your analysis.

  • Switch back to VS Code.
  • Create a new file called investigate.sql in the same folder as your setup.sql file.
  • Add the following queries to explore what each table looks like:
-- =============================================================
-- STEP 2: Spot the Obvious - Failed Login Attempts
-- =============================================================

-- Explore the data shape
SELECT * FROM users;
SELECT * FROM login_logs ORDER BY login_time LIMIT 10;
SELECT * FROM file_access_logs ORDER BY access_time LIMIT 10;

What do these queries do?

The first query shows all 5 users in the system. The second and third queries sample the first 10 rows from the login and file access logs, ordered by time, so you get a feel for what fields are available.

  • Save investigate.sql.
  • Switch back to your psql terminal where you're connected to security_lab.
  • Run the first exploration query by pasting this into psql:
SELECT * FROM users;

You should see 5 rows: alice, bob, charlie, diana, and temp_user, each with a department and role.

  • Now run the login log sample to see what login events look like:
SELECT * FROM login_logs ORDER BY login_time LIMIT 10;

You should see 10 rows of login events. Notice each row has a user_id, a timestamp, an IP address, a success boolean, and a user_agent string.

  • Run the file access log sample:
SELECT * FROM file_access_logs ORDER BY access_time LIMIT 10;

You should see file access records with paths, actions like READ or WRITE, and an is_sensitive flag.

Not seeing any data?

Make sure your prompt reads security_lab=#. If it shows a different database name, reconnect by running \c security_lab. If tables don't exist, re-run your setup.sql file from Step 1.

Help me troubleshoot why my security_lab queries return no data.

Filter for failed logins

You've seen the raw data. Now you need to narrow it down. The first thing any security analyst does is pull failed authentication attempts. Failures are where attacks hide.

  • Add the following query to investigate.sql below your explore queries:
-- Filter for failed logins
SELECT
    l.log_id,
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.login_time;

What does this query do?

This JOINs the login logs with the users table so you can see usernames instead of raw IDs. The WHERE l.success = FALSE filter keeps only failed attempts, and ORDER BY l.login_time sorts them chronologically so you can look for patterns.

  • Save investigate.sql.
  • Before you run this, think: how many failed logins do you expect to find in a dataset of 24 login events?
  • Verify your count by running this quick check in psql:
SELECT * FROM login_logs WHERE success = FALSE;

You should see 11 rows returned. That's almost half of all login events being failures.

  • Now run the full query with usernames to see who these failures belong to:
SELECT
    l.log_id,
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.login_time;

You should see 11 rows again, but now with readable usernames, timestamps, IPs, and user agents.

What should I notice?

Scan the output. Some failures belong to bob (a normal typo). Others belong to temp_user and other accounts, all from the same IP. But with 11 rows of mixed data, it's hard to say with confidence which IP is malicious versus which is just a user mistyping their password.

Attempt to find the attacker manually

You have the failed logins, but they're scattered across multiple users and IPs. What if you sort by IP address to group failures from the same source together?

  • Add this query to the bottom of investigate.sql:
-- Try to spot the attacker by ordering and filtering
SELECT
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.ip_address, l.login_time;

What does this query do?

This is the same failed-login filter, but now sorted by ip_address first and then login_time. Grouping by IP should cluster failures from the same source so you can eyeball which IP has the most.

  • Save investigate.sql.
  • Before you run this, predict: will sorting by IP make it obvious which address is the attacker?
  • Run the query in psql:
SELECT
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.ip_address, l.login_time;

You can see the IP 203.0.113.10 appears multiple times. But how many times exactly? Is it 5 failures or 8? And how many distinct users did it target? You'd have to count the rows manually.

This is the limit of raw WHERE filtering. You can see suspicious rows exist, but you cannot confidently quantify the threat by scanning output.

Why can't I just count the rows myself?

With 11 rows, maybe you could. But real security logs have millions of events per day. Manual counting doesn't scale. You need the database to count for you, which is exactly what aggregation (GROUP BY) does.

Query returning different row counts?

If you see fewer than 11 rows, make sure your WHERE clause uses l.success = FALSE (not = 0 or = 'false'). PostgreSQL booleans use the keywords TRUE and FALSE directly.

Help me troubleshoot my failed login query.

✔️ Awesome, I've got everything!

Great. Double check you've saved investigate.sql with all three query sections.

ⓧ I'd like to double check the full code

Here's the full investigate.sql so far. Make sure yours matches:

-- =============================================================
-- STEP 2: Spot the Obvious - Failed Login Attempts
-- =============================================================

-- Explore the data shape
SELECT * FROM users;
SELECT * FROM login_logs ORDER BY login_time LIMIT 10;
SELECT * FROM file_access_logs ORDER BY access_time LIMIT 10;
-- Filter for failed logins
SELECT
    l.log_id,
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.login_time;
-- Try to spot the attacker by ordering and filtering
SELECT
    u.username,
    l.login_time,
    l.ip_address,
    l.user_agent
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.success = FALSE
ORDER BY l.ip_address, l.login_time;

You've proven that raw WHERE filters can show you failures exist, but can't tell you which IP is the real threat. Next up, you'll use GROUP BY and HAVING to make the database count for you and surface the suspicious IPs automatically.

Surface the Suspicious IPs with Aggregation

In the last step, you filtered for failed logins and found 11 rows. Some of those are Bob mistyping his password once. Others might be an actual attacker probing your system. The raw output looked the same either way.

Aggregation changes that. GROUP BY and COUNT let you total up failures per IP address, instantly revealing which ones cross the line from "oops" into "threat."

In this step, get ready to:
  • Count failed login attempts per IP and filter for suspicious thresholds.
  • Analyze the full attack scope of the suspicious IP.
  • Identify which user account was compromised.
Count failures per IP address

Instead of scanning 11 rows one by one, you can ask PostgreSQL to group all failed logins by their source IP and count how many each one generated. The HAVING clause filters out IPs with only 1 or 2 failures (likely typos) and surfaces only the real outliers.

  • In VS Code, open investigate.sql and add the following section heading and query below your Step 2 code:
-- =============================================================
-- STEP 3: Surface the Suspicious IPs with Aggregation
-- =============================================================

-- Count failures per IP address
SELECT
    l.ip_address,
    COUNT(*) AS failed_attempts,
    COUNT(DISTINCT l.user_id) AS users_targeted
FROM login_logs l
WHERE l.success = FALSE
GROUP BY l.ip_address
HAVING COUNT(*) > 2
ORDER BY failed_attempts DESC;

What does this query do?

  • GROUP BY l.ip_address collapses all failed login rows into one row per unique IP address.
  • COUNT(*) tallies how many failed attempts came from each IP.
  • COUNT(DISTINCT l.user_id) reveals whether the IP targeted one account (possible typo) or many (credential stuffing).
  • HAVING COUNT(*) > 2 keeps only IPs with more than 2 failures, filtering out normal typos.

Before you run this, think: how many IPs do you expect to appear in the results? All of them, or just a few?

  • Save investigate.sql.
  • Switch to your psql session and run the query by pasting it in.

You should see exactly one IP in the output: 203.0.113.10 with 8 failed attempts targeting 4 distinct users. Every other IP had at most 1 failure and got filtered out by HAVING. That single row is your suspect.

Seeing zero rows?

Make sure you included WHERE l.success = FALSE. Without it, the count includes successful logins and the HAVING threshold may behave differently.

Also confirm your data loaded correctly by running SELECT COUNT(*) FROM login_logs WHERE success = FALSE; which should return 11.

Help me debug my GROUP BY query

Analyze the attack surface

You know the suspicious IP now. The next question is: what exactly was it doing? You need to see whether it ever succeeded, how many accounts it probed, and what time window the activity spans.

  • Add this query below the previous one in investigate.sql:
-- Analyze the attack surface
SELECT
    l.ip_address,
    COUNT(*) FILTER (WHERE l.success = FALSE) AS failures,
    COUNT(*) FILTER (WHERE l.success = TRUE) AS successes,
    COUNT(DISTINCT l.user_id) AS users_targeted,
    MIN(l.login_time) AS first_seen,
    MAX(l.login_time) AS last_seen
FROM login_logs l
WHERE l.ip_address = '203.0.113.10'
GROUP BY l.ip_address;

What is the FILTER clause?

The FILTER (WHERE ...) clause is a PostgreSQL feature that lets you apply a condition to a single aggregate without affecting the rest of the row. Here, one COUNT tallies only failures while another tallies only successes, all in the same query.

MIN(l.login_time) and MAX(l.login_time) give you the time window: how long this IP was active in your logs.

  • Save investigate.sql and run the query in psql.

You should see one row showing 8 failures, 2 successes, 4 users targeted, first seen at 2025-07-02 02:00:00 and last seen at 2025-07-02 03:00:00. The attacker was active for one hour and managed to break into at least one account.

Seeing a syntax error near FILTER?

The FILTER clause requires PostgreSQL 9.4 or later. Confirm you are connected to your PostgreSQL database (your prompt should show security_lab=#). If you see a different prompt, reconnect with \c security_lab.

Help me fix my FILTER clause syntax

Identify the compromised account

The suspicious IP had 2 successful logins alongside its 8 failures. That means it cracked at least one account. The next query pins down exactly which user got compromised by finding accounts where this IP both failed and eventually succeeded.

  • Add this query to investigate.sql below the attack surface query:
-- Identify the compromised account
SELECT
    u.username,
    u.department,
    u.role,
    COUNT(*) FILTER (WHERE l.success = FALSE) AS failed_attempts,
    COUNT(*) FILTER (WHERE l.success = TRUE) AS successful_logins
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE l.ip_address = '203.0.113.10'
GROUP BY u.username, u.department, u.role
HAVING COUNT(*) FILTER (WHERE l.success = TRUE) > 0;

What does this query do?

  • The JOIN connects login events to user details (name, department, role) so you can identify the person behind the user_id.
  • HAVING COUNT(*) FILTER (WHERE l.success = TRUE) > 0 keeps only users where the attacker's IP actually succeeded at least once. Failed-only accounts were probed but not cracked.
  • The result tells you exactly who to investigate further: the user, their team, and their access level.
  • Save investigate.sql and run the query in psql.

You should see one row: temp_user from the IT department with a Temp role, showing 5 failed attempts and 2 successful logins from the attacker's IP. That's your compromised account.

Seeing more than one row?

Check that your HAVING clause filters on l.success = TRUE (not FALSE). You want only accounts where the attacker succeeded, not every account they attempted.

Help me debug my compromised account query

Why is a Temp account the target?

Temporary accounts are common targets in real breaches. They often have weaker passwords, less monitoring, and broader access than they should. Attackers scan for these because they are low-hanging fruit with high potential access.

Now double check your complete investigate.sql file has all the queries from Steps 2 and 3.

✔️ Awesome, I've got everything!

Great. Confirm you have saved investigate.sql with all three Step 3 queries below your Step 2 work.

ⓧ I'd like to double check the full code

Your investigate.sql should contain the following sections in order. Scroll through your file and confirm each one is present:

  • The Step 2 section header and three queries (data shape exploration, failed login filter, ordering by IP).
  • The Step 3 section header (-- STEP 3: Surface the Suspicious IPs with Aggregation).
  • Query 1: Count failures per IP address using GROUP BY and HAVING.
  • Query 2: Analyze the attack surface with FILTER for the suspicious IP.
  • Query 3: Identify the compromised account using JOIN with HAVING on successful logins.

You now know WHO the attacker is (203.0.113.10) and WHO they compromised (temp_user). But you cannot see HOW FAST the attack happened or WHAT the attacker did after breaking in. Next up, you will use window functions to reconstruct the second-by-second attack timeline.

Reconstruct the Attack Timeline with Window Functions

You've identified the malicious IP and the compromised account. You know who was attacked, but not how fast it happened.

Speed matters because a human mistyping a password looks very different from a script hammering a login endpoint every 60 seconds. Window functions let you compare each row to its neighbours. In this step, you'll use the LAG() function to compute the time gap between consecutive login attempts, revealing the machine-gun timing of an automated brute-force tool.

In this step, get ready to:
  • Write a LAG() window function query that computes time gaps between login attempts.
  • Run the query and identify the automated attack signature from the timing pattern.
Use LAG to compute inter-login gaps

The LAG() function reaches back to the previous row in a result set and pulls a value from it. By subtracting the previous row's timestamp from the current row's timestamp, you get the exact gap between events.

  • Switch to VS Code and open investigate.sql.
  • Scroll to the bottom of the file, below the Step 3 queries.
  • Add the following query that uses LAG() to compute the gap between each of temp_user's login attempts:
-- =============================================================
-- STEP 4: Reconstruct the Attack Timeline with Window Functions
-- =============================================================

-- Use LAG to compute inter-login gaps
SELECT
    u.username,
    l.login_time,
    l.ip_address,
    l.success,
    l.user_agent,
    LAG(l.login_time) OVER (
        PARTITION BY l.user_id
        ORDER BY l.login_time
    ) AS prev_attempt,
    ROUND(
        EXTRACT(EPOCH FROM (
            l.login_time - LAG(l.login_time) OVER (
                PARTITION BY l.user_id
                ORDER BY l.login_time
            )
        )) / 60.0, 1
    ) AS gap_minutes
FROM login_logs l
JOIN users u ON l.user_id = u.user_id
WHERE u.username = 'temp_user'
ORDER BY l.login_time;

What does this query do?

  • LAG(l.login_time) OVER (PARTITION BY l.user_id ORDER BY l.login_time) grabs the timestamp of the previous login attempt for the same user.
  • EXTRACT(EPOCH FROM ...) converts the time difference into total seconds, then divides by 60 to get minutes.
  • PARTITION BY keeps the window scoped to one user. ORDER BY ensures the rows are in chronological order so LAG reaches back to the correct previous attempt.
  • The result is a gap_minutes column showing exactly how many minutes passed between each login attempt.
  • Save investigate.sql.

Before you run this, think about what you expect to see. If 203.0.113.10 is an automated tool, what kind of timing pattern would you expect between attempts?

  • Switch back to your psql terminal.
  • Copy the SELECT query (without the section header comments) and paste it into psql to run it.

You should see 8 rows for temp_user. The gap_minutes column shows 1.0 for every consecutive brute-force attempt from 203.0.113.10. Exactly 60 seconds apart, every single time.

Seeing a syntax error?

  • Check that both OVER ( clauses have matching closing parentheses. The nested structure means you need the right number of closing parens after ORDER BY l.login_time.
  • Make sure the query ends with a semicolon. Without it, psql waits for more input instead of executing.
  • Confirm you typed 'temp_user' with single quotes in the WHERE clause. Double quotes mean something different in PostgreSQL.

Still stuck? Ask for help.

Interpret the attack pattern

Look at the output closely. The first row has a NULL gap_minutes (no previous attempt to compare against). Every row after that from IP 203.0.113.10 shows exactly 1.0 minute between attempts.

What does the 60-second interval prove?

A human mistyping a password would show irregular gaps of varying lengths. They might try again after 5 seconds, then pause for 30 seconds to think, then wait a minute.

Precise 60-second intervals are the fingerprint of an automated script running in a loop with a sleep timer. The user_agent column confirms it: every attempt from that IP uses Python-Requests, a common HTTP library used in attack scripts.

This pattern maps to MITRE ATT&CK T1110.001 (Password Guessing). In a real SOC, this timing signature alone would be enough to trigger an automated block rule.

Also notice the last row: a successful login at 03:00, a full 55 minutes after the brute-force succeeded at 02:05. That's the attacker coming back after the initial break-in. The timeline is now fully reconstructed.

Why LAG instead of just sorting?

You could sort by time and eyeball the gaps in a small dataset. But in production, login tables have millions of rows across thousands of users. LAG() computes the gap per-user automatically, letting you filter for any user or threshold without manually scanning timestamps.

✔️ Awesome, I've got everything!

Great. Double check you've saved investigate.sql with the Step 4 query at the bottom.

ⓧ I'd like to double check the full code

Your investigate.sql should now contain these sections in order. Scroll through your file and confirm each one is present:

  • Step 2 header and three queries (explore data shape, filter for failed logins, spot attacker by ordering).
  • Step 3 header and three queries (count failures per IP, analyze attack surface, identify compromised account).
  • Step 4 header and one query (LAG to compute inter-login gaps for temp_user).

You now have hard evidence: the attack was automated, hitting every 60 seconds with a Python script. Next up, you'll trace what the attacker did after breaking in by joining the login logs with file access records.

Trace the Breach with JOINs

Your LAG() query proved the attack was automated. Every attempt arrived exactly 60 seconds apart, a dead giveaway for scripted brute-force tooling.

You now know WHO attacked (IP 203.0.113.10), WHO was compromised (temp_user), and HOW FAST it happened. But the real damage is what happened after the break-in. In this step, you'll JOIN the login logs with the file access logs to trace the attacker's full path from initial probe through data exfiltration.

In this step, get ready to:
  • JOIN login and file access logs into a unified attack timeline with CASE labels.
  • Generate a per-user threat summary using conditional aggregation with FILTER.
Join login and file access data into an attack timeline

Right now your login data and file access data live in separate tables. The attacker's login events are in login_logs and their file touches are in file_access_logs. To see the full story, you need to combine both into a single chronological timeline.

  • Switch to investigate.sql in VS Code and add the following query at the bottom of the file:
-- =============================================================
-- STEP 5: Trace the Breach with JOINs
-- =============================================================

-- Join login and file access data into an attack timeline
SELECT
    u.username,
    CASE
        WHEN l.log_id IS NOT NULL AND l.success = FALSE THEN 'Brute-Force Attempt'
        WHEN l.log_id IS NOT NULL AND l.success = TRUE THEN 'Successful Break-in'
        WHEN f.access_id IS NOT NULL THEN 'File Access: ' || f.action
    END AS event_type,
    COALESCE(l.login_time, f.access_time) AS event_time,
    COALESCE(l.ip_address, 'N/A') AS ip_address,
    COALESCE(f.file_path, 'N/A') AS file_path,
    CASE
        WHEN f.is_sensitive = TRUE THEN 'SENSITIVE'
        ELSE ''
    END AS sensitivity
FROM users u
LEFT JOIN login_logs l ON u.user_id = l.user_id
    AND l.ip_address = '203.0.113.10'
LEFT JOIN file_access_logs f ON u.user_id = f.user_id
WHERE u.username = 'temp_user'
    AND (l.log_id IS NOT NULL OR f.access_id IS NOT NULL)
ORDER BY event_time;

What does this query do?

  • LEFT JOIN login_logs connects login events to the user, but only for the attacker's IP (203.0.113.10). This filters out the user's normal logins.
  • LEFT JOIN file_access_logs connects file events to the same user. Together, these JOINs pull both types of activity into one result set.
  • The CASE expression labels each row as a brute-force attempt, a successful break-in, or a file access event. This is how you build a human-readable timeline.
  • COALESCE picks the first non-null value. Login rows have a login_time but no access_time, and file rows have the opposite. COALESCE merges them into a single event_time column for sorting.

Before you run this, what do you think the attacker did after successfully breaking in to the temp_user account? Did they stop, or keep going?

  • Save investigate.sql.
  • Copy the timeline query (starting from SELECT down to the closing semicolon) and paste it into your psql session to run it.

You should see a chronological timeline of events. The first rows show the brute-force attempts labeled Brute-Force Attempt, followed by Successful Break-in, then a series of file access events. The attacker touched /data/customer_pii.csv, /secrets/api_keys.env, /data/employee_salaries.xlsx, and /backups/db_dump.sql. Each sensitive file is clearly marked SENSITIVE in the output.

Why does this matter?

This is the query you would hand to an incident response team. It proves the chain of events: probe, crack, steal. The CASE labels turn raw database rows into a readable narrative that non-technical stakeholders can follow.

Seeing zero rows or an error?

  • Make sure you included AND l.ip_address = '203.0.113.10' on the same line as the first LEFT JOIN condition. Missing it returns all logins, not just the attacker's.
  • Check that the WHERE clause has u.username = 'temp_user' with single quotes around the value.
  • Confirm you're connected to security_lab. Your psql prompt should show security_lab=#.

Why isn't my attack timeline query returning results?

Generate the investigation summary

A timeline shows one user's story. A SOC analyst also needs a bird's-eye view of all users to know who needs attention first. This final query produces a per-user threat summary in a single pass.

  • Add the following query below the timeline query in investigate.sql:
-- Generate the investigation summary
SELECT
    u.username,
    u.department,
    u.role,
    COUNT(DISTINCT l.log_id) AS total_logins,
    COUNT(DISTINCT l.log_id) FILTER (WHERE l.success = FALSE) AS failed_logins,
    COUNT(DISTINCT l.ip_address) AS distinct_ips,
    COUNT(DISTINCT f.access_id) FILTER (WHERE f.is_sensitive = TRUE) AS sensitive_files_accessed,
    COUNT(DISTINCT f.access_id) FILTER (WHERE f.is_sensitive = TRUE AND f.success = FALSE) AS sensitive_access_denied
FROM users u
LEFT JOIN login_logs l ON u.user_id = l.user_id
LEFT JOIN file_access_logs f ON u.user_id = f.user_id
GROUP BY u.username, u.department, u.role
ORDER BY failed_logins DESC, sensitive_files_accessed DESC;

What does this query do?

  • LEFT JOINs connect every user to their login events and file access events. Users with no events still appear (they just show zeroes).
  • The FILTER clause is PostgreSQL's conditional aggregation syntax. COUNT(DISTINCT l.log_id) FILTER (WHERE l.success = FALSE) counts only the failed logins without needing a nested CASE expression.
  • The ORDER BY sorts users by risk: most failed logins first, then most sensitive file touches. The analyst reads top-to-bottom, worst offenders first.

Why FILTER instead of CASE WHEN?

Standard SQL uses COUNT(CASE WHEN condition THEN 1 END) for conditional counts. PostgreSQL's FILTER clause does the same thing with cleaner syntax. It reads like a sentence: count the log IDs, but only where success is false. This is the same syntax available in Snowflake and other enterprise data warehouses.

  • Save investigate.sql.
  • Copy the summary query and paste it into your psql session to run it.

You should see all 5 users listed. temp_user appears at the top with the most failed logins and 4 sensitive files accessed. Normal users like alice and diana show low or zero values across the board.

Seeing unexpected counts?

  • If counts seem inflated, make sure you're using COUNT(DISTINCT l.log_id) instead of COUNT(*). The JOINs create row duplication, and DISTINCT de-duplicates.
  • If you see an error about the FILTER keyword, confirm you're connected to security_lab on PostgreSQL (not another database engine). FILTER is PostgreSQL-specific.

Help me debug my investigation summary query.

✔️ Awesome, I've got everything!

Great. Double check you've saved investigate.sql with both the timeline query and the investigation summary.

ⓧ I'd like to double check the full code

Your investigate.sql file has grown across Steps 2 through 5. Scroll through and confirm these sections appear in order:

  • Step 2 section header, followed by the data exploration queries, the failed logins filter, and the ordering query.
  • Step 3 section header, followed by the GROUP BY failures-per-IP query, the attack surface analysis, and the compromised account query.
  • Step 4 section header, followed by the LAG() window function query for inter-login gaps.
  • Step 5 section header, followed by the attack timeline JOIN query and the investigation summary query.

Your investigation is complete. You traced the full attack story using only SQL: from the first failed probe, through the automated brute-force, to the compromised account pillaging sensitive files. The per-user summary is the kind of output a SOC analyst would use to brief their team on incident priority.

Secret mission

Build an Automated Threat Scoring System

A real SOC team doesn't manually investigate every user. They need an automated system that scores each user by risk level so analysts know who to look at first. Can you combine CTEs, conditional aggregation, CASE expressions, and the FILTER clause into a single query that ranks every user from Critical to Low?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing costs or cloud charges. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The security_lab PostgreSQL database (tables: users, login_logs, file_access_logs).
  • Local SQL files: setup.sql, investigate.sql, threat_score.sql.

Keep everything running

No action needed. Choose this if you plan to keep experimenting with the queries or extend the database with more security scenarios.

  • Your security_lab database stays in PostgreSQL and uses minimal disk space.
  • Your SQL files remain wherever you saved them in VS Code.

Pause - I'll come back to this later

Since everything is local, there is nothing to pause. Your database and files sit on disk until you explicitly remove them.

  • Close the psql terminal to free up memory.
  • Close the SQL files in VS Code.
  • Everything will be exactly where you left it when you come back.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps to delete the database and local files.

  • In your psql session, switch away from the security_lab database by running:
  • You cannot drop a database while you are connected to it.
\c postgres
  • Drop the project database by running:
DROP DATABASE security_lab;
  • Exit psql by running:
\q
  • Delete the local SQL files. On macOS or Linux, run:
rm setup.sql investigate.sql threat_score.sql
  • On Windows (PowerShell), run:
Remove-Item setup.sql, investigate.sql, threat_score.sql
  • Run these commands from the folder where you saved your SQL files.

Nice Work!

Nice Work!

You just investigated a full security breach using nothing but SQL queries. Starting from raw, noisy log data, you traced an attacker from their first probe all the way through to data exfiltration.

Here's what you learned:

  • Used GROUP BY and HAVING to aggregate login failures per IP and surface the malicious address 203.0.113.10 from noisy data.
  • Applied the LAG() window function to reconstruct the brute-force attack timeline and prove it was automated by revealing precise 60-second intervals between attempts.
  • Joined authentication and file access logs to trace the complete breach path from initial probe through sensitive data exfiltration, generating a per-user threat summary using conditional aggregation with FILTER.
  • Secret Mission: Built an automated threat scoring system that ranks all users by risk level using weighted factors, CTEs, and CASE expressions.

Ready to quiz yourself?