Lab Objective

Practise turning an authentication or patch-management question into SQL filters for dates, times, and numeric identifiers. This is a safe MariaDB-style exercise based on the Google Cybersecurity Certificate material supplied to me. It does not claim the course quiz answers or completion status as verified.

Lab Environment

  • Database: a disposable MariaDB/MySQL-compatible training database
  • Tables: log_in_attempts and machines
  • Relevant fields: login_date, login_time, event_id, success, and OS_patch_date
  • Safety: use only a training dataset. Never paste credentials, real employee data, or production log exports into a public lab write-up.

Scenario

An analyst is reviewing a recent incident. They need to identify login activity after an investigation date, constrain a review to a date range, look at out-of-hours attempts, and select an exact event when following a lead.

The purpose is not to label every unusual row an attack. The purpose is to produce a smaller, defensible queue for enrichment and triage.

Commands / Structure Practised

SQL pattern Security use
WHERE login_date > 'YYYY-MM-DD' Activity after a cutoff, excluding the cutoff date
WHERE login_date >= 'YYYY-MM-DD' Activity on or after a cutoff, including the cutoff date
WHERE login_date BETWEEN 'A' AND 'B' An inclusive date-review window
WHERE login_time < 'HH:MM:SS' Early or out-of-hours activity
WHERE event_id = 123 Follow one specific event through an investigation

Step 1 - Inspect the Schema First

Before filtering, confirm exact field names and types:

DESCRIBE log_in_attempts;
DESCRIBE machines;

Do not rely on memory for a field name such as login_time or OS_patch_date. The schema tells me whether I am comparing a date, a time, or another data type.

Step 2 - Compare an Exclusive and Inclusive Date Boundary

First, retrieve attempts strictly after a date:

SELECT event_id, username, login_date, login_time, country, success
FROM log_in_attempts
WHERE login_date > '2022-05-09';

Now include the date itself:

SELECT event_id, username, login_date, login_time, country, success
FROM log_in_attempts
WHERE login_date >= '2022-05-09';

Observation

The second query should contain every row from the first query plus any records on 2022-05-09. If it does not, stop and check the field type and actual stored values before trusting the filter.

Step 3 - Build an Inclusive Investigation Window

Narrow the review to a bounded date range:

SELECT event_id, username, login_date, login_time, country, success
FROM log_in_attempts
WHERE login_date BETWEEN '2022-05-09' AND '2022-05-11'
ORDER BY login_date, login_time;

BETWEEN includes both endpoints. That is useful when the incident window deliberately begins and ends on whole dates, but it should be stated in the case notes so another analyst knows what was included.

Step 4 - Look for Out-of-Hours Attempts

Use a time comparison to create a review queue:

SELECT event_id, username, login_date, login_time, country, success
FROM log_in_attempts
WHERE login_time < '07:00:00'
ORDER BY login_date, login_time;

An early login is a lead, not a verdict. Enrich the result with expected shift hours, travel, time zone, MFA events, device history, and whether the attempt succeeded before escalating it.

Step 5 - Follow One Event ID

When an analyst has an exact reference from a ticket or alert, retrieve that record directly:

SELECT *
FROM log_in_attempts
WHERE event_id = 1001;

Numeric values are not quoted in this example. Quoting dates and times but not numeric IDs makes the query’s data-type intent clear.

Step 6 - Apply the Same Idea to Patch Dates

Use a date range to scope an asset review:

SELECT device_id, operating_system, OS_patch_date
FROM machines
WHERE OS_patch_date BETWEEN '2021-03-01' AND '2021-09-01'
ORDER BY OS_patch_date, device_id;

This query identifies a review population. It does not independently establish whether a machine is vulnerable; I would still need the patch policy, software version, exposure, and asset criticality.

Security Takeaways

  1. A comparison operator is part of the evidence definition. > and >= answer different questions.
  2. BETWEEN is inclusive. Document that boundary so a future reviewer can reproduce the result.
  3. Time filtering creates leads, not conclusions. A 06:30 login might be suspicious, legitimate shift work, or a time-zone artifact.
  4. Use schema inspection before writing filters. Correct types and exact column names prevent silent reasoning mistakes.
  5. Keep training and production data separate. Public notes should use synthetic values and never disclose real identities or logs.

Where This Applies Beyond the Lab

The same pattern appears in SIEM searches, endpoint patch reviews, cloud audit logs, and incident timelines. First define the question and the boundary in plain language; then encode it precisely, preserve the query, and enrich the reduced result set before making a security decision.