Project description
I am a security professional at a large organization. Part of my job is to investigate security issues to help keep the system secure. I recently discovered some potential security issues that involve login attempts and employee machines.
My task is to examine the organization’s data in their employees and log_in_attempts tables. I’ll need to use SQL filters to retrieve records from different datasets and investigate the potential security issues.
Retrieve after hours failed login attempts
I recently discovered a potential security incident that occurred after business hours. To investigate this, I need to query the log_in_attempts table and review after hours login activity. I use filters in SQL to create a query that identifies all failed login attempts that occurred after 18:00. (The time of the login attempt is found in the login_time column. The success column contains a value of 0 when a login attempt failed; I can use either a value of 0 or FALSE in the query to identify failed login attempts.)
SELECT * log_in_attempts WHERE login_time > “18:00” AND success = 0 OR success = FALSE;Entering the above query in the sql console, I am looking for all files in log_in_attemps table for entries / rows that match the criteria as stated like login_time data should be greater than 18:00 and success value has to be equal to 0 or false. So I am looking for all the unsuccessful login attempts after the working hours. This is a sign of brute force attack depending on the volume of the attempts.
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. I will use filters in SQL to create a query that identifies all login attempts that occurred on 2022-05-09 or 2022-05-08. (The date of the login attempt is found in the login_date column.)
SELECT * FROM log_in_attempts WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';Retrieve login attempts outside of Mexico
There’s been suspicious activity with login attempts, but the team has determined that this activity didn’t originate in Mexico. Now, I need to investigate login attempts that occurred outside of Mexico. I will use filters in SQL to create a query that identifies all login attempts that occurred outside of Mexico. (When referring to Mexico, the country column contains values of both MEX and MEXICO, and I need to use the LIKE keyword with % to make sure my query reflects this. It will look for entries as MEX and any other words starting with MEX.)
SELECT * FROM log_in_attempts WHERE NOT country LIKE 'MEX%';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 these employee machines and will need to query the employees table. I will use filters in SQL to create a query that identifies all employees in the Marketing department for all offices in the East building.
(The department of the employee is found in the department column, which contains values that include Marketing. The office is found in the office column. Some examples of values in this column are East-170, East-320, and North-434. I will need to use the LIKE keyword with % again to filter for the East building.)
SELECT * FROM employees WHERE department = 'Marketing' AND office LIKE 'East%';Retrieve employees in Finance or Sales
Our team now needs to perform a different security update on machines for employees in the Sales and Finance departments. I will use filters in SQL to create a query that identifies all employees in the Sales or Finance departments. (The department of the employee is found in the department column, which contains values that include Sales and Finance.)
SELECT * FROM employees WHERE department = 'Finance' OR department = 'Sales';Retrieve all employees not in IT
Our team needs to make one more update to employee machines. The employees who are in the Information Technology department already had this update, but employees in all other departments need it. I will use filters in SQL to create a query which identifies all employees not in the IT department. (The department of the employee is found in the department column, which contains values that include Information Technology.)
SELECT * FROM employees WHERE NOT department = 'Information Technology';Summary
Using SQL is fast, efficient and sharp. Data stored in SQL tables can easily be searched by logical operators. To find the cause of the problem and analyze the unusual activity in the network, we have found that the login attempts are failing and increasing especially after office hours and it is localized on non IT departments and sources outside of Mexico as a country. I have pinned an eye on finance, marketing and sales departments. Probably one of the computers in these departments is compromised and has to be taken in custody for further investigation.
