Our organization is actively enhancing the security of its system, and I am responsible for ensuring its safety. My tasks include investigating potential security issues and updating employee computers when necessary. The steps outlined below showcase examples of how I employed SQL with filters to execute security-related tasks.
Table of contents
Open Table of contents
Retrieve After-Hours Failed Login Attempts
A potential security incident took place after business hours (post 18:00), and it is crucial to investigate all failed login attempts during this time.
The provided code illustrates how I crafted a SQL query to filter for failed login attempts that occurred after business hours.
The screenshot is divided into two parts: the first showcases my query, while the second displays a section of the output. To filter for failed login attempts occurring after 18:00, I initiated the query by selecting all data from the log_in_attempts table. Subsequently, I employed a WHERE clause with an AND operator. The first condition, login_time > '18:00', narrows down the results to login attempts after 18:00. The second condition, success = FALSE, specifically targets failed login attempts.
Retrieving Login Attempts on Specific Dates
In response to a suspicious event on 2022-05-09, I developed a SQL query to investigate any login activity on that date or the preceding day (2022-05-08). The code demonstrates how I filtered for login attempts on these specific dates.
The screenshot is divided into two parts: the first presents my query, and the second displays a segment of the output. To retrieve all login attempts on either 2022-05-09 or 2022-05-08, I initiated the query by selecting all data from the log_in_attempts table. Utilizing a WHERE clause with an OR operator, I filtered the results to display login attempts on either 2022-05-09 or 2022-05-08. The first condition, login_date = '2022-05-09', isolates logins on 2022-05-09, while the second condition, login_date = '2022-05-08', targets logins on 2022-05-08.
Retrieving Login Attempts Outside of Mexico
Following an examination of the organization’s login attempt data, I identified a potential issue with attempts outside of Mexico. It is imperative to investigate these login attempts. The provided code illustrates how I crafted a SQL query to filter for login attempts occurring outside of Mexico.
The screenshot is divided into two parts: the first presents my query, and the second displays a segment of the output. To retrieve all login attempts in countries other than Mexico, I began by selecting all data from the log_in_attempts table. Using a WHERE clause with NOT, I filtered for countries other than Mexico. Employing LIKE with the pattern 'MEX%', I matched both 'MEX' and 'MEXICO' because the dataset uses these representations. The '%' sign signifies any number of unspecified characters when combined with LIKE.
Retrieve Marketing Department Employees
In response to our team’s goal of updating computers for specific Marketing department employees, I needed information on which employee machines to update. The provided code illustrates how I created a SQL query to filter for employee machines belonging to individuals in the Marketing department located in the East building.
The screenshot comprises my query in the first part and a section of the output in the second part. To retrieve all employees in the Marketing department located in the East building, I initiated the query by selecting all data from the employees table. Using a WHERE clause with AND, I filtered for employees in both the Marketing department and the East building. The pattern 'East%' with LIKE in the office column matched the specific office numbers representing the East building. The first condition, department = 'Marketing', filters for Marketing department employees, while the second condition, office LIKE 'East%', filters for employees in the East building.
Retrieve Finance or Sales Department Employees
For updating machines in the Finance and Sales departments with a distinct security update, I created a SQL query to gather information on employees solely from these two departments. The provided code demonstrates how I filtered for employee machines from individuals in the Finance or Sales departments.
The screenshot is divided into two parts: the first displays my query, and the second presents a section of the output. To retrieve all employees in the Finance and Sales departments, I began by selecting all data from the employees table. Employing a WHERE clause with OR, I filtered for employees in either the Finance or Sales departments. Using OR instead of AND ensures inclusion of all employees in either department. The first condition, department = 'Finance', targets employees from the Finance department, while the second condition, department = 'Sales', focuses on employees from the Sales department.
Retrieve Employees not in IT
For a final security update on employees outside the Information Technology department, I crafted a SQL query to gather information on these individuals. The provided code illustrates how I filtered for employee machines from those not in the Information Technology department.
The screenshot consists of my query in the first part and a section of the output in the second part. The query aims to retrieve all employees not in the Information Technology department. The process involved selecting all data from the employees table and utilizing a WHERE clause with NOT to filter out employees in this department.
Summary
In summary, I employed filters in SQL queries to obtain precise information about login attempts and employee machines. Two tables, namely log_in_attempts and employees, were used for this purpose. The AND, OR, and NOT operators were applied to filter for specific information tailored to each task. Additionally, I utilized LIKE along with the '%' wildcard to filter for patterns.