My organization is working to make their system more secure. It is my job to ensure the system is safe, investigate all potential security issues, and update employee computers as needed. The following steps provide examples of how I used SQL with filters to perform security-related tasks.
There was a potential security incident that occurred after business hours (after 18:00). All after-hours login attempts that failed need to be investigated.
The following code demonstrates how I created a SQL query to filter for failed login attempts that occurred after business hours.
SELECT * FROM log_in_attempts WHERE login_time > '18:00' AND success = 0;
This query filters for failed login attempts that occurred after 18:00. First, I started by selecting all data from the log_in_attempts table. Then, I used a WHERE clause with an AND operator to output only login attempts after 18:00 that were unsuccessful.
A suspicious event occurred on 2022-05-09. Any login activity that happened on 2022-05-09 or the day before needs to be investigated.
The following code demonstrates how I created a SQL query to filter for login attempts on specific dates.
SELECT * FROM log_in_attempts WHERE login_date = '2022-05-08' OR login_date = '2022-05-09';This query returns login attempts from 2022-05-08 and 2022-05-09. I used a WHERE clause with an OR operator to return entries that matched either date.
After investigating the organization’s data, I found unusual login attempts occurring outside of Mexico.
The following code demonstrates how I filtered for login attempts outside of Mexico.
SELECT * FROM log_in_attempts WHERE country NOT LIKE 'MEX%';
This query returns logins from any country except Mexico. I used NOT with LIKE and the pattern 'MEX%' to exclude values that start with “MEX”, which includes “MEX” and “MEXICO”.
My team wants to update computers for Marketing employees in the East building.
The following code demonstrates how I filtered for those employees.
SELECT * FROM employees WHERE department = 'Marketing' AND office LIKE 'East%';This query returns all employees in the Marketing department whose offices begin with "East". I used AND to combine both conditions.
The machines for Finance and Sales employees need a different update.
The following code demonstrates how I filtered for these departments.
SELECT * FROM employees WHERE department = 'Finance' OR department = 'Sales';This query uses OR to return employees from either the Finance or Sales departments.
Another security update is needed for employees not in the Information Technology department.
The following demonstrates how I filtered for them.
SELECT * FROM employees WHERE NOT department = 'Information Technology';This query excludes employees from IT, returning only those in other departments.
I applied filters to SQL queries to gather specific information from two tables: log_in_attempts and employees. I used AND, OR, NOT, and LIKE operators, and the percent sign (%) as a wildcard for pattern matching. These filters helped me investigate security incidents and plan system updates.