Lab 05 — SQL Security Analytics Lab

Mission Information

Difficulty: Intermediate
Estimated Time: 120–150 minutes
Primary Language: SQL
Security Domain: SOC / Vulnerability Management / Identity Security / GRC / Security Analytics
Environment: Local SQLite lab
Automation Type: Defensive Security Data Analysis

Mission

Your task is to build and investigate a synthetic enterprise security database.

You will create tables representing:

USERS

ASSETS

LOGIN EVENTS

VULNERABILITIES

INCIDENTS

Then you will use SQL to answer practical cybersecurity questions.

Examples:

WHO HAS THE MOST FAILED LOGINS?

WHICH SOURCE IP TARGETED
THE MOST ACCOUNTS?

WHICH ADMINISTRATORS
DO NOT HAVE MFA?

WHICH CRITICAL ASSETS
HAVE OPEN CRITICAL VULNERABILITIES?

WHICH ASSETS
HAVE NO OWNER?

WHICH INCIDENTS
REMAIN UNASSIGNED?

The workflow will be:

SECURITY QUESTION
      ↓
IDENTIFY TABLES
      ↓
SELECT
      ↓
FILTER
      ↓
JOIN
      ↓
GROUP
      ↓
AGGREGATE
      ↓
INTERPRET
      ↓
REPORT

Why This Lab Matters

Security platforms store enormous amounts of structured information.

Examples include:

SIEM EVENTS

IDENTITY DATA

ASSET INVENTORIES

VULNERABILITY FINDINGS

INCIDENT RECORDS

CLOUD SECURITY FINDINGS

COMPLIANCE EVIDENCE

SQL allows security professionals to move from:

MILLIONS OF RECORDS

to:

ONE ANSWER

For example:

Which privileged accounts
do not have MFA?

or:

Which critical production assets
have unresolved critical findings?

These are security questions.

SQL is simply the tool used to answer them.

Learning Objectives

By completing this lab, you should be able to:

CREATE A SECURITY DATABASE

CREATE TABLES

INSERT SYNTHETIC DATA

USE SELECT

USE WHERE

USE AND / OR

USE IN

USE LIKE

USE ORDER BY

USE LIMIT

USE DISTINCT

USE COUNT

USE GROUP BY

USE HAVING

USE INNER JOIN

USE LEFT JOIN

USE CASE

USE SUBQUERIES

USE COMMON TABLE EXPRESSIONS

CORRELATE USERS AND EVENTS

CORRELATE ASSETS AND VULNERABILITIES

BUILD SECURITY METRICS

GENERATE ANALYST FINDINGS

Final Architecture

             SECURITY DATABASE
                     ↓
       ┌─────────────┼─────────────┐
       ↓             ↓             ↓
     USERS         ASSETS      LOGIN EVENTS
       ↓             ↓             ↓
       └─────────────┼─────────────┘
                     ↓
             VULNERABILITIES
                     ↓
                INCIDENTS
                     ↓
                    SQL
                     ↓
          FILTER / JOIN / GROUP
                     ↓
              SECURITY INSIGHT
                     ↓
                ANALYST REPORT

Authorization and Safety

This lab uses:

SYNTHETIC SECURITY DATA

Do not run modification queries against production security databases unless you are specifically authorized.

For learning and investigation, prefer:

READ-ONLY QUERIES

01 — Choose the Database Engine

For this lab, use:

SQLite

Why?

FREE

LIGHTWEIGHT

LOCAL

NO SERVER REQUIRED

PORTABLE

EASY TO RESET

02 — Verify SQLite

Run:

sqlite3 --version

If available, you are ready.

Alternatively, you can use:

DB Browser for SQLite

VS Code SQLite extension

Python sqlite3 module

03 — Create the Lab Workspace

Create:

sql-security-analytics/
|
+-- database/
|
+-- queries/
|
+-- reports/
|
+-- README.md

Linux/macOS:

mkdir -p sql-security-analytics/{database,queries,reports}
cd sql-security-analytics

PowerShell:

mkdir sql-security-analytics

cd sql-security-analytics

mkdir database
mkdir queries
mkdir reports

04 — Create the Database

Run:

sqlite3 database/security-lab.db

You should enter the SQLite prompt:

sqlite>

05 — Enable Headers

Inside SQLite:

.headers on

06 — Improve Display

Run:

.mode column

07 — Create the Users Table

Run:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    department TEXT,
    role TEXT,
    privileged INTEGER NOT NULL DEFAULT 0,
    mfa_enabled INTEGER NOT NULL DEFAULT 0,
    account_status TEXT NOT NULL
);

08 — Understand the Users Schema

Fields:

user_id
Unique user identifier

username
Account name

department
Business department

role
Job role

privileged
0 = standard
1 = privileged

mfa_enabled
0 = no
1 = yes

account_status
active / disabled / locked

09 — Insert Synthetic Users

Run:

INSERT INTO users
(
    user_id,
    username,
    department,
    role,
    privileged,
    mfa_enabled,
    account_status
)
VALUES
(1, 'admin01', 'IT', 'Systems Administrator', 1, 1, 'active'),
(2, 'admin02', 'IT', 'Cloud Administrator', 1, 0, 'active'),
(3, 'analyst01', 'Security', 'SOC Analyst', 1, 1, 'active'),
(4, 'user01', 'Finance', 'Accountant', 0, 1, 'active'),
(5, 'user02', 'HR', 'HR Specialist', 0, 0, 'active'),
(6, 'user03', 'Sales', 'Sales Executive', 0, 1, 'active'),
(7, 'service_backup', 'IT', 'Service Account', 1, 0, 'active'),
(8, 'former_user', 'Sales', 'Former Employee', 0, 0, 'disabled');

10 — Verify Users

Run:

SELECT *
FROM users;

11 — Create the Assets Table

Run:

CREATE TABLE assets (
    asset_id INTEGER PRIMARY KEY,
    hostname TEXT NOT NULL UNIQUE,
    ip_address TEXT NOT NULL,
    operating_system TEXT,
    environment TEXT,
    criticality TEXT,
    owner TEXT,
    internet_exposed INTEGER NOT NULL DEFAULT 0
);

12 — Insert Synthetic Assets

Run:

INSERT INTO assets
(
    asset_id,
    hostname,
    ip_address,
    operating_system,
    environment,
    criticality,
    owner,
    internet_exposed
)
VALUES
(1, 'DC01', '10.10.10.10', 'Windows Server', 'Production', 'Critical', 'Infrastructure Team', 0),
(2, 'WEB01', '10.10.20.10', 'Ubuntu Linux', 'Production', 'Critical', 'Web Team', 1),
(3, 'APP01', '10.10.20.20', 'Ubuntu Linux', 'Production', 'High', 'Application Team', 0),
(4, 'DB01', '10.10.30.10', 'Windows Server', 'Production', 'Critical', 'Database Team', 0),
(5, 'DEV01', '10.10.40.10', 'Ubuntu Linux', 'Development', 'Medium', 'Development Team', 0),
(6, 'OLD01', '10.10.50.10', 'Windows Server', 'Production', 'High', NULL, 0);

13 — Verify Assets

Run:

SELECT *
FROM assets;

14 — Create the Login Events Table

Run:

CREATE TABLE login_events (
    event_id INTEGER PRIMARY KEY,
    event_time TEXT NOT NULL,
    username TEXT NOT NULL,
    source_ip TEXT NOT NULL,
    target_asset_id INTEGER,
    status TEXT NOT NULL,
    authentication_type TEXT,
    FOREIGN KEY (target_asset_id)
        REFERENCES assets(asset_id)
);

15 — Insert Authentication Events

Run:

INSERT INTO login_events
(
    event_id,
    event_time,
    username,
    source_ip,
    target_asset_id,
    status,
    authentication_type
)
VALUES
(1, '2026-08-29T09:00:01Z', 'admin01', '10.10.99.10', 1, 'failed', 'password'),
(2, '2026-08-29T09:00:15Z', 'admin01', '10.10.99.10', 1, 'failed', 'password'),
(3, '2026-08-29T09:00:31Z', 'admin01', '10.10.99.10', 1, 'failed', 'password'),
(4, '2026-08-29T09:01:05Z', 'admin01', '10.10.99.10', 1, 'success', 'password'),
(5, '2026-08-29T09:02:10Z', 'user01', '10.10.88.20', 2, 'failed', 'password'),
(6, '2026-08-29T09:02:15Z', 'user02', '10.10.88.20', 2, 'failed', 'password'),
(7, '2026-08-29T09:02:20Z', 'user03', '10.10.88.20', 2, 'failed', 'password'),
(8, '2026-08-29T09:02:25Z', 'admin02', '10.10.88.20', 2, 'failed', 'password'),
(9, '2026-08-29T09:03:10Z', 'user01', '10.10.60.30', 3, 'failed', 'password'),
(10, '2026-08-29T09:03:20Z', 'user01', '10.10.60.30', 3, 'failed', 'password'),
(11, '2026-08-29T09:03:30Z', 'user01', '10.10.60.30', 3, 'failed', 'password'),
(12, '2026-08-29T09:04:00Z', 'analyst01', '10.10.70.20', 1, 'success', 'mfa'),
(13, '2026-08-29T09:05:00Z', 'admin02', '10.10.70.21', 4, 'success', 'password'),
(14, '2026-08-29T09:06:00Z', 'service_backup', '10.10.30.50', 4, 'success', 'service'),
(15, '2026-08-29T09:07:00Z', 'former_user', '10.10.90.50', 2, 'failed', 'password');

16 — Create the Vulnerabilities Table

Run:

CREATE TABLE vulnerabilities (
    vulnerability_id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    finding_name TEXT NOT NULL,
    severity TEXT NOT NULL,
    cvss REAL,
    status TEXT NOT NULL,
    discovered_date TEXT,
    remediation_due_date TEXT,
    FOREIGN KEY (asset_id)
        REFERENCES assets(asset_id)
);

17 — Insert Vulnerability Data

Run:

INSERT INTO vulnerabilities
(
    vulnerability_id,
    asset_id,
    finding_name,
    severity,
    cvss,
    status,
    discovered_date,
    remediation_due_date
)
VALUES
(1, 2, 'Outdated Web Framework', 'Critical', 9.8, 'Open', '2026-08-01', '2026-08-15'),
(2, 2, 'Weak TLS Configuration', 'High', 8.1, 'Open', '2026-08-02', '2026-08-20'),
(3, 1, 'Missing Security Update', 'Critical', 9.5, 'Open', '2026-08-03', '2026-08-17'),
(4, 3, 'Outdated Application Library', 'High', 7.8, 'Open', '2026-08-05', '2026-08-25'),
(5, 4, 'Database Configuration Review', 'Medium', 5.3, 'Open', '2026-08-06', '2026-09-05'),
(6, 5, 'Development Package Finding', 'Medium', 5.0, 'Closed', '2026-08-07', '2026-09-07'),
(7, 6, 'Legacy Service Exposure', 'Critical', 9.0, 'Open', '2026-07-15', '2026-07-30');

18 — Create the Incidents Table

Run:

CREATE TABLE incidents (
    incident_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    severity TEXT NOT NULL,
    status TEXT NOT NULL,
    assigned_to TEXT,
    related_asset_id INTEGER,
    opened_time TEXT NOT NULL,
    FOREIGN KEY (related_asset_id)
        REFERENCES assets(asset_id)
);

19 — Insert Incident Data

Run:

INSERT INTO incidents
(
    incident_id,
    title,
    severity,
    status,
    assigned_to,
    related_asset_id,
    opened_time
)
VALUES
(1, 'Repeated Administrative Login Failures', 'High', 'Open', 'analyst01', 1, '2026-08-29T09:01:00Z'),
(2, 'Web Server Security Alert', 'Critical', 'Open', NULL, 2, '2026-08-29T09:10:00Z'),
(3, 'Database Authentication Review', 'Medium', 'Investigating', 'analyst01', 4, '2026-08-29T09:15:00Z'),
(4, 'Legacy Server Risk Review', 'High', 'Open', NULL, 6, '2026-08-29T09:20:00Z');

20 — Verify Tables

Run:

.tables

Expected:

assets

incidents

login_events

users

vulnerabilities

21 — Your First Security Query

Ask:

Which users are privileged?

Run:

SELECT
    username,
    department,
    role
FROM users
WHERE privileged = 1;

22 — Find Privileged Users Without MFA

Security question:

Which privileged accounts
do not have MFA?

Run:

SELECT
    username,
    department,
    role,
    account_status
FROM users
WHERE privileged = 1
AND mfa_enabled = 0;

Expected candidates include:

admin02

service_backup

23 — Important Interpretation

Do not automatically conclude:

SECURITY INCIDENT

Instead ask:

SHOULD THIS ACCOUNT SUPPORT MFA?

IS IT INTERACTIVE?

IS IT A SERVICE ACCOUNT?

WHAT OTHER CONTROLS EXIST?

IS THERE A DOCUMENTED EXCEPTION?

24 — Active Privileged Users Without MFA

Improve the query:

SELECT
    username,
    role
FROM users
WHERE privileged = 1
AND mfa_enabled = 0
AND account_status = 'active';

25 — Count Privileged Accounts

Run:

SELECT
    COUNT(*) AS privileged_accounts
FROM users
WHERE privileged = 1;

26 — Calculate MFA Coverage

Run:

SELECT
    COUNT(*) AS active_users,
    SUM(
        CASE
            WHEN mfa_enabled = 1
            THEN 1
            ELSE 0
        END
    ) AS mfa_enabled_users
FROM users
WHERE account_status = 'active';

27 — Calculate MFA Percentage

Run:

SELECT
    COUNT(*) AS active_users,

    SUM(
        CASE
            WHEN mfa_enabled = 1
            THEN 1
            ELSE 0
        END
    ) AS mfa_enabled_users,

    ROUND(
        100.0 *
        SUM(
            CASE
                WHEN mfa_enabled = 1
                THEN 1
                ELSE 0
            END
        )
        / COUNT(*),
        2
    ) AS mfa_percentage

FROM users
WHERE account_status = 'active';

28 — Identify Disabled Accounts

Run:

SELECT
    username,
    department,
    role
FROM users
WHERE account_status = 'disabled';

29 — Why Disabled Accounts Matter

A disabled account is not necessarily a problem.

But review:

DOES IT STILL HAVE APPLICATION ACCESS?

DOES IT OWN RESOURCES?

IS IT STILL IN PRIVILEGED GROUPS?

SHOULD IT BE REMOVED?

30 — Analyze Failed Logins

Run:

SELECT
    username,
    COUNT(*) AS failed_logins
FROM login_events
WHERE status = 'failed'
GROUP BY username
ORDER BY failed_logins DESC;

31 — Find Repeated Failures

Use:

SELECT
    username,
    COUNT(*) AS failed_logins
FROM login_events
WHERE status = 'failed'
GROUP BY username
HAVING COUNT(*) >= 3
ORDER BY failed_logins DESC;

32 — Why Use HAVING?

WHERE filters:

ROWS

before aggregation.

HAVING filters:

GROUPS

after aggregation.

Mental model:

WHERE
 ↓
GROUP
 ↓
HAVING

33 — Analyze Failures by Source IP

Run:

SELECT
    source_ip,
    COUNT(*) AS failed_logins
FROM login_events
WHERE status = 'failed'
GROUP BY source_ip
ORDER BY failed_logins DESC;

34 — Find High-Volume Sources

Run:

SELECT
    source_ip,
    COUNT(*) AS failed_logins
FROM login_events
WHERE status = 'failed'
GROUP BY source_ip
HAVING COUNT(*) >= 3
ORDER BY failed_logins DESC;

35 — Count Unique Users per Source

Run:

SELECT
    source_ip,
    COUNT(
        DISTINCT username
    ) AS unique_users
FROM login_events
WHERE status = 'failed'
GROUP BY source_ip
ORDER BY unique_users DESC;

36 — Identify One Source Targeting Many Accounts

Run:

SELECT
    source_ip,
    COUNT(*) AS failed_logins,
    COUNT(
        DISTINCT username
    ) AS unique_users
FROM login_events
WHERE status = 'failed'
GROUP BY source_ip
HAVING COUNT(
    DISTINCT username
) >= 3
ORDER BY unique_users DESC;

37 — Security Interpretation

One source targeting multiple accounts may deserve review.

However, legitimate explanations can include:

VPN GATEWAY

NAT DEVICE

PROXY

SHARED APPLICATION

AUTOMATION

Detection:

PATTERN

is not the same as:

CONCLUSION

38 — Join Login Events with Assets

Run:

SELECT
    l.event_time,
    l.username,
    l.source_ip,
    l.status,
    a.hostname,
    a.criticality
FROM login_events AS l
INNER JOIN assets AS a
    ON l.target_asset_id = a.asset_id
ORDER BY l.event_time;

39 — Why Joins Matter

Before:

target_asset_id = 1

After:

DC01
Critical

Security context becomes much stronger.

40 — Failed Logins Against Critical Assets

Run:

SELECT
    l.username,
    l.source_ip,
    a.hostname,
    a.criticality,
    COUNT(*) AS failed_logins
FROM login_events AS l
INNER JOIN assets AS a
    ON l.target_asset_id = a.asset_id
WHERE l.status = 'failed'
AND a.criticality = 'Critical'
GROUP BY
    l.username,
    l.source_ip,
    a.hostname,
    a.criticality
ORDER BY failed_logins DESC;

41 — Join Users with Login Events

Run:

SELECT
    l.event_time,
    l.username,
    u.role,
    u.privileged,
    u.mfa_enabled,
    l.source_ip,
    l.status
FROM login_events AS l
LEFT JOIN users AS u
    ON l.username = u.username
ORDER BY l.event_time;

42 — Why LEFT JOIN?

LEFT JOIN retains every login event even if:

USERNAME IS NOT
IN THE USER INVENTORY

That can be security-relevant.

43 — Find Unknown Users in Authentication Logs

Run:

SELECT DISTINCT
    l.username
FROM login_events AS l
LEFT JOIN users AS u
    ON l.username = u.username
WHERE u.user_id IS NULL;

In a larger dataset this might identify:

UNMANAGED ACCOUNTS

DATA QUALITY ISSUES

OLD IDENTITIES

UNEXPECTED IDENTITIES

44 — Find Disabled Accounts Attempting Login

Run:

SELECT
    l.event_time,
    l.username,
    l.source_ip,
    l.status
FROM login_events AS l
INNER JOIN users AS u
    ON l.username = u.username
WHERE u.account_status = 'disabled';

45 — Why This Deserves Review

Possible explanations include:

STALE APPLICATION CREDENTIAL

OLD DEVICE

ACCOUNT CLEANUP ISSUE

EXPECTED DENIED LOGIN

UNAUTHORIZED ATTEMPT

46 — Successful Privileged Logins Without MFA

Run:

SELECT
    l.event_time,
    l.username,
    u.role,
    l.source_ip,
    l.authentication_type,
    a.hostname
FROM login_events AS l
INNER JOIN users AS u
    ON l.username = u.username
LEFT JOIN assets AS a
    ON l.target_asset_id = a.asset_id
WHERE l.status = 'success'
AND u.privileged = 1
AND u.mfa_enabled = 0;

47 — Interpret Carefully

For:

service_backup

MFA may not be applicable if it is:

NON-INTERACTIVE

This is why security analytics needs:

BUSINESS CONTEXT

48 — Successful Admin Logins

Run:

SELECT
    l.event_time,
    l.username,
    l.source_ip,
    a.hostname,
    l.authentication_type
FROM login_events AS l
INNER JOIN users AS u
    ON l.username = u.username
LEFT JOIN assets AS a
    ON l.target_asset_id = a.asset_id
WHERE l.status = 'success'
AND u.privileged = 1
ORDER BY l.event_time;

49 — Failure Followed by Success Concept

A useful investigation pattern:

MULTIPLE FAILURES
      ↓
SUCCESS

SQL can identify candidates.

50 — Find Users with Both Failure and Success

Run:

SELECT
    username,
    SUM(
        CASE
            WHEN status = 'failed'
            THEN 1
            ELSE 0
        END
    ) AS failures,

    SUM(
        CASE
            WHEN status = 'success'
            THEN 1
            ELSE 0
        END
    ) AS successes

FROM login_events
GROUP BY username
HAVING failures >= 3
AND successes >= 1;

51 — Important Limitation

This query does not prove:

FAILURES OCCURRED
BEFORE
THE SUCCESS

It only proves both statuses exist.

Timeline matters.

52 — Timeline Query for a User

Run:

SELECT
    event_time,
    username,
    source_ip,
    status,
    authentication_type
FROM login_events
WHERE username = 'admin01'
ORDER BY event_time;

Now manually review sequence.

53 — Security Data Principle

Aggregation gives:

SUMMARY

Timeline gives:

SEQUENCE

Both matter.

54 — Vulnerability Inventory

Run:

SELECT
    vulnerability_id,
    finding_name,
    severity,
    cvss,
    status
FROM vulnerabilities
ORDER BY cvss DESC;

55 — Open Vulnerabilities

Run:

SELECT
    finding_name,
    severity,
    cvss,
    status
FROM vulnerabilities
WHERE status = 'Open'
ORDER BY cvss DESC;

56 — Critical Open Findings

Run:

SELECT
    vulnerability_id,
    finding_name,
    cvss
FROM vulnerabilities
WHERE severity = 'Critical'
AND status = 'Open'
ORDER BY cvss DESC;

57 — Join Vulnerabilities to Assets

Run:

SELECT
    a.hostname,
    a.environment,
    a.criticality,
    v.finding_name,
    v.severity,
    v.cvss,
    v.status
FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
ORDER BY v.cvss DESC;

58 — Critical Findings on Critical Assets

Run:

SELECT
    a.hostname,
    a.criticality,
    a.environment,
    a.internet_exposed,
    v.finding_name,
    v.cvss,
    v.remediation_due_date
FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
WHERE v.status = 'Open'
AND v.severity = 'Critical'
AND a.criticality = 'Critical'
ORDER BY v.cvss DESC;

59 — Why Asset Context Changes Priority

Compare:

Critical vulnerability
on isolated development VM

with:

Critical vulnerability
on internet-facing production server

Technical severity may be the same.

Risk context is not.

60 — Internet-Exposed Assets with Open Findings

Run:

SELECT
    a.hostname,
    a.ip_address,
    a.criticality,
    v.finding_name,
    v.severity,
    v.cvss
FROM assets AS a
INNER JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
WHERE a.internet_exposed = 1
AND v.status = 'Open'
ORDER BY v.cvss DESC;

61 — Count Findings per Asset

Run:

SELECT
    a.hostname,
    COUNT(
        v.vulnerability_id
    ) AS finding_count
FROM assets AS a
LEFT JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
GROUP BY a.hostname
ORDER BY finding_count DESC;

62 — Why LEFT JOIN Here?

It includes assets with:

ZERO FINDINGS

Those assets are still part of inventory.

63 — Open Finding Count per Asset

Run:

SELECT
    a.hostname,
    COUNT(
        v.vulnerability_id
    ) AS open_findings
FROM assets AS a
LEFT JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
    AND v.status = 'Open'
GROUP BY a.hostname
ORDER BY open_findings DESC;

64 — Identify Assets Without Owners

Run:

SELECT
    hostname,
    ip_address,
    environment,
    criticality
FROM assets
WHERE owner IS NULL
OR TRIM(owner) = '';

Expected:

OLD01

65 — Why Asset Ownership Matters

Without an owner:

WHO PATCHES IT?

WHO APPROVES CHANGES?

WHO RESPONDS TO ALERTS?

WHO ACCEPTS RISK?

Asset ownership is a security control.

66 — Assets Without Owners and Open Findings

Run:

SELECT
    a.hostname,
    a.criticality,
    v.finding_name,
    v.severity,
    v.status
FROM assets AS a
INNER JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
WHERE (
    a.owner IS NULL
    OR TRIM(a.owner) = ''
)
AND v.status = 'Open';

67 — Overdue Findings

Because the lab uses:

2026-08-29

as the reference date, query:

SELECT
    a.hostname,
    v.finding_name,
    v.severity,
    v.remediation_due_date
FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
WHERE v.status = 'Open'
AND v.remediation_due_date < '2026-08-29'
ORDER BY v.remediation_due_date;

68 — Production Date Logic

In a live SQLite database, you might use:

date('now')

Example:

WHERE remediation_due_date < date('now')

Use the lab’s fixed date when you want reproducible results.

69 — Overdue Critical Findings

Run:

SELECT
    a.hostname,
    a.owner,
    v.finding_name,
    v.cvss,
    v.remediation_due_date
FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
WHERE v.status = 'Open'
AND v.severity = 'Critical'
AND v.remediation_due_date < '2026-08-29'
ORDER BY v.cvss DESC;

70 — Vulnerability Prioritization Query

Run:

SELECT
    a.hostname,
    a.criticality,
    a.internet_exposed,
    v.finding_name,
    v.severity,
    v.cvss,

    CASE
        WHEN v.severity = 'Critical'
        AND a.criticality = 'Critical'
        AND a.internet_exposed = 1
            THEN 'Priority 1'

        WHEN v.severity = 'Critical'
        AND a.criticality IN (
            'Critical',
            'High'
        )
            THEN 'Priority 2'

        WHEN v.severity = 'High'
            THEN 'Priority 3'

        ELSE 'Standard Review'
    END AS remediation_priority

FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
WHERE v.status = 'Open'
ORDER BY
    remediation_priority,
    v.cvss DESC;

71 — Risk Scoring Warning

This is a:

TRAINING MODEL

A production prioritization process may include:

EXPLOITABILITY

THREAT INTELLIGENCE

BUSINESS IMPACT

DATA SENSITIVITY

EXPOSURE

COMPENSATING CONTROLS

REMEDIATION AGE

72 — Incident Inventory

Run:

SELECT *
FROM incidents
ORDER BY opened_time;

73 — Open Incidents

Run:

SELECT
    incident_id,
    title,
    severity,
    assigned_to
FROM incidents
WHERE status = 'Open';

74 — Find Unassigned Incidents

Run:

SELECT
    incident_id,
    title,
    severity,
    opened_time
FROM incidents
WHERE assigned_to IS NULL;

75 — Why IS NULL?

Do not use:

assigned_to = NULL

Use:

assigned_to IS NULL

because NULL represents:

UNKNOWN / ABSENT

rather than a normal value.

76 — Critical Unassigned Incidents

Run:

SELECT
    incident_id,
    title,
    severity,
    opened_time
FROM incidents
WHERE assigned_to IS NULL
AND severity = 'Critical';

77 — Join Incidents to Assets

Run:

SELECT
    i.incident_id,
    i.title,
    i.severity,
    i.status,
    i.assigned_to,
    a.hostname,
    a.criticality
FROM incidents AS i
LEFT JOIN assets AS a
    ON i.related_asset_id = a.asset_id
ORDER BY i.opened_time;

78 — Unassigned Incidents on Critical Assets

Run:

SELECT
    i.incident_id,
    i.title,
    i.severity,
    a.hostname,
    a.criticality
FROM incidents AS i
INNER JOIN assets AS a
    ON i.related_asset_id = a.asset_id
WHERE i.assigned_to IS NULL
AND a.criticality = 'Critical';

79 — Security Analytics Correlation

Now start combining:

ASSET CRITICALITY

VULNERABILITIES

INCIDENTS

AUTHENTICATION

This is where SQL becomes very powerful.

80 — Assets with Both Incidents and Open Vulnerabilities

Run:

SELECT DISTINCT
    a.hostname,
    a.criticality
FROM assets AS a
INNER JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
INNER JOIN incidents AS i
    ON a.asset_id = i.related_asset_id
WHERE v.status = 'Open'
AND i.status IN (
    'Open',
    'Investigating'
);

81 — Count Open Findings and Incidents per Asset

Run:

WITH finding_counts AS (
    SELECT
        asset_id,
        COUNT(*) AS open_findings
    FROM vulnerabilities
    WHERE status = 'Open'
    GROUP BY asset_id
),

incident_counts AS (
    SELECT
        related_asset_id AS asset_id,
        COUNT(*) AS active_incidents
    FROM incidents
    WHERE status IN (
        'Open',
        'Investigating'
    )
    GROUP BY related_asset_id
)

SELECT
    a.hostname,
    a.criticality,
    COALESCE(
        f.open_findings,
        0
    ) AS open_findings,
    COALESCE(
        i.active_incidents,
        0
    ) AS active_incidents

FROM assets AS a

LEFT JOIN finding_counts AS f
    ON a.asset_id = f.asset_id

LEFT JOIN incident_counts AS i
    ON a.asset_id = i.asset_id

ORDER BY
    active_incidents DESC,
    open_findings DESC;

82 — What Is a CTE?

A:

COMMON TABLE EXPRESSION

uses:

WITH

to create temporary named query results.

Mental model:

COMPLEX QUESTION
      ↓
BREAK INTO SMALLER QUESTIONS
      ↓
COMBINE RESULTS

83 — Why Use COALESCE?

If an asset has no incident:

NULL

can become:

0

using:

COALESCE(value, 0)

This makes reports easier to interpret.

84 — Security Dashboard Query

Build a high-level security summary:

SELECT
    (SELECT COUNT(*) FROM users)
        AS total_users,

    (
        SELECT COUNT(*)
        FROM users
        WHERE privileged = 1
    ) AS privileged_users,

    (
        SELECT COUNT(*)
        FROM users
        WHERE mfa_enabled = 0
        AND account_status = 'active'
    ) AS active_users_without_mfa,

    (
        SELECT COUNT(*)
        FROM login_events
        WHERE status = 'failed'
    ) AS failed_logins,

    (
        SELECT COUNT(*)
        FROM vulnerabilities
        WHERE status = 'Open'
    ) AS open_vulnerabilities,

    (
        SELECT COUNT(*)
        FROM vulnerabilities
        WHERE status = 'Open'
        AND severity = 'Critical'
    ) AS open_critical_vulnerabilities,

    (
        SELECT COUNT(*)
        FROM incidents
        WHERE status IN (
            'Open',
            'Investigating'
        )
    ) AS active_incidents;

85 — Why Metrics Need Context

Suppose:

Open vulnerabilities = 6

That alone does not tell you:

SEVERITY

ASSET CRITICALITY

AGE

EXPOSURE

OWNERSHIP

Metrics are starting points.

Not conclusions.

86 — Authentication Dashboard Query

Run:

SELECT
    status,
    COUNT(*) AS event_count
FROM login_events
GROUP BY status;

87 — Authentication by Type

Run:

SELECT
    authentication_type,
    status,
    COUNT(*) AS event_count
FROM login_events
GROUP BY
    authentication_type,
    status
ORDER BY
    authentication_type,
    status;

88 — Failed Logins by Department

Run:

SELECT
    u.department,
    COUNT(*) AS failed_logins
FROM login_events AS l
INNER JOIN users AS u
    ON l.username = u.username
WHERE l.status = 'failed'
GROUP BY u.department
ORDER BY failed_logins DESC;

89 — Failed Logins by Privilege

Run:

SELECT
    CASE
        WHEN u.privileged = 1
            THEN 'Privileged'
        ELSE 'Standard'
    END AS account_type,

    COUNT(*) AS failed_logins

FROM login_events AS l

INNER JOIN users AS u
    ON l.username = u.username

WHERE l.status = 'failed'

GROUP BY account_type;

90 — Security Question: Privileged Accounts with Failures

Run:

SELECT
    l.username,
    COUNT(*) AS failed_logins
FROM login_events AS l
INNER JOIN users AS u
    ON l.username = u.username
WHERE l.status = 'failed'
AND u.privileged = 1
GROUP BY l.username
ORDER BY failed_logins DESC;

91 — Security Question: Critical Assets with Authentication Failures

Run:

SELECT
    a.hostname,
    COUNT(*) AS failed_logins
FROM login_events AS l
INNER JOIN assets AS a
    ON l.target_asset_id = a.asset_id
WHERE l.status = 'failed'
AND a.criticality = 'Critical'
GROUP BY a.hostname
ORDER BY failed_logins DESC;

92 — Security Question: Internet-Exposed Critical Assets

Run:

SELECT
    hostname,
    ip_address,
    operating_system,
    owner
FROM assets
WHERE internet_exposed = 1
AND criticality = 'Critical';

93 — Security Question: Internet-Exposed Assets with Critical Findings

Run:

SELECT
    a.hostname,
    a.ip_address,
    a.owner,
    v.finding_name,
    v.cvss
FROM assets AS a
INNER JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
WHERE a.internet_exposed = 1
AND v.status = 'Open'
AND v.severity = 'Critical';

94 — Security Question: High-Risk Asset Without Owner

Run:

SELECT
    hostname,
    criticality,
    environment,
    ip_address
FROM assets
WHERE criticality IN (
    'Critical',
    'High'
)
AND (
    owner IS NULL
    OR TRIM(owner) = ''
);

95 — Subquery Example

Find users whose usernames appear in failed login events:

SELECT
    username,
    department,
    role
FROM users
WHERE username IN (
    SELECT DISTINCT username
    FROM login_events
    WHERE status = 'failed'
);

96 — NOT IN Caution

NULL values can make:

NOT IN

behave unexpectedly.

For many anti-match operations, consider:

NOT EXISTS

97 — NOT EXISTS Example

Find users with no authentication events:

SELECT
    u.username,
    u.department,
    u.account_status
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM login_events AS l
    WHERE l.username = u.username
);

98 — Why This Is Useful

An account with no observed usage may deserve review if it is:

PRIVILEGED

ENABLED

OLD

UNOWNED

But your login table may cover only a short time period.

Do not overinterpret limited data.

99 — Asset Data Quality Check

Find duplicated IP addresses:

SELECT
    ip_address,
    COUNT(*) AS asset_count
FROM assets
GROUP BY ip_address
HAVING COUNT(*) > 1;

100 — User Data Quality Check

Find duplicate usernames:

SELECT
    username,
    COUNT(*) AS user_count
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

The schema’s:

UNIQUE

constraint should already prevent this.

101 — NULL Ownership Metric

Run:

SELECT
    COUNT(*) AS unowned_assets
FROM assets
WHERE owner IS NULL
OR TRIM(owner) = '';

102 — Findings by Severity

Run:

SELECT
    severity,
    COUNT(*) AS finding_count
FROM vulnerabilities
WHERE status = 'Open'
GROUP BY severity
ORDER BY finding_count DESC;

103 — Use CASE for Severity Ordering

Alphabetical ordering is not security ordering.

Use:

SELECT
    severity,
    COUNT(*) AS finding_count
FROM vulnerabilities
WHERE status = 'Open'
GROUP BY severity
ORDER BY
    CASE severity
        WHEN 'Critical' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
        ELSE 5
    END;

104 — Incident Severity Summary

Run:

SELECT
    severity,
    COUNT(*) AS incident_count
FROM incidents
WHERE status IN (
    'Open',
    'Investigating'
)
GROUP BY severity;

105 — Incident Assignment Summary

Run:

SELECT
    CASE
        WHEN assigned_to IS NULL
            THEN 'Unassigned'
        ELSE assigned_to
    END AS assignment,

    COUNT(*) AS incident_count

FROM incidents
GROUP BY assignment
ORDER BY incident_count DESC;

106 — Create a Security Review Query

Build:

SELECT
    a.hostname,
    a.criticality,
    a.owner,

    COUNT(
        DISTINCT v.vulnerability_id
    ) AS open_findings,

    COUNT(
        DISTINCT i.incident_id
    ) AS active_incidents

FROM assets AS a

LEFT JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
    AND v.status = 'Open'

LEFT JOIN incidents AS i
    ON a.asset_id = i.related_asset_id
    AND i.status IN (
        'Open',
        'Investigating'
    )

GROUP BY
    a.asset_id,
    a.hostname,
    a.criticality,
    a.owner

ORDER BY
    active_incidents DESC,
    open_findings DESC;

107 — Important Join Pitfall

When joining:

ONE ASSET

to:

MULTIPLE VULNERABILITIES

and:

MULTIPLE INCIDENTS

rows may multiply.

That is why the previous query uses:

COUNT(DISTINCT ...)

This is an important analytics skill.

108 — Save Your Queries

Create:

queries/
|
+-- 01_identity_review.sql
|
+-- 02_authentication_analysis.sql
|
+-- 03_vulnerability_analysis.sql
|
+-- 04_asset_hygiene.sql
|
+-- 05_incident_analysis.sql
|
+-- 06_security_metrics.sql

109 — Identity Review File

Place queries such as:

Privileged accounts

Users without MFA

Privileged users without MFA

Disabled accounts

Unused accounts

inside:

01_identity_review.sql

110 — Authentication Analysis File

Include:

Failed logins by user

Failed logins by IP

Unique users per source

Privileged login failures

Disabled-account attempts

Successful admin logins

111 — Vulnerability Analysis File

Include:

Open findings

Critical findings

Critical assets with critical findings

Internet-exposed assets with findings

Overdue vulnerabilities

Prioritization query

112 — Asset Hygiene File

Include:

Assets without owners

Duplicate IPs

Internet-exposed critical assets

Assets without findings

High-criticality unowned assets

113 — Incident Analysis File

Include:

Open incidents

Unassigned incidents

Critical unassigned incidents

Incidents by asset

Incidents on critical systems

114 — Security Metrics File

Include:

MFA percentage

Failed login count

Open vulnerability count

Critical vulnerability count

Active incident count

Unowned asset count

115 — Export Query Results to CSV

Inside SQLite:

.headers on
.mode csv
.output reports/privileged-users-without-mfa.csv

Then run:

SELECT
    username,
    role
FROM users
WHERE privileged = 1
AND mfa_enabled = 0
AND account_status = 'active';

Restore output:

.output stdout

116 — Export Failed Login Summary

Use:

.output reports/failed-login-summary.csv

Then:

SELECT
    username,
    COUNT(*) AS failed_logins
FROM login_events
WHERE status = 'failed'
GROUP BY username
ORDER BY failed_logins DESC;

Return:

.output stdout

117 — Export Vulnerability Priority Report

Create:

reports/vulnerability-priority.csv

using the prioritization query.

118 — Generate an Analyst Report

Create:

reports/security-analysis-report.md

Suggested structure:

# SQL Security Analytics Report

## Executive Summary

## Identity Findings

## Authentication Findings

## Vulnerability Findings

## Asset Hygiene Findings

## Incident Findings

## Recommended Follow-Up

## Limitations

119 — Example Identity Finding

Finding:
Privileged accounts without MFA

Evidence:
admin02
service_backup

Review:
Validate whether MFA is technically applicable
to each account and confirm documented
compensating controls for non-interactive
service identities.

120 — Example Authentication Finding

Finding:
Repeated failed logins against admin01

Evidence:
3 failed events from 10.10.99.10,
followed by successful authentication.

Review:
Correlate with MFA, endpoint, VPN,
identity-provider, and administrative
activity before determining cause.

121 — Example Vulnerability Finding

Finding:
Critical open vulnerability on WEB01

Context:
Production
Critical asset
Internet exposed

Review:
Prioritize remediation according to the
organization's vulnerability management
process and validate compensating controls.

122 — Example Asset Finding

Finding:
OLD01 has no documented owner.

Context:
Production
High criticality
Open critical vulnerability

Review:
Establish accountable ownership and determine
whether the system should remain operational.

123 — Example Incident Finding

Finding:
Critical web-server incident remains unassigned.

Review:
Validate incident ownership and escalation
according to SOC procedures.

124 — SQL Investigation Workflow

Whenever you receive a security question:

QUESTION
   ↓
WHICH TABLE?
   ↓
WHICH ROWS?
   ↓
WHICH COLUMNS?
   ↓
NEED ANOTHER TABLE?
   ↓
JOIN
   ↓
GROUP?
   ↓
AGGREGATE?
   ↓
INTERPRET

125 — Query Quality Principle

Do not begin with:

SELECT *

for every investigation.

Ask:

WHICH COLUMNS
DO I ACTUALLY NEED?

126 — Why SELECT * Can Be Risky

Large tables may contain:

SENSITIVE DATA

LARGE PAYLOADS

UNNECESSARY COLUMNS

Prefer:

DATA MINIMIZATION

127 — Read-Only First

In security analytics, your default should often be:

SELECT

not:

UPDATE

DELETE

128 — Dangerous UPDATE Example

Never casually run:

UPDATE users
SET account_status = 'disabled';

That affects:

EVERY ROW

129 — Why WHERE Matters

Compare:

UPDATE users
SET account_status = 'disabled';

with:

UPDATE users
SET account_status = 'disabled'
WHERE username = 'example-user';

Even then, modification should only happen with proper authorization and change control.

130 — Dangerous DELETE Example

This:

DELETE FROM login_events;

removes:

EVERY EVENT

from the table.

Do not practice destructive commands against valuable datasets.

131 — Use Transactions for Controlled Changes

Conceptually:

BEGIN TRANSACTION;

Make an authorized change.

Review.

Then:

COMMIT;

or:

ROLLBACK;

For this lab, you do not need to modify the dataset after creation.

132 — Parameterized Queries

When applications construct SQL using user input, do not concatenate untrusted data into queries.

Conceptually avoid:

"SELECT ... WHERE username = '" + input + "'"

Use:

PARAMETERIZED QUERIES

through your application language.

133 — Why Parameterization Matters

It helps prevent:

SQL INJECTION

and improves query safety.

134 — SQL Injection Security Principle

Application input should follow:

USER INPUT
    ↓
VALIDATION
    ↓
PARAMETERIZED QUERY
    ↓
DATABASE

not:

USER INPUT
    ↓
STRING CONCATENATION
    ↓
SQL EXECUTION

135 — Database Least Privilege

A reporting application may need:

SELECT

but not:

DROP TABLE

DELETE

CREATE USER

ADMINISTER DATABASE

136 — Read-Only Security Analyst Role

Conceptually:

SECURITY_ANALYST
      ↓
SELECT
      ↓
SECURITY TABLES

not:

SECURITY_ANALYST
      ↓
FULL DATABASE ADMIN

137 — Protect Database Credentials

Do not hard-code:

DATABASE USERNAME

DATABASE PASSWORD

CONNECTION STRING SECRETS

inside scripts or Git repositories.

Use approved:

SECRET MANAGEMENT

138 — Database Logging

Security-sensitive databases should provide appropriate:

ACCESS LOGGING

QUERY AUDITING

ADMINISTRATIVE CHANGE LOGGING

according to organizational requirements.

139 — Backup the Lab Database

Create a backup:

cp database/security-lab.db \
database/security-lab-backup.db

PowerShell:

Copy-Item `
    ".\database\security-lab.db" `
    ".\database\security-lab-backup.db"

140 — Why Backups Matter

Security data can be:

OPERATIONALLY IMPORTANT

AUDIT RELEVANT

INCIDENT RELEVANT

Backup design should include:

CONFIDENTIALITY

INTEGRITY

RECOVERY TESTING

141 — Add an Index

Indexes can improve query performance.

Example:

CREATE INDEX idx_login_events_username
ON login_events(username);

Create another:

CREATE INDEX idx_login_events_source_ip
ON login_events(source_ip);

142 — Vulnerability Index

CREATE INDEX idx_vulnerabilities_asset
ON vulnerabilities(asset_id);

143 — Why Indexes Matter

Without suitable indexes:

LARGE TABLE
   ↓
FULL SCAN
   ↓
SLOW QUERY

With suitable indexing:

QUERY
  ↓
INDEX
  ↓
FASTER LOOKUP

But indexes also consume storage and affect writes.

144 — View Query Plan

SQLite supports:

EXPLAIN QUERY PLAN
SELECT
    username,
    COUNT(*)
FROM login_events
WHERE status = 'failed'
GROUP BY username;

You do not need to become a database administrator.

But understand that query design affects performance.

145 — Create a Security View

Create:

CREATE VIEW privileged_users AS
SELECT
    user_id,
    username,
    department,
    role,
    mfa_enabled,
    account_status
FROM users
WHERE privileged = 1;

Query:

SELECT *
FROM privileged_users;

146 — Why Views Are Useful

Views can:

SIMPLIFY COMPLEX QUERIES

STANDARDIZE REPORTING

LIMIT EXPOSED COLUMNS

depending on database platform and permissions.

147 — Create an Open Vulnerability View

Run:

CREATE VIEW open_vulnerability_details AS
SELECT
    a.hostname,
    a.criticality,
    a.owner,
    a.internet_exposed,
    v.finding_name,
    v.severity,
    v.cvss,
    v.remediation_due_date
FROM vulnerabilities AS v
INNER JOIN assets AS a
    ON v.asset_id = a.asset_id
WHERE v.status = 'Open';

148 — Query the View

Run:

SELECT *
FROM open_vulnerability_details
ORDER BY cvss DESC;

149 — Build a Priority Dashboard View

Create:

CREATE VIEW security_priority_dashboard AS
SELECT
    a.asset_id,
    a.hostname,
    a.criticality,
    a.owner,
    a.internet_exposed,

    COUNT(
        DISTINCT v.vulnerability_id
    ) AS open_findings,

    COUNT(
        DISTINCT i.incident_id
    ) AS active_incidents

FROM assets AS a

LEFT JOIN vulnerabilities AS v
    ON a.asset_id = v.asset_id
    AND v.status = 'Open'

LEFT JOIN incidents AS i
    ON a.asset_id = i.related_asset_id
    AND i.status IN (
        'Open',
        'Investigating'
    )

GROUP BY
    a.asset_id,
    a.hostname,
    a.criticality,
    a.owner,
    a.internet_exposed;

150 — Query the Dashboard

Run:

SELECT *
FROM security_priority_dashboard
ORDER BY
    active_incidents DESC,
    open_findings DESC;

151 — Investigation Challenge 01

Without copying an earlier query, determine:

Which active privileged user
without MFA had a successful login?

Expected thinking:

users
   +
login_events
   ↓
JOIN

152 — Investigation Challenge 02

Determine:

Which source IP generated failed logins
against the largest number of unique users?

Use:

COUNT(DISTINCT ...)

153 — Investigation Challenge 03

Determine:

Which critical production assets
have overdue open vulnerabilities?

You need:

assets
+
vulnerabilities

154 — Investigation Challenge 04

Determine:

Which assets have both
open incidents
and
critical vulnerabilities?

Use:

assets

vulnerabilities

incidents

155 — Investigation Challenge 05

Determine:

Which active accounts have no MFA
and at least one failed login?

156 — Investigation Challenge 06

Determine:

Which assets have no documented owner
but still have active incidents?

157 — Investigation Challenge 07

Determine:

Which source IPs generated
only failed authentication
and no successful authentication?

Think about:

GROUP BY

HAVING

158 — Investigation Challenge 08

Determine:

Which user had three or more
failed logins and at least
one successful login?

Then manually inspect the timeline.

159 — Investigation Challenge 09

Determine:

Which open vulnerability
has the earliest overdue due date?

160 — Investigation Challenge 10

Create one query that returns:

HOSTNAME

CRITICALITY

OWNER

OPEN FINDINGS

CRITICAL FINDINGS

ACTIVE INCIDENTS

for every asset.

161 — Build the Final SQL Portfolio

Your repository should contain:

sql-security-analytics/
|
+-- database/
|   +-- security-lab.db
|   +-- security-lab-backup.db
|
+-- queries/
|   +-- 01_identity_review.sql
|   +-- 02_authentication_analysis.sql
|   +-- 03_vulnerability_analysis.sql
|   +-- 04_asset_hygiene.sql
|   +-- 05_incident_analysis.sql
|   +-- 06_security_metrics.sql
|
+-- reports/
|   +-- privileged-users-without-mfa.csv
|   +-- failed-login-summary.csv
|   +-- vulnerability-priority.csv
|   +-- security-analysis-report.md
|
+-- README.md

162 — README Structure

Include:

PROJECT OVERVIEW

SECURITY USE CASE

DATABASE SCHEMA

TABLE RELATIONSHIPS

DATASET DESCRIPTION

HOW TO CREATE DATABASE

HOW TO RUN QUERIES

SECURITY QUESTIONS

SAMPLE FINDINGS

SECURITY CONSIDERATIONS

LIMITATIONS

163 — Document the Schema

Add:

USERS
  |
  | username
  ↓
LOGIN_EVENTS

ASSETS
  |
  +------→ LOGIN_EVENTS
  |
  +------→ VULNERABILITIES
  |
  +------→ INCIDENTS

164 — Entity Relationship Mental Model

          USERS
            |
            |
            ↓
      LOGIN EVENTS
            |
            ↓
          ASSETS
         /     \
        ↓       ↓
VULNERABILITIES INCIDENTS

165 — Document Limitations

Your lab does not include:

REAL-TIME SIEM INGESTION

BILLIONS OF EVENTS

MULTI-TENANT SECURITY

FULL IDENTITY GOVERNANCE

CLOUD SECURITY TABLES

EDR TELEMETRY

THREAT INTELLIGENCE TABLES

ADVANCED TEMPORAL CORRELATION

166 — Common SQL Security Analytics Mistakes

Avoid:

SELECT * EVERYWHERE

NO WHERE FILTER

WRONG JOIN TYPE

JOINING ON WRONG COLUMN

IGNORING NULL VALUES

COUNTING DUPLICATED JOIN ROWS

NO DATA NORMALIZATION

ASSUMING METRICS EQUAL RISK

NO TIME CONTEXT

NO ASSET CONTEXT

NO IDENTITY CONTEXT

RUNNING WRITE QUERIES ON PRODUCTION

HARDCODING DATABASE CREDENTIALS

167 — Manual Validation

Before trusting any query:

SELECT SMALL SAMPLE
      ↓
MANUALLY COUNT
      ↓
RUN ANALYTICS QUERY
      ↓
COMPARE RESULTS

168 — Query Validation Example

Manually review:

SELECT *
FROM login_events
WHERE source_ip = '10.10.88.20';

Count the usernames.

Then compare against:

SELECT
    source_ip,
    COUNT(DISTINCT username)
FROM login_events
WHERE source_ip = '10.10.88.20'
GROUP BY source_ip;

169 — Security Analytics Principle

Never assume:

QUERY EXECUTED SUCCESSFULLY
=
QUERY ANSWER IS CORRECT

A syntactically correct query can still contain:

BAD LOGIC

BAD JOIN

WRONG FILTER

WRONG ASSUMPTION

170 — Mission Validation Checklist

Confirm:

• SQLite environment prepared
• Security database created
• Users table created
• Assets table created
• Login events table created
• Vulnerabilities table created
• Incidents table created
• Synthetic data inserted
• Basic SELECT queries completed
• WHERE filtering used
• ORDER BY used
• GROUP BY used
• HAVING used
• COUNT used
• DISTINCT used
• INNER JOIN used
• LEFT JOIN used
• CASE used
• NULL values handled
• Subquery used
• CTE used
• Privileged users reviewed
• MFA gaps reviewed
• Failed logins analyzed
• Failed sources analyzed
• Multi-user sources identified
• Disabled-account activity reviewed
• Vulnerabilities joined to assets
• Critical findings identified
• Overdue findings identified
• Unowned assets identified
• Unassigned incidents identified
• Security metrics generated
• Query results exported
• Analyst report created
• Queries manually validated
• Database backup created
• Destructive production queries avoided
• Limitations documented

Mission Review

You started with:

SEPARATE SECURITY TABLES

containing:

USERS

ASSETS

AUTHENTICATION

VULNERABILITIES

INCIDENTS

You transformed those tables into:

SECURITY QUESTIONS
       ↓
SQL QUERIES
       ↓
FILTERING
       ↓
CORRELATION
       ↓
AGGREGATION
       ↓
SECURITY FINDINGS

What You Built

You now have a local enterprise-style security analytics database capable of answering questions about:

IDENTITY SECURITY

MFA COVERAGE

AUTHENTICATION FAILURES

PRIVILEGED ACCESS

ASSET CRITICALITY

VULNERABILITY EXPOSURE

ASSET OWNERSHIP

INCIDENT OWNERSHIP

Key Security Lesson

The most important lesson from this lab is:

SECURITY ANALYTICS
STARTS WITH
A QUESTION

Do not begin with:

WHAT QUERY CAN I WRITE?

Begin with:

WHAT SECURITY QUESTION
DO I NEED TO ANSWER?

Then identify:

WHICH DATA?

WHICH TABLES?

WHICH RELATIONSHIPS?

WHICH FILTERS?

WHICH TIMEFRAME?

WHICH CONTEXT?

Final Mental Model

SECURITY QUESTION
       ↓
IDENTIFY DATA
       ↓
SELECT
       ↓
FILTER
       ↓
JOIN
       ↓
GROUP
       ↓
AGGREGATE
       ↓
VALIDATE
       ↓
INTERPRET
       ↓
SECURITY DECISION

SQL alone does not create security insight.

The real equation is:

STRUCTURED DATA
      +
SQL
      +
SECURITY CONTEXT
      +
ANALYST JUDGMENT
      =
ACTIONABLE SECURITY INSIGHT

What’s Next?

➡️ Lab 06 — Vulnerability Data Analysis and Prioritization

The next lab takes the vulnerability concepts from this SQL lab and turns them into a complete Python-based security workflow.

You will build:

VULNERABILITY SCANNER EXPORT
          ↓
PYTHON
          ↓
VALIDATE
          ↓
NORMALIZE
          ↓
DEDUPLICATE
          ↓
ASSET CONTEXT
          ↓
RISK PRIORITIZATION
          ↓
OWNER MAPPING
          ↓
REMEDIATION REPORT

You will work with:

SEVERITY

CVSS

ASSET CRITICALITY

EXPOSURE

OWNERSHIP

REMEDIATION DUE DATES

DUPLICATE FINDINGS

and convert raw vulnerability data into an analyst-ready remediation queue.