{ "cells": [ { "cell_type": "code", "execution_count": 3, "id": "84b491a8-4ce7-44f3-aed9-feba2ddd1b3b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "HR Records: 5\n", "App Records: 6\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Employee_IDNameTerm_DateDepartment
0E001Alice Smith2023-11-15Sales
1E005Bob Johnson2023-12-01IT
2E010Charlie Brown2024-01-10Finance
3E012David Miller2023-10-20Marketing
4E015Eve Wilson2023-12-25Engineering
\n", "
" ], "text/plain": [ " Employee_ID Name Term_Date Department\n", "0 E001 Alice Smith 2023-11-15 Sales\n", "1 E005 Bob Johnson 2023-12-01 IT\n", "2 E010 Charlie Brown 2024-01-10 Finance\n", "3 E012 David Miller 2023-10-20 Marketing\n", "4 E015 Eve Wilson 2023-12-25 Engineering" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "\n", "# Load the datasets\n", "df_hr = pd.read_csv('hr_terminations.csv')\n", "df_app = pd.read_csv('app_users.csv')\n", "\n", "# Convert date columns to actual datetime objects immediately\n", "df_hr['Term_Date'] = pd.to_datetime(df_hr['Term_Date'])\n", "df_app['Last_Login'] = pd.to_datetime(df_app['Last_Login'])\n", "\n", "print(f\"HR Records: {len(df_hr)}\")\n", "print(f\"App Records: {len(df_app)}\")\n", "df_hr.head()" ] }, { "cell_type": "code", "execution_count": 4, "id": "1ebfee0c-aece-471a-8d21-49232d672cce", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Data cleaning complete.\n" ] } ], "source": [ "# Strip whitespace from IDs and Names to prevent 'false negatives'\n", "df_hr['Employee_ID'] = df_hr['Employee_ID'].str.strip()\n", "df_app['User_ID'] = df_app['User_ID'].str.strip()\n", "\n", "# Standardizing names for easier visual review later\n", "df_hr['Name'] = df_hr['Name'].str.strip().str.title()\n", "df_app['Full_Name'] = df_app['Full_Name'].str.strip().str.title()\n", "\n", "print(\"Data cleaning complete.\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "d5a547ec-e787-4b1e-ae77-07ab5b77b980", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Employee_IDNameTerm_DateDepartmentUser_IDFull_NameAccount_StatusLast_Login
0E001Alice Smith2023-11-15SalesE001Alice SmithActive2024-01-05
1E005Bob Johnson2023-12-01ITE005Bob JohnsonActive2023-11-28
2E010Charlie Brown2024-01-10FinanceE010Charlie BrownDisabled2024-01-08
3E012David Miller2023-10-20MarketingNaNNaNNaNNaT
4E015Eve Wilson2023-12-25EngineeringE015Eve WilsonActive2024-02-01
\n", "
" ], "text/plain": [ " Employee_ID Name Term_Date Department User_ID Full_Name \\\n", "0 E001 Alice Smith 2023-11-15 Sales E001 Alice Smith \n", "1 E005 Bob Johnson 2023-12-01 IT E005 Bob Johnson \n", "2 E010 Charlie Brown 2024-01-10 Finance E010 Charlie Brown \n", "3 E012 David Miller 2023-10-20 Marketing NaN NaN \n", "4 E015 Eve Wilson 2023-12-25 Engineering E015 Eve Wilson \n", "\n", " Account_Status Last_Login \n", "0 Active 2024-01-05 \n", "1 Active 2023-11-28 \n", "2 Disabled 2024-01-08 \n", "3 NaN NaT \n", "4 Active 2024-02-01 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# We join on the ID. \n", "# We use 'left' because we only care about people on the termination list.\n", "audit_merge = pd.merge(\n", " df_hr, \n", " df_app, \n", " left_on='Employee_ID', \n", " right_on='User_ID', \n", " how='left'\n", ")\n", "\n", "# Display the merged table\n", "audit_merge" ] }, { "cell_type": "code", "execution_count": 6, "id": "5db62c12-6f2d-48cc-b008-39e135348f00", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Finding 1: 3 users still marked as 'Active'\n", "Finding 2: 2 users logged in after termination\n" ] } ], "source": [ "# 1. Identify Terminated but still 'Active' in Application\n", "active_leavers = audit_merge[audit_merge['Account_Status'] == 'Active'].copy()\n", "\n", "# 2. Identify Logins occurring AFTER termination date\n", "# This is a critical security finding indicating potential account misuse\n", "post_term_logins = audit_merge[audit_merge['Last_Login'] > audit_merge['Term_Date']].copy()\n", "\n", "print(f\"Finding 1: {len(active_leavers)} users still marked as 'Active'\")\n", "print(f\"Finding 2: {len(post_term_logins)} users logged in after termination\")" ] }, { "cell_type": "code", "execution_count": 8, "id": "67c23470-4c0d-422d-bd1a-b0ad19487aca", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Collecting openpyxl\n", " Downloading openpyxl-3.1.5-py2.py3-none-any.whl.metadata (2.5 kB)\n", "Collecting et-xmlfile (from openpyxl)\n", " Downloading et_xmlfile-2.0.0-py3-none-any.whl.metadata (2.7 kB)\n", "Downloading openpyxl-3.1.5-py2.py3-none-any.whl (250 kB)\n", "Downloading et_xmlfile-2.0.0-py3-none-any.whl (18 kB)\n", "Installing collected packages: et-xmlfile, openpyxl\n", "\u001b[2K \u001b[38;2;114;156;31m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2/2\u001b[0m [openpyxl]━━\u001b[0m \u001b[32m1/2\u001b[0m [openpyxl]\n", "\u001b[1A\u001b[2KSuccessfully installed et-xmlfile-2.0.0 openpyxl-3.1.5\n", "Note: you may need to restart the kernel to use updated packages.\n" ] } ], "source": [ "%pip install openpyxl" ] }, { "cell_type": "code", "execution_count": 9, "id": "e2fd2073-04e0-41f9-b76f-1677967e96b9", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Audit Report Exported: Termination_Audit_Report.xlsx\n" ] } ], "source": [ "# Create a summary report\n", "with pd.ExcelWriter('Termination_Audit_Report.xlsx') as writer:\n", " active_leavers.to_excel(writer, sheet_name='Active_Leavers', index=False)\n", " post_term_logins.to_excel(writer, sheet_name='Post_Term_Logins', index=False)\n", " audit_merge.to_excel(writer, sheet_name='Full_Traceability_Matrix', index=False)\n", "\n", "print(\"Audit Report Exported: Termination_Audit_Report.xlsx\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.2" } }, "nbformat": 4, "nbformat_minor": 5 }