Project description The scenario of this project is that I’m a security professional at a large organization. Part of my job is to keep the system secure and investigate security issues. I’ve recently discovered some potential security issues that involve login attempts on employee machines. I will also need to pull up certain employees in specific departments to make updates to their machines, so I will need to use SQL with filters to get only those machines/departments to show in the log.
- Retrieve after-hours failed login attempts
- Retrieve login attempts on specific dates
- Retrieve login attempts outside of Mexico
- Retrieve employees in Marketing
- Retrieve employees in Finance or Sales
- Retrieve all employees not in IT
I was asked to investigate a potential security incident that occurred after business hours, which is 18:00. I will need to pull a log that shows those occurrences.
The screenshot below shows just how I execute that task using an SQL query and filter the after business hour failed login attempts.
This query filters all the failed login attempts after 18:00. I selected all the data from the log_in_attempts table. Following that, I used the Where clause to select the login_time column from the table, along with the and operator to only show the attempts that were after 18:00 and unsuccessful.
A suspicious event occurred on 2022-05-09, and to investigate the event, I had to review all the login attempts that occurred on that day and the day prior.
The screenshot below shows how I executed that task using an SQL query and filtered the login attempts on the specific dates.
To complete this task, I created a query that selected all the data from the log_in_attempts table and used the where clause to select the login_date column and requested only logins that happened on 2022-05-09 or 2022-05-08 by using the or operator. Doing so eliminated all the unnecessary dates from the returned log.
Later, it was found that there was suspicious activity with login attempts but it was determined that the activity did not originate in Mexico and more information needed to be gathered.
The screenshot below shows how I executed that task using an SQL query and filtered the login attempts that occurred everywhere except Mexico.
To complete this task, I created a query selecting all the data from the log_in_attempts table and used the where clause to select the country column and used the LIKE operator with the “MEX%” string to bring up any data that represents Mexico. The % represents any number of unspecified characters when used with LIKE.
The team was given the task of updating certain employees in the Marketing department’s computers. To complete this, we need to know which computers to update.
The screenshot below shows how I executed that task using an SQL query and filtered the employees in the Marketing department in the East building.
To complete this task, I created a query selecting all the data in the employees table. Then I used the Where clause to target the department column, pairing that with the AND operator to also target the office column as well. When targeting the office column, I used East% to be sure to select all the office numbers within the East building.
The next task is to update the computers for the employees in the Finance and Sales departments. It's going to be a different update from the previous one for the marketing department, so a new query needs to be requested to get the information on those computers.
The screenshot below shows how I executed that task using an SQL query to filter the employees in the Finance and Sales departments.
To complete this task, I created a query selecting all the data in the employees table. Then used a WHERE clause with the OR operator to filter the employees that's only in the Finance and Sales departments. The reason I used the OR operator instead of the AND is that I wanted to pull all information on employees that's in either department. The first condition “department = ‘Finance’” pulls all employees in the Finance department, and the second condition, “department = ‘Sales’”, pulls all employees in the Sales department.
My last task is to make a final update for all employees that are not in the IT (Information Technology) department. I need to get the information on all employees that are not a part of this department.
The screenshot below shows how I executed that task using an SQL query to filter the employees not in the Information Technology department.
The query shows how I selected all the employees from the employees table, then used a WHERE clause with the NOT operator to filter the employees not in the IT department.
To complete the task above, I had to use different filters on SQL queries to get the necessary information about login attempts and employee departments. I used the AND, OR, and NOT operators to filter the information from the employee and log_in_attempts tables. Then used even more specific filters, LIKE and the percentage sign to get the information needed from the tables.