Apply filters to SQL queries

Project description

My organization is currently engaged in the process of enhancing the security of its system. I am responsible for the security of the system, the investigation of all potential security issues, and the timely updating of employee computers as required. The subsequent procedures illustrate how I implemented SQL with filters to execute security-related duties.

Retrieve after hours failed login attempts

A prospective security incident transpired after the close of business at 18:00. It is necessary to investigate all unsuccessful login attempts that occur after hours.

The SQL query that I developed to filter for failed login attempts that occurred after business hours is illustrated in the following code.

This picture shows my query in the first part and some of the results in the second. This query only looks for failed tries to log in that happened after 18:00. To begin, I chose all the information from the log_in_attempts table. Then, I used an AND operator with a WHERE clause to narrow down my results to only show failed login tries that happened after 18:00. The first condition is that login_time > "18:00", which only shows tries to log in that happened after that time. The second condition is success = FALSE, which checks for login tries that failed. 

Retrieve login attempts on specific dates

On February 25, 2022, a strange event took place. Any login action that took place on or before February 5, 2022, needs to be looked into.

The code below shows how I made a SQL query to find attempts to log in that happened on certain times. 

My query appears in the first section of the screenshot, followed by a section of the result in the second. All login attempts made on 2022-05-08 or 2022-05-09 are returned by this query. I began by choosing every piece of information from the log_in_attempts table. I then filtered my results using a WHERE clause and an OR operator to produce just login attempts that took place on 2022-05-08 or 2022-05-09. To filter for logins on 2022-05-09, the first condition is login_date = '2022-05-09'. login_date = '2022-05-08' is the second condition, which filters for logins on 2022-05-08.

Retrieve login attempts outside of Mexico

Because I looked into the organization's data on login tries, I think there is a problem with the ones that happened outside of Mexico. These attempts to log in should be looked into.

The code below shows how I made a SQL query to look for tries to log in that happened outside of Mexico. 

This picture shows my query in the first part and some of the results in the second. This query shows all attempts to log in that happened in places other than Mexico. To begin, I chose all the information from the log_in_attempts table. After that, I used a WHERE sentence with NOT to find countries other than Mexico. Because the information shows Mexico as MEX and MEXICO, I used LIKE with MEX% as the pattern to match. When used with LIKE, the percentage sign (%) stands for any number of characters that aren't defined. 

Retrieve employees in Marketing

My team intends to upgrade the devices of specific employees in the Marketing department. In order to accomplish this, I must obtain information regarding which employee machines require updating.

The SQL query I developed to filter for employee workstations from the Marketing department in the East building is illustrated in the following code.

This picture shows my query in the first part and some of the results in the second. The query shows a list of all the people who work in the East building's Marketing department. To begin, I chose all data from the employees table. Then, I used an AND in a WHERE clause to find employees who work in both the East building and the Marketing department. To match, I used LIKE with East% because the data in the office column is from the East building and has a specific office number. The first condition is department = "Marketing," which only shows employees who work in the Marketing area. The second condition is the office LIKE "East%" part, which only shows employees who work in the East building.

Retrieve employees in Finance or Sales

The machines that employees in the Sales and Finance offices use also need to be updated. I only need to get details on employees from these two departments because I need a different security update.

The code below shows how I made a SQL query to find employee machines that belong to employees who work in the Finance or Sales departments.

The first section of the screenshot depicts my query, while the second part shows a subset of the results. This query retrieves all personnel from the Finance and Sales departments. First, I selected all data from the employees table. Then I used a WHERE clause with OR to look for personnel in the Finance and Sales departments. I used the OR operator rather than AND because I want all employees to be in either department. The first criterion, department = 'Finance', searches for employees in the Finance department. The second criterion, department = 'Sales', searches for employees in the Sales department.

Retrieve all employees not in IT

One more security update needs to be made for employees who aren't in the IT area by my team. Before I can make the change, I need to find out more about these employees.

The steps below show how I made a SQL query to find employee machines that belong to people who aren't in the IT department.

The first section of the screenshot depicts my query, while the second part shows a subset of the results. The query returns all employees who do not work in the information technology department. First, I selected all data from the employees table. Then, using a WHERE clause with NOT, I filtered for employees who did not work in this department.

Summary

To obtain particular information about login attempts and employee machines, I used SQL queries with filters. I utilized two separate tables: log_in_attempts and employees. I used the AND, OR, and NOT operators to find the exact information required for each task. I also used the LIKE and percentage symbol (%) wildcards to filter for patterns.