Applying filters to SQL queries

In this section I will use SQL to return back queries from a database.

Scenario

You are a security professional at a large organization. Part of your job is to investigate security issues to help keep the system secure. You recently discovered some potential security issues that involve login attempts and employee machines.

Your task is to examine the organization’s data in their employees and log_in_attempts tables. You’ll need to use SQL filters to retrieve records from different datasets and investigate the potential security issues.

Retrieve after hours failed login attempts

Recently it was discovered that a potential security incident occurred after business hours(18:00). All login attempts after this time need to be investigated.

The following code demonstrates how I created a SQL query to filter for all attempts that came after the time 18:00:

The first part of the screenshot is the Query I wrote and the second part is what the output was. This query is able to filter for all times after 18:00. First I selected all data from the log_in_attempts table. Then I used the WHERE clause with the AND operator to filter results to output only login attempts that occurred after 18:00 where the attempt was unsuccessful. The first condition login_time > 18:00 filters for login attempts that occurred after 18:00. The second condition is success = FALSE, which filters for all login attempts that were a failure represented by the 0.

Retrieve login attempts on specific dates

Recently a suspicious event occurred on 2022-05-09. Any login activity that happened on 2022-05-09 or on the day before needs to be investigated.

The following code shows the SQL query I created to filter for login attempts that occurred on specific dates.

The first part of this screenshot is my query and the second part in a portion of the returned output. This query returns all login attempts that occurred on 2022-05-08 and 2022-05-09. I first started by selecting all data from the log_in_attempts table. I then used a WHERE clause with the OR operator to filter results to output only login attempts where the login data was either 2022-05-09 or 2022-05-08. The first condition login_date = ‘2022-05-09’ filters logins for that date and the second condition login_date = ‘2022-05-08’ filters logins for that date.

Retrieve login attempts outside of mexico

After investigating login attempt data I believe there is an issue with login attempts that occurred outside of Mexico and these login attempts should be investigated.

The following code shows how I created a SQL query to filter for login attempts that occurred outside of mexico.

The first part of the screenshot is my query and the second part is the output from the query. This query is able to return all login attempts that occurred in countries other than Mexico. I started by selecting all data from the log_in_attempts table and then used a WHERE clause with NOT to filter for countries that were not mexico. I used LIKE with MEX% as the pattern to match because the dataset represents Mexico as both MEX and MEXICO. The percentage sign is used to represent any character after “MEX” when being used with LIKE.

Retrieve employees in Marketing

The team wants to update the computers for certain employees within the marketing department. To be able to do this I need to get information on which employee machines need updating.

The following code demonstrates the SQL query I created to solve the issue of which machines from employees in the marketing department need updating in the East building.

The first part of the screenshot is my query and the second part is output. This query shows all employees who work in marketing in the east office. I started by selecting all data from the employees table. I then used the WHERE clause with AND to filter for employees working in marketing AND in the east building. I used LIKE with East% as the pattern to match because the numbers following East can vary as shown. The first condition is the department = ‘Marketing’ portion which filters all employees in that department. The condition that follows is the office LIKE East% portion which filters employees in the east building.

Retrieve employees in Finance or Sales

The machines in the finance and sales departments also need updating like the ones in the marketing department but with a different update. Since a different update is needed I need to get information from these two departments.

The following code shows the SQL query I created to filter for employee machines from the employees in the FInance and Sales departments.

The first part of the screenshot is my query and the second part is a portion of the output. This query is able to return all employees in the finance and sales department. I first selected all data from the employees table. I then used the WHERE clause with OR to filter for employees who are in the Finance and and Sales departments. I had to use OR instead of AND because I needed information from both departments not information where they are in both departments. The first condition is department = ‘Sales’, which filters for employees from the Sales department. The second condition is department = ‘Finance’, which filters for employees from the Finance department.

Retrieve all employees not in IT

One more security update on employees is needed, this time on employees who are not in the IT department. Information on these employees is needed.

The following query demonstrates how i filtered for employee machines from employees who are not in the IT department.

The first part of the screenshot is my query and the second part is a portion of the output from the query. This query returns all employees who are not in the IT department. I first started by selecting all data from the employees table. I followed it with a WHERE clause with the NOT operator to filter for employees who are not in the department.

Summary

During this exercise I used SQL with filters to get specific information on login attempts and employee machines. To do this I had to use two different tables, log_in_attempts and employees. I used the AND, OR, NOT operators to filter for specific information needed for each task. I also used LIKE and the percentage sign (%) to filter for patterns within this data.

*All screenshots were taken from google Qwiklabs page during the completion of labs*

Page updated

Google Sites

Report abuse