SOC Incident Report: Investigation of a Volt Typhoon-Inspired Intrusion
A Complete Write-Up Demonstrating Real SOC Investigation Methodology
Apply Filters to SQL Queries: A Cybersecurity Use Case
Practical experience with the Google Cybersecurity Certificate
Project Overview
SQL (Structured Query Language) is a powerful tool used to create, interact with, and retrieve information from databases. As a cybersecurity professional, SQL queries are essential for filtering through large volumes of log data — helping with everything from threat hunting and root cause analysis, to asset enumeration.
This exercise demonstrates how to use SQL queries to retrieve specific data from log tables based on real-world scenarios.
Use Case 1: Retrieve Failed Login Attempts After Business Hours
A potential security incident was flagged due to suspicious login activity after normal business hours.
SQL Query:
SELECT *
FROM log_in_attempts
WHERE login_time > '18:00' AND success = FALSE;
Explanation:
SELECT *retrieves all columns from thelog_in_attemptstable.WHERE login_time > '18:00'filters records to those occurring after 6 PM.AND success = FALSEensures only failed login attempts are included.
Use Case 2: Retrieve Login Attempts on Specific Dates
To investigate a suspicious event, I needed all login activity from a particular day and the day before.
SQL Query:
SELECT *
FROM log_in_attempts
WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';
Explanation:
- Filters the
log_in_attemptstable to include only entries from May 8th or 9th, 2022.
Use Case 3: Retrieve Login Attempts Outside of Mexico
The focus of this investigation was to isolate login attempts that did not originate in Mexico.
SQL Query:
SELECT *
FROM log_in_attempts
WHERE NOT country LIKE 'MEX%';
Explanation:
- This filters out any records where the
countrycolumn starts with “MEX”, covering entries likeMEXandMEXICO. NOT ... LIKE 'MEX%'ensures we only see non-Mexico logins.
Use Case 4: Retrieve Employees in Marketing (East Office)
For a machine update task, I needed to identify employees working in the Marketing department and located in the East office.
SQL Query:
SELECT *
FROM employees
WHERE department = 'Marketing' AND office LIKE 'East%';
Explanation:
- Targets employees in the
Marketingdepartment. - Filters those whose office names start with
"East"(e.g., East-170, East-320).
Use Case 5: Retrieve Employees in Finance or Sales
To perform a department-specific update, I needed information about employees in either Finance or Sales.
SQL Query:
SELECT *
FROM employees
WHERE department = 'Finance' OR department = 'Sales';
Explanation:
- Retrieves all employees in either the
FinanceorSalesdepartments.
Use Case 6: Retrieve All Employees Not in IT
An update had already been rolled out to the IT department, so I needed to find all other employees.
SQL Query:
SELECT *
FROM employees
WHERE NOT department = 'Information Technology';
Explanation:
- Filters out all employees from the
Information Technologydepartment, returning everyone else.
Final Thoughts
These examples illustrate how SQL queries help cybersecurity professionals quickly extract relevant data from vast datasets. Whether it’s detecting unauthorized access, tracking login patterns, or organizing machine updates, SQL provides the precision and speed necessary for effective cybersecurity operations.
