Project Overview
This coursework walkthrough presents SQL filters, operators, and query examples for extracting security-relevant records. The sample tables illustrate filtering behavior; they do not establish production experience, a dataset-size benchmark, or unaided query authorship.
By applying specific filters to SQL queries, security professionals can isolate suspicious activities, detect anomalies, and generate targeted reports from security logs and databases.
Scenario
In the training scenario, an analyst reviews network and access logs for records that need further investigation. The examples cover how to:
- Filter traffic from specific IP addresses flagged as suspicious
- Identify unusual access patterns during non-business hours
- Generate summary reports of failed login attempts
- Detect potential data exfiltration by analyzing outbound traffic volumes
Implementation
The walkthrough uses the following SQL filtering techniques:
Basic Filtering with WHERE Clause
Query to identify all traffic from a suspicious IP address:
mysql> SELECT *
-> FROM network_logs
-> WHERE source_ip = '192.168.1.137'
-> ORDER BY timestamp DESC;
34 rows returned (showing first 3)
| id | timestamp | source_ip | destination_ip | port | protocol | bytes_sent |
|---|---|---|---|---|---|---|
| 1082437 | 2024-05-03 02:14:28 | 192.168.1.137 | 203.0.113.42 | 22 | SSH | 4096 |
| 1082302 | 2024-05-03 02:12:15 | 192.168.1.137 | 203.0.113.42 | 22 | SSH | 8192 |
| 1082105 | 2024-05-03 01:57:33 | 192.168.1.137 | 203.0.113.42 | 22 | SSH | 2048 |
Using Multiple Conditions with AND/OR
Identify potential unauthorized access during non-business hours:
mysql> SELECT *
-> FROM access_logs
-> WHERE (HOUR(access_time) < 8 OR HOUR(access_time) > 18)
-> AND access_status = 'success'
-> AND user_role != 'admin'
-> ORDER BY access_time;
17 rows returned (showing first 3)
| log_id | user_id | access_time | resource_accessed | access_status | user_role |
|---|---|---|---|---|---|
| 45291 | user047 | 2024-05-02 05:12:47 | /admin/config | success | standard |
| 45387 | user023 | 2024-05-02 19:34:12 | /reports/security | success | standard |
| 45412 | user047 | 2024-05-02 22:03:19 | /admin/users | success | standard |
Using the LIKE Operator for Pattern Matching
Find attempts to access sensitive directories:
mysql> SELECT COUNT(*) as attempt_count, user_id, resource_accessed
-> FROM access_logs
-> WHERE resource_accessed LIKE '%/admin/%'
-> OR resource_accessed LIKE '%/config/%'
-> OR resource_accessed LIKE '%/secure/%'
-> GROUP BY user_id, resource_accessed
-> HAVING attempt_count > 5
-> ORDER BY attempt_count DESC;
4 rows returned
| attempt_count | user_id | resource_accessed |
|---|---|---|
| 27 | user047 | /admin/config |
| 14 | user023 | /admin/users |
| 8 | user015 | /secure/keys |
| 6 | user047 | /config/database |
Advanced Filtering with Subqueries
Identify users with failed login attempts followed by successful access:
mysql> SELECT DISTINCT l1.user_id, l1.ip_address, l1.timestamp
-> as failed_time, l2.timestamp as success_time
-> FROM login_logs l1
-> JOIN login_logs l2 ON l1.user_id = l2.user_id AND l1.ip_address = l2.ip_address
-> WHERE l1.status = 'failed'
-> AND l2.status = 'success'
-> AND l2.timestamp > l1.timestamp
-> AND TIMESTAMPDIFF(MINUTE, l1.timestamp, l2.timestamp) < 30
-> ORDER BY l1.user_id, l1.timestamp;
7 rows returned (showing first 3)
| user_id | ip_address | failed_time | success_time |
|---|---|---|---|
| user015 | 192.168.1.54 | 2024-05-03 14:23:42 | 2024-05-03 14:24:15 |
| user023 | 192.168.1.78 | 2024-05-02 19:30:58 | 2024-05-02 19:34:12 |
| user047 | 192.168.1.137 | 2024-05-03 01:45:27 | 2024-05-03 01:47:38 |
Security Findings
The example query results illustrate records that could prompt further review:
Key Findings
- Potential Brute Force Attempts: Identified multiple failed login attempts followed by successful logins within short time periods
- Unauthorized Access Patterns: Detected unusual access patterns from non-admin users attempting to access sensitive resources
- Data Exfiltration Indicators: Found suspicious outbound data transfers during non-business hours
These example records would prompt further investigation of user047. Query matches alone do not establish account compromise or data exfiltration.
Conclusion
The examples show how filters, pattern matching, and joins narrow a set of records for investigation. They support a coursework explanation of query behavior, with findings kept separate from conclusions that would require additional evidence.