a1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task A1 - Initial access - (Log analysis) Points: 10\n",
    "\n",
    "## Problem Statement\n",
    "\n",
    "> We believe that the attacker may have gained access to the victim’s network by phishing a legitimate users credentials and connecting over the company’s VPN. The FBI has obtained a copy of the company’s VPN server log for the week in which the attack took place. Do any of the user accounts show unusual behavior which might indicate their credentials have been compromised?\n",
    "> Note that all IP addresses have been anonymized.\n",
    "> Enter the username which shows signs of a possible compromise."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given VPN logs, we need to return a username who is seemingly malicious.\n",
    "First, use Python to analyze the given file `data/vpn.log`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Node</th>\n",
       "      <th>Username</th>\n",
       "      <th>Start Time</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Service</th>\n",
       "      <th>Active</th>\n",
       "      <th>Auth</th>\n",
       "      <th>Real Ip</th>\n",
       "      <th>Vpn Ip</th>\n",
       "      <th>Proto</th>\n",
       "      <th>Port</th>\n",
       "      <th>Bytes Total</th>\n",
       "      <th>Error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>openvpn-server</td>\n",
       "      <td>Doris.X</td>\n",
       "      <td>2022.06.27 07:48:08 EDT</td>\n",
       "      <td>37494.0</td>\n",
       "      <td>VPN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>172.28.168.133</td>\n",
       "      <td>10.128.20.108</td>\n",
       "      <td>UDP</td>\n",
       "      <td>1194</td>\n",
       "      <td>2.030863e+09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>openvpn-server</td>\n",
       "      <td>Kelly.G</td>\n",
       "      <td>2022.06.27 08:15:32 EDT</td>\n",
       "      <td>32314.0</td>\n",
       "      <td>VPN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>172.19.185.189</td>\n",
       "      <td>10.128.20.194</td>\n",
       "      <td>UDP</td>\n",
       "      <td>1194</td>\n",
       "      <td>2.363123e+09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>openvpn-server</td>\n",
       "      <td>Dorothy.D</td>\n",
       "      <td>2022.06.27 08:43:39 EDT</td>\n",
       "      <td>18184.0</td>\n",
       "      <td>VPN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>172.22.90.19</td>\n",
       "      <td>10.128.20.43</td>\n",
       "      <td>UDP</td>\n",
       "      <td>1194</td>\n",
       "      <td>2.009805e+09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>openvpn-server</td>\n",
       "      <td>James.V</td>\n",
       "      <td>2022.06.27 08:54:02 EDT</td>\n",
       "      <td>2266.0</td>\n",
       "      <td>VPN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>172.25.206.19</td>\n",
       "      <td>10.128.20.78</td>\n",
       "      <td>UDP</td>\n",
       "      <td>1194</td>\n",
       "      <td>1.379042e+08</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>openvpn-server</td>\n",
       "      <td>Joan.P</td>\n",
       "      <td>2022.06.27 09:28:35 EDT</td>\n",
       "      <td>20074.0</td>\n",
       "      <td>VPN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>172.23.234.95</td>\n",
       "      <td>10.128.20.106</td>\n",
       "      <td>UDP</td>\n",
       "      <td>1194</td>\n",
       "      <td>1.318902e+09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Node   Username               Start Time  Duration Service  \\\n",
       "0  openvpn-server    Doris.X  2022.06.27 07:48:08 EDT   37494.0     VPN   \n",
       "1  openvpn-server    Kelly.G  2022.06.27 08:15:32 EDT   32314.0     VPN   \n",
       "2  openvpn-server  Dorothy.D  2022.06.27 08:43:39 EDT   18184.0     VPN   \n",
       "3  openvpn-server    James.V  2022.06.27 08:54:02 EDT    2266.0     VPN   \n",
       "4  openvpn-server     Joan.P  2022.06.27 09:28:35 EDT   20074.0     VPN   \n",
       "\n",
       "   Active  Auth         Real Ip         Vpn Ip Proto  Port   Bytes Total Error  \n",
       "0       0     1  172.28.168.133  10.128.20.108   UDP  1194  2.030863e+09   NaN  \n",
       "1       0     1  172.19.185.189  10.128.20.194   UDP  1194  2.363123e+09   NaN  \n",
       "2       0     1    172.22.90.19   10.128.20.43   UDP  1194  2.009805e+09   NaN  \n",
       "3       0     1   172.25.206.19   10.128.20.78   UDP  1194  1.379042e+08   NaN  \n",
       "4       0     1   172.23.234.95  10.128.20.106   UDP  1194  1.318902e+09   NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/a1/vpn.log\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This result shows `data/vpn.log`'s columns.\n",
    "Some of the tables seem useless in terms of analysis, so let's remove them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=[\"Node\", \"Service\", \"Active\", \"Proto\", \"Port\", \"Error\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After this procedure, use `Start Time` and `Duration` to create a new column.\n",
    "This column name should be `End Time` opposition to `Start Time`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dateutil import parser\n",
    "df[[\"Duration\"]] = df[[\"Duration\"]].fillna(value=0)\n",
    "df['Start Time'] = pd.to_datetime(df['Start Time'])\n",
    "df[\"Duration\"] = df[\"Duration\"].apply(lambda x: timedelta(0, x))\n",
    "end_time = df[\"Start Time\"] + df[\"Duration\"]\n",
    "df.insert(2, \"End Time\", end_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check `Start Time` and `End Time`.\n",
    "Assume you were an attacker, what do you do to deceive data? You may want to use VPN from multiple computers. See if there is an overlap between them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Moses.K', '172.30.122.56', '172.27.26.101')]\n"
     ]
    }
   ],
   "source": [
    "df = df.sort_values(\"Username\")\n",
    "usernames = df[\"Username\"].unique()\n",
    "suspect = []\n",
    "for user in usernames:\n",
    "    user_rows = df[df[\"Username\"] == user]\n",
    "    intervals = []\n",
    "    for i, row in user_rows.iterrows():\n",
    "        start_time = row[\"Start Time\"]\n",
    "        end_time = row[\"End Time\"]\n",
    "        real_ip = row[\"Real Ip\"]\n",
    "        intervals.append((start_time, end_time, real_ip))\n",
    "    intervals.sort()\n",
    "    # see if there is an overlap in intervals\n",
    "    is_suspicious = False\n",
    "    for i in range(1, len(intervals)):\n",
    "        prev_left, prev_right, prev_ip = intervals[i - 1]\n",
    "        cur_left, cur_right, cur_ip = intervals[i]\n",
    "        if prev_left <= cur_left <= prev_right and prev_ip != cur_ip:\n",
    "            is_suspicious = True\n",
    "        if is_suspicious:\n",
    "            suspect.append((user, prev_ip, cur_ip))\n",
    "            break\n",
    "print(suspect)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes! We've found a suspicious user that has an overlap in accessing.\n",
    "He seems to have enter VPN using two different IPs at the same time.\n",
    "\n",
    "![badgea1](img/badgea1.png)"
   ]
  }
 ],
 "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.10.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "b7866fe74932d61b081c4ec2597adfb5d737ce2ec54e37047e1f636f4795859c"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}