Project description

For this project, I will use SQL filters to retrieve records from different datasets and investigate potential security issues. I learned that some potential security issues were discovered that involved login attempts and employee machines. I will examine the organization's data in their employees and log_in_attempts table. I will diagnose each table with multiple SQL queries to figure out what exactly happened and bring some light to those security issues. As well as find out what departments need to be updated with the new security updates.

Retrieve after hours failed login attempts

To query the log_in_attempts table and review after hours login activity will need to create a query to pull the failed login attempts that occurred after 18:00.

The following query was executed to retrieve the information:

> SELECT * → FROM log_in_attempts → WHERE login_time > '18:00:00' AND success = FALSE;

This query will grab * all information from long_in_attemps table where login_time is greater than 18:00 and the success failed. This will show me the usernames that tried to log in to the server after hours.

Retrieve login attempts on specific dates

A suspicious event occurred on 2022-05-09. To investigate this event, I want to review all login attempts which occurred on this day and the day before.

To obtain that information, I executed the following query:

> SELECT * -> FROM log_in_attempts -> WHERE login_date = '2022-05-08' OR login_date = '2022-05-09';

The output will display 75 records from those two days. The new filter 'OR' is introduced in this query. The use of 'OR' operator will retrieve the information of both days even if any of those dates do not exist.

Retrieve login attempts outside of Mexico

There's been suspicious activity with login attempts, but the team has determined that this activity did not originate from Mexico. Now, I need to investigate login attempts that occurred outside of Mexico.

To obtain that information, I executed the following query:

> SELECT * -> FROM log_in_attempts -> WHERE NOT country LIKE 'MEX%'

The output displays 144 records for all the countries except Mexico.

The 'NOT' operator will grab all the records that do not include Mexico and by including % next to MEX, it will discard any variations of MEX including MEXICO or other after the first three letters.

Retrieve employees in Marketing

My team wants to perform security updates on specific employee machines in the Marketing department. I am responsible for getting information on those employee machines and will need to query the 'employees' table.

To obtain that information, I executed the following query:

> SELECT * -> FROM employees -> WHERE department = 'Marketing' AND office LIKE 'East-%';

This query will select only the employees who belong to the Marketing department and who work in the East Offices. I used AND and LIKE operators to satisfy both criteria. LIKE will output any patterns that we need for the specific query.

Retrieve employees in Sales or Finance

My team now needs to perform a different security update on machines for employees in the Sales and Finance department. I will create a query that identifies all employees in the Sales or Finance departments.

To obtain that information, I executed the following query:

> SELECT * -> FROM employees -> WHERE department = 'Sales' OR department = 'Finance';

Within this query, I used OR operator to satisfy this criterion. This query will look for any the Finance department or Sales department records and output them.

Retrieve all employees not in IT

My team needs to make one more update to employee machines. The employees in the Information Technology (IT) department already had this update, but employees in all other departments need it. I will be using SQL to create a query that identifies all employees not in the IT department.

To obtain that information, I executed the following query:

> SELECT * -> FROM employees -> WHERE NOT department = 'Information Technology';

This query will output every employees' machines working in all the departments except for IT department. WHERE NOT operator will discard any information regarding the IT deparment. Where we can know how many employees need to be updated and deployed those update patches.

Summary

I created some filters to SQL queries to get specific information on login attempts and employee machines. I selected two different tables, log_in_attempts and employees. I used multiple operators like AND, OR, and NOT to filter the specific information needed for each task. I also used LIKE and the percentage sign (%) wildcard to filter for patterns.