Amadi Chinedu Joseph

Apply filters to SQL queries Project description My Organisation requested that we run a security test to investigate the issues that involve login attempts and employee machines, so we used SQL filters to retrieve records from different data sets and we instigated the potential security issues. I used SQL to filter and correct the issues regarding the employee machines and the failed login attempts. Retrieve after-hours failed login attempts We had a security incident that occurred minutes or hours after business time which is ‘18:00’ and all after-business hour login attempts that failed need to be investigated thoroughly. The diagram below shows how i created the SQL query to filter for failed login attempts that occurred after ‘18:00’. The first line of the screenshot shows my query. The following screenshot is some part of the output. This query filters for all failed login attempts that occurred after working hours. I selected all data from the login attempts table, then i used the WHERE clause, AND operator. With those, i was able to filter the output that shows the result of all failed login attempts. Retrieve login attempts on specific dates A security threat event occurred on ‘-’. So we have to investigate all the login attempts that occurred on or before ‘-’ needs to be looked into. The diagram below shows the query i created to filter all the login attempts that occurred ‘-’ or ‘-’. The first line in the screenshot is my query and the second is the output. I collected all the data from the login attempts and filtered all the login attempts that occurred on ‘-’ and ‘-’ i got this output that contain all the login attempts. I used the condition log_in_date = ‘-’ to filter for login attempts that happened on that date and used the command log_in_date = ‘-’ to filter for login that happened on that date. Retrieve login attempts outside of Mexico We had login attempts that occurred outside of Mexico, that need to be investigated. I was given the task to investigate the incident. The screenshot diagram below shows my SQL query and the output that contains the data of the login attempts that happened outside of Mexico. The first group on top in the screenshot is my SQL query. The second group is the output, it contain the login attempts that occurred outside of Mexico. I selected the data from the log attempts table and i used WHERE, NOT, LIKE, and ‘MEX%’ to be the matching pattern representing Mexico and MEX. The sign (%) represents some number of unspecified characters if used with LIKE. Retrieve employees in Marketing We the security team want to run an update on machines for some of the employees in the marketing department, so i have to get the specific employees i need to update their machines. Below is the screenshot that contains the SQL query i used to filter for employees in the East building. In the screenshot above is my SQL query, and the output. I selected the employee's data from the table. The query returned the information of the employees in the East building that their machines need patching or updating. And used the WHERE with AND clause to filter for employees that work in the Marketing department and where their office is located which is the East building. WHERE department = ‘Marketing’ filter for employees working under the marketing department, AND office LIKE ‘East%’ filters for the employee's office located in the East building. Retrieve employees in Finance or Sales My team needs to perform a different update to the computers of all the employees in the finance or the sales department and i need to locate information of these employees. Below is my SQL query to filter for the employee's information and the output. Above is the screenshot of the SQL query to retrieve the information. And the output of the query that contains the information of the employees. I used SELECT to collect data FROM the employee's table. Used WHERE department = ‘finance’ OR department = ‘sales’ to filter for employees in both departments. Retrieve all employees not in IT My team needs to perform one more update, this update has already been made to employees' computers in the IT departments, I should retrieve information of the employee's that are not in the IT department. Below is my SQL query to retrieve the information of the employees. Above is the screenshot of the SQL query in the first line, in the second line is the output that contains the information of the employees for the update. I used WHERE NOT department = ‘Information Technology’ to filter for the employees that are not in the IT department, The NOT negates. Summary I used SQL queries and filters to retrieve specific information on login attempts and some employee computers, using different tables, employee, and log_in_attempts. I applied NOT, AND, and NOT operators in filtering for particular information needed to perform tasks. Also Included LIKE and (%).