Forti_VPN_Logs_Analysis-Public_v0.2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1c7e0d59-9867-4430-b9c9-fe6fbc7c9914",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Fortinet VPN Logs Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db70d781-5f75-4278-b96e-c0e2d92236d0",
   "metadata": {},
   "source": [
    "This notebook helps to deal with VPN logs from Fortinet firewalls.\n",
    "Although data conversion can be used for all types of \"key-value\" logs, data processing will not work with another type of log due to the column names."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47e901cf-4e5f-48ee-b04c-69dd9b1843e1",
   "metadata": {},
   "source": [
    "## Logs conversion to csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ca4cead-3315-4266-948b-7925c915cc9a",
   "metadata": {},
   "source": [
    "Use of multiprocessing to convert Fortinet firewall log files with non csv format to csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "77684a1a-a41a-466d-be4a-474f273f2c9f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def read_files_folder(path):\n",
    "    from os import walk\n",
    "    files = []\n",
    "    for (dirpath, dirnames, filenames) in walk(path):\n",
    "        for filename in filenames:\n",
    "            if filename.endswith(\"log\"):\n",
    "                file_path = dirpath + \"\\\\\" + filename\n",
    "                files.append(file_path)\n",
    "    return files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d226d377-277d-4a4b-b04e-c9268c5bcbed",
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_file_functions(output_path=r\"C:\\Logs_VPN_Events\\logs_csv\"):\n",
    "    f = open(r\"process_file.py\", \"w+\")\n",
    "\n",
    "    file_content = f\"\"\"def load_log(log_string_list):\n",
    "    import re\n",
    "    import pandas as pd\n",
    "    pattern = re.compile('(\\w+)=(?:\"([^\"]*)\"|(\\S*))')\n",
    "\n",
    "    lines_s = pd.Series(log_string_list)\n",
    "    data = lines_s.str.extractall(pattern)\n",
    "    data.index = data.index.get_level_values(0)\n",
    "    data[1] = data[1].fillna(data[2])\n",
    "    return data.pivot(index=None, columns=0, values=1)\n",
    "    \n",
    "    \n",
    "def process_file(file):\n",
    "    import pandas as pd\n",
    "    from os import path\n",
    "\n",
    "    output_folder = r'{output_path}'\n",
    "    first = True\n",
    "    with open(file, \"r\") as f:\n",
    "        if first:\n",
    "            log_df = load_log(f.readlines())\n",
    "            first = False\n",
    "        else:\n",
    "            t_df = load_log(f.readlines())\n",
    "            log_df = log_df.append(t_df)\n",
    "            del t_df\n",
    "    file_name = path.basename(file)\n",
    "    file_path = output_folder + '\\\\\\\\' + file_name + '.csv'\n",
    "    log_df.to_csv(file_path)\n",
    "\n",
    "    del log_df\"\"\"\n",
    "\n",
    "    f.write(file_content)\n",
    "    f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9965e99a-d9dc-4b46-8aad-a994224ffbf5",
   "metadata": {},
   "outputs": [],
   "source": [
    "write_file_functions()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c1af5ad8-240a-4659-81e0-31f9158948bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting process_file.py\n"
     ]
    }
   ],
   "source": [
    "%%writefile process_file.py\n",
    "\n",
    "def load_log(log_string_list):\n",
    "    import re\n",
    "    import pandas as pd\n",
    "    pattern = re.compile('(\\w+)=(?:\"([^\"]*)\"|(\\S*))')\n",
    "    \n",
    "    lines_s = pd.Series(log_string_list)\n",
    "    data = lines_s.str.extractall(pattern)\n",
    "    data.index = data.index.get_level_values(0)\n",
    "    data[1] = data[1].fillna(data[2])\n",
    "    return data.pivot(index=None, columns=0, values=1)\n",
    "\n",
    "def process_file(file):\n",
    "    import pandas as pd\n",
    "    from os import path\n",
    "    \n",
    "    output_folder = r\"C:\\Logs_VPN_Events\\logs_csv\"\n",
    "    first = True\n",
    "    with open(file, \"r\") as f:\n",
    "        if first:\n",
    "            log_df = load_log(f.readlines())\n",
    "            first = False\n",
    "        else:\n",
    "            t_df = load_log(f.readlines())\n",
    "            log_df = log_df.append(t_df)\n",
    "            del t_df\n",
    "    file_name = path.basename(file)\n",
    "    file_path = output_folder + \"\\\\\" + file_name + \".csv\"\n",
    "    log_df.to_csv(file_path)\n",
    "    \n",
    "    del log_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "aad15add-0cc8-4405-95b1-6d3c79a7c78d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import process_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cb52ddf6-6371-4893-856a-33624ed594e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 35min 10s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "from multiprocessing import Pool\n",
    "import process_file\n",
    "\n",
    "if __name__ ==  '__main__': \n",
    "    num_processors = 12\n",
    "    p=Pool(processes = num_processors)\n",
    "    logs_folder = r\"C:\\Logs_VPN_Events\"\n",
    "\n",
    "    files = read_files_folder(logs_folder)\n",
    "\n",
    "    p.map(process_file.process_file, files)\n",
    "    \n",
    "    p.close()\n",
    "    p.join()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0568f767-0483-428d-bcb8-250be50421a9",
   "metadata": {},
   "source": [
    "## Dealing with different columns in files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "21def674-cb7f-4c56-93fc-7f7945a6b04f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn import preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5973cde0-496a-4ac1-9662-3654ab006971",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n",
      "all equal False\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "import d6tstack.combine_csv\n",
    "\n",
    "cfg_fnames = list(glob.glob(r\"C:\\logs_csv\\*.csv\"))\n",
    "c = d6tstack.combine_csv.CombinerCSV(cfg_fnames)\n",
    "\n",
    "# check columns\n",
    "print('all equal',c.is_all_equal())\n",
    "print('')\n",
    "d = c.is_column_present()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a81f2ec-ed51-45b1-ae52-85db51241582",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "fnames = d6tstack.combine_csv.CombinerCSV(cfg_fnames).to_csv_align(output_dir=r'C:\\logs_csv\\fixed')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5f8361f-2290-4c2d-bbb0-8a5ecea376e5",
   "metadata": {},
   "source": [
    "## Reading log files with Dask from disk "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a5535eb0-472f-4d67-9c68-949bbc9c3287",
   "metadata": {},
   "outputs": [],
   "source": [
    "import dask.dataframe as dd\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5cc4cc1c-f39f-4cb9-b48e-016185bbbfba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 64.1 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_dask = dd.read_csv(r'C:\\logs_vpn_out_fix\\*.csv',dtype={'error_num': 'object',\n",
    "        'group': 'object','locport': 'object','peer_notif': 'object','reason': 'object','remport': 'object',\n",
    "        'seq': 'object','spi': 'object','tunnelip': 'object','tunneltype': 'object','user': 'object',\n",
    "        'desc': 'object','espauth': 'object','esptransform': 'object','in_spi': 'object','out_spi': 'object',\n",
    "        'rcvdbyte': 'float','stage': 'object','tunnelid': 'object','Unnamed: 0': 'object','assignip': 'object',\n",
    "        'dst_host': 'object','duration': 'float','eventtime': 'object','logid': 'object','nextstat': 'object',\n",
    "        'phase2_name': 'object','sentbyte': 'float','exch': 'object','version': 'object'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd0b0abb-ba0a-43ef-bb8c-2bee0f896852",
   "metadata": {},
   "source": [
    "## Researching"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a18d6788-73fa-452a-bdf9-6b37fa2535e7",
   "metadata": {},
   "source": [
    "# Useful functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "84d973e1-9554-4efb-8480-df665ab78f6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# MaxMind ASN\n",
    "import geoip2.database\n",
    "def get_asn(ip):\n",
    "    try:\n",
    "        x = reader.asn(ip)\n",
    "    except:\n",
    "        return pd.np.nan\n",
    "    try:\n",
    "        return x.autonomous_system_organization if x else pd.np.nan\n",
    "    except KeyError:\n",
    "        return pd.np.nan\n",
    "\n",
    "# MaxMind Country\n",
    "def get_country(ip):\n",
    "    try:\n",
    "        x = reader.country(ip)\n",
    "    except:\n",
    "        return pd.np.nan\n",
    "    try:\n",
    "        return x.country.name if x else pd.np.nan\n",
    "    except KeyError:\n",
    "        return pd.np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3862ce7c-4c30-4dc2-ad09-3b021ab854f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tor Nodes List\n",
    "import requests\n",
    "nodes = requests.request(method='GET', url=\"https://www.dan.me.uk/torlist/\") #30 minutes limit\n",
    "nodes_list = nodes.text.split(\"\\n\")\n",
    "\n",
    "def is_tor_node(ip):\n",
    "    if ip in nodes_list:\n",
    "        return True\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ddb51522-3f00-4310-a8eb-5f4c947b4bf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def abuseipdb_req(ip):\n",
    "    import json\n",
    "    try:\n",
    "        key = <api-key>\n",
    "        url = 'https://api.abuseipdb.com/api/v2/check'\n",
    "        querystring = {\n",
    "            'ipAddress': ip,\n",
    "            'maxAgeInDays': '90'\n",
    "        }\n",
    "        headers = {\n",
    "            'Accept': 'application/json',\n",
    "            'Key': key\n",
    "        }\n",
    "        response = requests.request(method='GET', url=url, headers=headers, params=querystring)\n",
    "        decodedResponse = json.loads(response.text)\n",
    "    except e:\n",
    "        print(e)\n",
    "        return pd.np.nan\n",
    "    try:\n",
    "        return decodedResponse[\"data\"][\"abuseConfidenceScore\"] if decodedResponse else pd.np.nan\n",
    "    except KeyError:\n",
    "        return pd.np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0db7da91-73ad-4812-a06f-d2cd8b3a6301",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Convert bytes to human readable\n",
    "def sizeof_fmt(num, suffix='B'):\n",
    "    for unit in ['','Ki','Mi','Gi','Ti','Pi','Ei','Zi']:\n",
    "        if abs(num) < 1024.0:\n",
    "            return \"%3.1f%s%s\" % (num, unit, suffix)\n",
    "        num /= 1024.0\n",
    "    return \"%.1f%s%s\" % (num, 'Yi', suffix)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c91eaaf9-c543-43be-ae0c-2cbc18ec8892",
   "metadata": {},
   "source": [
    "## Searching for information exfiltration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fea83457-d953-4358-ad39-7da60ffa6602",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_exf = df_dask[[\"remip\", \"tunnelid\", \"sentbyte\"]].groupby([\"remip\",\"tunnelid\"]).max().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "eacca78a-2814-4f0c-bc8e-5223280df4fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_exf.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "be8450c0-c1f1-44c7-ba54-ca9f4979e37d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_exf[\"h_sentbyte\"] = df_exf.sentbyte.apply(sizeof_fmt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "2bacdcb4-ef22-4eec-ba3a-0c184397af43",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ext_10 = df_exf.nlargest(10, \"sentbyte\").sort_values(by=\"sentbyte\", ascending=False).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "4e91404d-c660-41f8-a59e-394580ba36f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "import plotly.io as pio\n",
    "pio.renderers.default = 'iframe'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "ef9dbabc-fc8a-415c-bfeb-a773956d4760",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"100%\"\n",
       "    height=\"545px\"\n",
       "    src=\"iframe_figures/figure_13.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = px.bar(df_ext_10.iloc[2:8], y=\"remip\", x=\"sentbyte\", title='Destiantion IPs by data sent', orientation='h')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f86cb2f-a2b4-417c-b66c-e1829b04cb41",
   "metadata": {},
   "source": [
    "### Data mining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "edf3bb5b-525b-4ff1-b4b8-0c4f71a07baa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 34.1 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "## Add ASN\n",
    "with geoip2.database.Reader(r'C:\\maxmind\\GeoLite2-ASN.mmdb') as reader:\n",
    "    unique_ips = df_exf['remip'].unique()\n",
    "    unique_ips = pd.Series(unique_ips, index = unique_ips)\n",
    "    df_exf['ASN'] = df_exf['remip'].map(unique_ips.apply(get_asn))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "4cf79138-85e1-4036-8216-d5a5da225950",
   "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>index</th>\n",
       "      <th>remip</th>\n",
       "      <th>tunnelid</th>\n",
       "      <th>sentbyte</th>\n",
       "      <th>h_sentbyte</th>\n",
       "      <th>ASN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>495</td>\n",
       "      <td>103.6.139.246</td>\n",
       "      <td>2973221928.0</td>\n",
       "      <td>3.470560e+12</td>\n",
       "      <td>3.2TiB</td>\n",
       "      <td>HKBN Enterprise Solutions HK Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>103.6.139.246</td>\n",
       "      <td>3054928288.0</td>\n",
       "      <td>1.646262e+12</td>\n",
       "      <td>1.5TiB</td>\n",
       "      <td>HKBN Enterprise Solutions HK Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>164</td>\n",
       "      <td>191.101.209.126</td>\n",
       "      <td>1826793719.0</td>\n",
       "      <td>3.506824e+10</td>\n",
       "      <td>32.7GiB</td>\n",
       "      <td>GTT Communications Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>214</td>\n",
       "      <td>191.101.209.72</td>\n",
       "      <td>1826793700.0</td>\n",
       "      <td>2.084387e+10</td>\n",
       "      <td>19.4GiB</td>\n",
       "      <td>GTT Communications Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>291</td>\n",
       "      <td>191.101.209.72</td>\n",
       "      <td>1826793661.0</td>\n",
       "      <td>1.833587e+10</td>\n",
       "      <td>17.1GiB</td>\n",
       "      <td>GTT Communications Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>361</td>\n",
       "      <td>115.134.131.154</td>\n",
       "      <td>3068782335.0</td>\n",
       "      <td>1.560168e+10</td>\n",
       "      <td>14.5GiB</td>\n",
       "      <td>TM Net, Internet Service Provider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>293</td>\n",
       "      <td>171.99.130.158</td>\n",
       "      <td>3069050613.0</td>\n",
       "      <td>8.586856e+09</td>\n",
       "      <td>8.0GiB</td>\n",
       "      <td>TRUE INTERNET Co.,Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>37</td>\n",
       "      <td>210.61.150.144</td>\n",
       "      <td>3067193495.0</td>\n",
       "      <td>7.414605e+09</td>\n",
       "      <td>6.9GiB</td>\n",
       "      <td>Data Communication Business Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>202.188.229.70</td>\n",
       "      <td>3027033592.0</td>\n",
       "      <td>5.857543e+09</td>\n",
       "      <td>5.5GiB</td>\n",
       "      <td>TM Net, Internet Service Provider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>122.103.241.185</td>\n",
       "      <td>3069552181.0</td>\n",
       "      <td>4.883923e+09</td>\n",
       "      <td>4.5GiB</td>\n",
       "      <td>ARTERIA Networks Corporation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index            remip      tunnelid      sentbyte h_sentbyte                                   ASN\n",
       "495    495    103.6.139.246  2973221928.0  3.470560e+12     3.2TiB  HKBN Enterprise Solutions HK Limited\n",
       "1        1    103.6.139.246  3054928288.0  1.646262e+12     1.5TiB  HKBN Enterprise Solutions HK Limited\n",
       "164    164  191.101.209.126  1826793719.0  3.506824e+10    32.7GiB               GTT Communications Inc.\n",
       "214    214   191.101.209.72  1826793700.0  2.084387e+10    19.4GiB               GTT Communications Inc.\n",
       "291    291   191.101.209.72  1826793661.0  1.833587e+10    17.1GiB               GTT Communications Inc.\n",
       "361    361  115.134.131.154  3068782335.0  1.560168e+10    14.5GiB     TM Net, Internet Service Provider\n",
       "293    293   171.99.130.158  3069050613.0  8.586856e+09     8.0GiB                TRUE INTERNET Co.,Ltd.\n",
       "37      37   210.61.150.144  3067193495.0  7.414605e+09     6.9GiB     Data Communication Business Group\n",
       "32      32   202.188.229.70  3027033592.0  5.857543e+09     5.5GiB     TM Net, Internet Service Provider\n",
       "13      13  122.103.241.185  3069552181.0  4.883923e+09     4.5GiB          ARTERIA Networks Corporation"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_exf.nlargest(10, \"sentbyte\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "c36e09e6-de63-4f83-bcb0-59336bf92835",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 44.9 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "## Add country\n",
    "with geoip2.database.Reader(r'C:\\maxmind\\GeoLite2-Country.mmdb') as reader:\n",
    "    unique_ips = df_exf['remip'].unique()\n",
    "    unique_ips = pd.Series(unique_ips, index = unique_ips)\n",
    "    df_exf['remip_country'] = df_exf['remip'].map(unique_ips.apply(get_country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bb98067-21a6-44dd-82ec-d024be327658",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_exf.nlargest(10, \"sentbyte\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "66ecd562-1a1e-448d-9730-c0d8bfff53fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ext_map = df_exf.sort_values(by=\"sentbyte\", ascending=False).iloc[2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "bfbab99c-6440-4f3d-af29-0c4932d83636",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ext_map = df_ext_map[[\"sentbyte\", \"remip_country\"]].groupby(\"remip_country\").sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "ae14ab0c-a810-4696-98c6-f8639d1f9036",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'temp-plot.html'"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import plotly as py\n",
    "import plotly.graph_objs as go\n",
    "import pandas as pd\n",
    "\n",
    "data = dict (\n",
    "    type = 'choropleth',\n",
    "    locations = df_ext_map['remip_country'],\n",
    "    locationmode='country names',\n",
    "    colorscale = 'blugrn',\n",
    "    z=df_ext_map['sentbyte'])\n",
    "\n",
    "map = go.Figure(data=[data])\n",
    "py.offline.plot(map)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6e9bb56-d9b7-47a6-b6d2-1f68a2daa48b",
   "metadata": {},
   "source": [
    "### Search for Tor nodes communications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "108e47b6-2301-4c25-88d6-5ddeff562a0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 18 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "unique_remip = df_exf[\"remip\"].unique()\n",
    "unique_remip = pd.Series(unique_remip, index = unique_remip)\n",
    "df_exf['is_tor_node'] = df_exf['remip'].map(unique_remip.apply(is_tor_node))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "5acd1b28-604f-4535-b1a9-c417bd6e5399",
   "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>index</th>\n",
       "      <th>remip</th>\n",
       "      <th>tunnelid</th>\n",
       "      <th>sentbyte</th>\n",
       "      <th>h_sentbyte</th>\n",
       "      <th>ASN</th>\n",
       "      <th>remip_country</th>\n",
       "      <th>is_tor_node</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>189</td>\n",
       "      <td>185.220.100.247</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>F3 Netze e.V.</td>\n",
       "      <td>Germany</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>196</td>\n",
       "      <td>45.129.56.200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>31173 Services AB</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>210</td>\n",
       "      <td>185.220.100.244</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>F3 Netze e.V.</td>\n",
       "      <td>Germany</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>211</td>\n",
       "      <td>185.220.101.129</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>Cia Triad Security LLC</td>\n",
       "      <td>Germany</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index            remip tunnelid  sentbyte h_sentbyte                     ASN remip_country  is_tor_node\n",
       "189    189  185.220.100.247      0.0       NaN     nanYiB           F3 Netze e.V.       Germany         True\n",
       "196    196    45.129.56.200      0.0       NaN     nanYiB       31173 Services AB       Denmark         True\n",
       "210    210  185.220.100.244      0.0       NaN     nanYiB           F3 Netze e.V.       Germany         True\n",
       "211    211  185.220.101.129      0.0       NaN     nanYiB  Cia Triad Security LLC       Germany         True"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_exf[df_exf[\"is_tor_node\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b3083b9-71b6-4752-8e16-bedbc9240e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_exf.nlargest(10, \"sentbyte\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7aa8f780-1b18-44fe-97b1-fe1676d1a3a4",
   "metadata": {},
   "source": [
    "### AbuseIPDB score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "840ad7b3-717d-41a3-a344-f9b49b63f7f6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 2min 22s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "unique_ips = df_exf[\"remip\"].unique()\n",
    "unique_ips = pd.Series(unique_ips, index = top10)\n",
    "df_exf['remip_abuse'] = df_exf['remip'].map(unique_ips.apply(abuseipdb_req))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "665fcefd-7d29-459c-ab76-4b2f17c567a8",
   "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>index</th>\n",
       "      <th>remip</th>\n",
       "      <th>tunnelid</th>\n",
       "      <th>sentbyte</th>\n",
       "      <th>h_sentbyte</th>\n",
       "      <th>ASN</th>\n",
       "      <th>remip_country</th>\n",
       "      <th>is_tor_node</th>\n",
       "      <th>remip_abuse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>162.142.125.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>CENSYS-ARIN-01</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>192.241.210.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>DIGITALOCEAN-ASN</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>49</td>\n",
       "      <td>167.248.133.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>CENSYS-ARIN-03</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>57</td>\n",
       "      <td>111.7.96.166</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>Guangdong Mobile Communication Co.Ltd.</td>\n",
       "      <td>China</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>62</td>\n",
       "      <td>184.105.139.69</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>HURRICANE</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>64</td>\n",
       "      <td>65.49.20.69</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>HURRICANE</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>95</td>\n",
       "      <td>74.120.14.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>CENSYS-ARIN-02</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>114</td>\n",
       "      <td>74.120.14.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>CENSYS-ARIN-02</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>138</td>\n",
       "      <td>162.142.125.128</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>CENSYS-ARIN-01</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>142</td>\n",
       "      <td>184.105.247.195</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nanYiB</td>\n",
       "      <td>HURRICANE</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index            remip tunnelid  sentbyte h_sentbyte                                     ASN  remip_country  is_tor_node  remip_abuse\n",
       "16      16   162.142.125.39      0.0       NaN     nanYiB                          CENSYS-ARIN-01  United States        False          100\n",
       "31      31   192.241.210.30      0.0       NaN     nanYiB                        DIGITALOCEAN-ASN  United States        False          100\n",
       "49      49   167.248.133.53      0.0       NaN     nanYiB                          CENSYS-ARIN-03  United States        False          100\n",
       "57      57     111.7.96.166      0.0       NaN     nanYiB  Guangdong Mobile Communication Co.Ltd.          China        False          100\n",
       "62      62   184.105.139.69      0.0       NaN     nanYiB                               HURRICANE  United States        False          100\n",
       "64      64      65.49.20.69      0.0       NaN     nanYiB                               HURRICANE  United States        False          100\n",
       "95      95     74.120.14.40      0.0       NaN     nanYiB                          CENSYS-ARIN-02  United States        False          100\n",
       "114    114     74.120.14.39      0.0       NaN     nanYiB                          CENSYS-ARIN-02  United States        False          100\n",
       "138    138  162.142.125.128      0.0       NaN     nanYiB                          CENSYS-ARIN-01  United States        False          100\n",
       "142    142  184.105.247.195      0.0       NaN     nanYiB                               HURRICANE  United States        False          100"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_exf.nlargest(10, \"remip_abuse\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9664a8b8-61bc-4811-b24d-4f0464740930",
   "metadata": {},
   "source": [
    "## Tor Nodes communications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "10e518bf-fa59-4d7f-b42f-fd8ad8b9abab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 24.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_tor = df[[\"srcip\",\"dstip\"]].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "52a14367-e73c-4820-aa09-33650110ddb3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 14.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "unique_ips_src = df_tor['srcip'].unique()\n",
    "unique_ips_dst = df_tor['dstip'].unique()\n",
    "\n",
    "unique_ips_src = pd.Series(unique_ips_src, index = unique_ips_src)\n",
    "unique_ips_dst = pd.Series(unique_ips_dst, index = unique_ips_dst)\n",
    "\n",
    "df_tor['is_tor_node_src'] = df_tor['srcip'].map(unique_ips_src.apply(is_tor_node))\n",
    "df_tor['is_tor_node_dst'] = df_tor['dstip'].map(unique_ips_dst.apply(is_tor_node))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "03c68a9a-6adc-475b-be78-7a4cfc46d6a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51.15.47.139      157\n",
       "46.105.55.153       8\n",
       "83.135.89.183       6\n",
       "82.65.125.114       6\n",
       "185.21.216.195      6\n",
       "188.126.94.37       3\n",
       "188.40.142.18       1\n",
       "Name: dstip, dtype: int64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tor[df_tor[\"is_tor_node_dst\"]][\"dstip\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "5b761106-795c-4129-8d70-8c3890735c9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45.129.56.200      141\n",
       "185.220.100.240     90\n",
       "171.25.193.78       85\n",
       "185.220.100.252     70\n",
       "185.220.100.243     55\n",
       "185.220.101.144     40\n",
       "185.220.101.6       33\n",
       "205.185.116.3       26\n",
       "142.44.246.156      22\n",
       "185.220.100.250     20\n",
       "84.209.139.0        19\n",
       "162.247.74.204      19\n",
       "199.195.251.84      16\n",
       "37.187.196.70       12\n",
       "185.220.100.246     11\n",
       "163.172.213.212     11\n",
       "185.220.102.251     11\n",
       "185.220.100.242     11\n",
       "198.144.120.177     10\n",
       "107.189.10.42       10\n",
       "209.141.55.26       10\n",
       "77.247.181.163      10\n",
       "185.220.101.7       10\n",
       "185.220.101.136      9\n",
       "199.195.250.77       9\n",
       "185.83.214.69        9\n",
       "51.210.80.127        9\n",
       "185.220.102.4        8\n",
       "176.10.104.240       8\n",
       "5.2.69.50            8\n",
       "185.220.101.142      8\n",
       "185.220.100.255      8\n",
       "185.220.100.247      8\n",
       "18.27.197.252        8\n",
       "89.163.154.91        7\n",
       "89.163.249.244       4\n",
       "185.220.101.146      3\n",
       "64.113.32.29         2\n",
       "185.220.101.137      1\n",
       "185.220.101.1        1\n",
       "Name: srcip, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tor[df_tor[\"is_tor_node_src\"]][\"srcip\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37da4e53-f502-475c-a24c-38a6f4ecdc4d",
   "metadata": {},
   "source": [
    "### Data mining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "023d6b4f-13d3-498d-ad96-366c1c4c4014",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1min 20s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = df_dask[~df_dask[\"tunnelid\"].isnull()][[\"remip\", \"duration\", \"tunnelid\",\"sentbyte\"]].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5032555c-6dc3-4906-a806-71fd5be9a03b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_work = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dd4fb1c8-6ea1-4237-aad5-40a05605f1d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "266790f2-da17-4dd0-829a-924768e06a49",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_work.remip.unique()\n",
    "df_final = pd.Series(df_final).to_frame().rename(columns={0:\"remip\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2594d935-93ed-44c5-b3cb-60d5f2e684d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "duration_t = df_work[[\"remip\", \"duration\", \"tunnelid\"]].groupby([\"remip\", \"tunnelid\"]).max().dropna().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c21ebb42-d734-4178-9099-7a3aae14b05e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final[\"n_tunnels\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").count()[\"duration\"])\n",
    "df_final[\"n_connections\"] = df_final[\"remip\"].map(df_work.value_counts(\"remip\"))\n",
    "df_final[\"duration_sum\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").sum()[\"duration\"])\n",
    "df_final[\"duration_min\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").agg(np.min)[\"duration\"])\n",
    "df_final[\"duration_max\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").agg(np.max)[\"duration\"])\n",
    "df_final[\"duration_mean\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").agg(np.mean)[\"duration\"])\n",
    "df_final[\"duration_var\"] = df_final[\"remip\"].map(duration_t[[\"remip\", \"duration\"]].groupby(\"remip\").agg(np.var)[\"duration\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6bc30b7c-49b5-460e-a296-77244e3d9dc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "sentby_t = df_work[~df_work[\"tunnelid\"].isnull()][[\"remip\", \"sentbyte\", \"tunnelid\"]].groupby([\"remip\", \"tunnelid\"]).max().dropna().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d53b3d88-b605-41dc-908a-230d6941ea1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final[\"sentbyte_sum\"] = df_final[\"remip\"].map(sentby_t[[\"remip\", \"sentbyte\"]].groupby(\"remip\").sum()[\"sentbyte\"])\n",
    "df_final[\"sentbyte_min\"] = df_final[\"remip\"].map(sentby_t[[\"remip\", \"sentbyte\"]].groupby(\"remip\").agg(np.min)[\"sentbyte\"])\n",
    "df_final[\"sentbyte_max\"] = df_final[\"remip\"].map(sentby_t[[\"remip\", \"sentbyte\"]].groupby(\"remip\").agg(np.max)[\"sentbyte\"])\n",
    "df_final[\"sentbyte_mean\"] = df_final[\"remip\"].map(sentby_t[[\"remip\", \"sentbyte\"]].groupby(\"remip\").agg(np.mean)[\"sentbyte\"])\n",
    "df_final[\"sentbyte_var\"] = df_final[\"remip\"].map(sentby_t[[\"remip\", \"sentbyte\"]].groupby(\"remip\").agg(np.var)[\"sentbyte\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c4309178-39b4-451c-9608-a8adeda26ebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_final[~df_final[\"n_tunnels\"].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "19cb45fc-681b-43ea-aeba-e2b80d17a980",
   "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>remip</th>\n",
       "      <th>n_tunnels</th>\n",
       "      <th>n_connections</th>\n",
       "      <th>duration_sum</th>\n",
       "      <th>duration_min</th>\n",
       "      <th>duration_max</th>\n",
       "      <th>duration_mean</th>\n",
       "      <th>duration_var</th>\n",
       "      <th>sentbyte_sum</th>\n",
       "      <th>sentbyte_min</th>\n",
       "      <th>sentbyte_max</th>\n",
       "      <th>sentbyte_mean</th>\n",
       "      <th>sentbyte_var</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>210.22.132.214</td>\n",
       "      <td>29.0</td>\n",
       "      <td>550</td>\n",
       "      <td>106014.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>15499.0</td>\n",
       "      <td>3655.655172</td>\n",
       "      <td>1.586899e+07</td>\n",
       "      <td>1.005893e+10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.707831e+09</td>\n",
       "      <td>3.468595e+08</td>\n",
       "      <td>2.064729e+17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>211.36.1.126</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1361</td>\n",
       "      <td>744569.0</td>\n",
       "      <td>24599.0</td>\n",
       "      <td>28801.0</td>\n",
       "      <td>28637.269231</td>\n",
       "      <td>6.784200e+05</td>\n",
       "      <td>1.212432e+09</td>\n",
       "      <td>2582718.0</td>\n",
       "      <td>1.259659e+08</td>\n",
       "      <td>4.663198e+07</td>\n",
       "      <td>1.850490e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>115.134.131.154</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1717</td>\n",
       "      <td>3128632.0</td>\n",
       "      <td>1164.0</td>\n",
       "      <td>2509427.0</td>\n",
       "      <td>173812.888889</td>\n",
       "      <td>3.523311e+11</td>\n",
       "      <td>1.790880e+10</td>\n",
       "      <td>167265.0</td>\n",
       "      <td>1.560168e+10</td>\n",
       "      <td>9.949331e+08</td>\n",
       "      <td>1.357241e+19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>113.161.106.18</td>\n",
       "      <td>13.0</td>\n",
       "      <td>583</td>\n",
       "      <td>297653.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>56986.0</td>\n",
       "      <td>22896.384615</td>\n",
       "      <td>4.215194e+08</td>\n",
       "      <td>4.110226e+08</td>\n",
       "      <td>25678.0</td>\n",
       "      <td>1.150723e+08</td>\n",
       "      <td>3.161712e+07</td>\n",
       "      <td>1.850948e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180.168.169.90</td>\n",
       "      <td>12.0</td>\n",
       "      <td>526</td>\n",
       "      <td>166454.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>36956.0</td>\n",
       "      <td>13871.166667</td>\n",
       "      <td>2.085070e+08</td>\n",
       "      <td>5.226736e+07</td>\n",
       "      <td>23420.0</td>\n",
       "      <td>1.299919e+07</td>\n",
       "      <td>4.355613e+06</td>\n",
       "      <td>2.089468e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>223.24.190.29</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55</td>\n",
       "      <td>26906.0</td>\n",
       "      <td>26906.0</td>\n",
       "      <td>26906.0</td>\n",
       "      <td>26906.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000106e+06</td>\n",
       "      <td>8000106.0</td>\n",
       "      <td>8.000106e+06</td>\n",
       "      <td>8.000106e+06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>58.153.80.60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14</td>\n",
       "      <td>367.0</td>\n",
       "      <td>367.0</td>\n",
       "      <td>367.0</td>\n",
       "      <td>367.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.554023e+06</td>\n",
       "      <td>8554023.0</td>\n",
       "      <td>8.554023e+06</td>\n",
       "      <td>8.554023e+06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>49.130.126.96</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33</td>\n",
       "      <td>13010.0</td>\n",
       "      <td>13010.0</td>\n",
       "      <td>13010.0</td>\n",
       "      <td>13010.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.473580e+07</td>\n",
       "      <td>34735804.0</td>\n",
       "      <td>3.473580e+07</td>\n",
       "      <td>3.473580e+07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>10.14.141.102</td>\n",
       "      <td>1.0</td>\n",
       "      <td>52</td>\n",
       "      <td>15298.0</td>\n",
       "      <td>15298.0</td>\n",
       "      <td>15298.0</td>\n",
       "      <td>15298.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.645677e+06</td>\n",
       "      <td>8645677.0</td>\n",
       "      <td>8.645677e+06</td>\n",
       "      <td>8.645677e+06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>114.45.9.148</td>\n",
       "      <td>1.0</td>\n",
       "      <td>64</td>\n",
       "      <td>32121.0</td>\n",
       "      <td>32121.0</td>\n",
       "      <td>32121.0</td>\n",
       "      <td>32121.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.083675e+07</td>\n",
       "      <td>20836746.0</td>\n",
       "      <td>2.083675e+07</td>\n",
       "      <td>2.083675e+07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               remip  n_tunnels  n_connections  duration_sum  duration_min  \\\n",
       "88    210.22.132.214       29.0            550      106014.0         114.0   \n",
       "5       211.36.1.126       26.0           1361      744569.0       24599.0   \n",
       "2    115.134.131.154       18.0           1717     3128632.0        1164.0   \n",
       "6     113.161.106.18       13.0            583      297653.0         215.0   \n",
       "3     180.168.169.90       12.0            526      166454.0         117.0   \n",
       "..               ...        ...            ...           ...           ...   \n",
       "96     223.24.190.29        1.0             55       26906.0       26906.0   \n",
       "101     58.153.80.60        1.0             14         367.0         367.0   \n",
       "103    49.130.126.96        1.0             33       13010.0       13010.0   \n",
       "114    10.14.141.102        1.0             52       15298.0       15298.0   \n",
       "182     114.45.9.148        1.0             64       32121.0       32121.0   \n",
       "\n",
       "     duration_max  duration_mean  duration_var  sentbyte_sum  sentbyte_min  \\\n",
       "88        15499.0    3655.655172  1.586899e+07  1.005893e+10           0.0   \n",
       "5         28801.0   28637.269231  6.784200e+05  1.212432e+09     2582718.0   \n",
       "2       2509427.0  173812.888889  3.523311e+11  1.790880e+10      167265.0   \n",
       "6         56986.0   22896.384615  4.215194e+08  4.110226e+08       25678.0   \n",
       "3         36956.0   13871.166667  2.085070e+08  5.226736e+07       23420.0   \n",
       "..            ...            ...           ...           ...           ...   \n",
       "96        26906.0   26906.000000           NaN  8.000106e+06     8000106.0   \n",
       "101         367.0     367.000000           NaN  8.554023e+06     8554023.0   \n",
       "103       13010.0   13010.000000           NaN  3.473580e+07    34735804.0   \n",
       "114       15298.0   15298.000000           NaN  8.645677e+06     8645677.0   \n",
       "182       32121.0   32121.000000           NaN  2.083675e+07    20836746.0   \n",
       "\n",
       "     sentbyte_max  sentbyte_mean  sentbyte_var  \n",
       "88   1.707831e+09   3.468595e+08  2.064729e+17  \n",
       "5    1.259659e+08   4.663198e+07  1.850490e+15  \n",
       "2    1.560168e+10   9.949331e+08  1.357241e+19  \n",
       "6    1.150723e+08   3.161712e+07  1.850948e+15  \n",
       "3    1.299919e+07   4.355613e+06  2.089468e+13  \n",
       "..            ...            ...           ...  \n",
       "96   8.000106e+06   8.000106e+06           NaN  \n",
       "101  8.554023e+06   8.554023e+06           NaN  \n",
       "103  3.473580e+07   3.473580e+07           NaN  \n",
       "114  8.645677e+06   8.645677e+06           NaN  \n",
       "182  2.083675e+07   2.083675e+07           NaN  \n",
       "\n",
       "[93 rows x 13 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.sort_values(by=\"n_tunnels\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0644faa3-e560-4394-91f1-8809dcb27afc",
   "metadata": {},
   "source": [
    "### PCA to reduce components to 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f6e3e13c-7806-437f-b1cc-98282f65b345",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace NaN with 0 and apply PCA. No Label Encoder\n",
    "def apply_pca2(df_int):\n",
    "    from sklearn.decomposition import PCA\n",
    "    df_int.replace(np.nan, \"0\", inplace=True)\n",
    "    \n",
    "    pca = PCA(n_components=2)\n",
    "    principalComponents = pca.fit_transform(df_int)\n",
    "\n",
    "    principalDf = pd.DataFrame(data=principalComponents, columns=['x', 'y'])\n",
    "    \n",
    "    return principalDf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f866bdc9-719b-4e16-8955-785a7ebabc31",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pca = df_final[[\"n_tunnels\",\"n_connections\", \"duration_sum\",\"sentbyte_sum\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "337a27b8-fd10-4683-aab9-7973356ce885",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\python38\\lib\\site-packages\\pandas\\core\\frame.py:5233: SettingWithCopyWarning:\n",
      "\n",
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_pca_final = apply_pca2(df_pca)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "211c1994-5aa8-4df6-86f8-b23bf30c5bb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "remip = df_final[\"remip\"].reset_index()\n",
    "dfjoin = df_pca_final.join(remip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "bbf1b3e6-03c5-4a9c-b844-f2d87a9a3a50",
   "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>x</th>\n",
       "      <th>y</th>\n",
       "      <th>remip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-5.662833e+10</td>\n",
       "      <td>-3.446632e+05</td>\n",
       "      <td>10.14.141.158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-5.663200e+10</td>\n",
       "      <td>-3.626998e+05</td>\n",
       "      <td>192.168.0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-3.875444e+10</td>\n",
       "      <td>2.700874e+06</td>\n",
       "      <td>115.134.131.154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-5.661097e+10</td>\n",
       "      <td>-2.053365e+05</td>\n",
       "      <td>180.168.169.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-4.718642e+10</td>\n",
       "      <td>2.614039e+06</td>\n",
       "      <td>171.99.130.158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>-5.665053e+10</td>\n",
       "      <td>-3.699487e+05</td>\n",
       "      <td>10.14.141.119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>-5.665975e+10</td>\n",
       "      <td>-3.683208e+05</td>\n",
       "      <td>181.215.176.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>-5.665127e+10</td>\n",
       "      <td>-3.453214e+05</td>\n",
       "      <td>223.24.94.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>-5.666324e+10</td>\n",
       "      <td>-3.711788e+05</td>\n",
       "      <td>222.125.64.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>-5.664240e+10</td>\n",
       "      <td>-3.395711e+05</td>\n",
       "      <td>114.45.9.148</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               x             y            remip\n",
       "0  -5.662833e+10 -3.446632e+05    10.14.141.158\n",
       "1  -5.663200e+10 -3.626998e+05     192.168.0.16\n",
       "2  -3.875444e+10  2.700874e+06  115.134.131.154\n",
       "3  -5.661097e+10 -2.053365e+05   180.168.169.90\n",
       "4  -4.718642e+10  2.614039e+06   171.99.130.158\n",
       "..           ...           ...              ...\n",
       "88 -5.665053e+10 -3.699487e+05    10.14.141.119\n",
       "89 -5.665975e+10 -3.683208e+05  181.215.176.250\n",
       "90 -5.665127e+10 -3.453214e+05     223.24.94.33\n",
       "91 -5.666324e+10 -3.711788e+05    222.125.64.74\n",
       "92 -5.664240e+10 -3.395711e+05     114.45.9.148\n",
       "\n",
       "[93 rows x 3 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfjoin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "eaeaf20c-5dca-401d-b4e1-4194ed99befb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "import plotly.io as pio\n",
    "pio.renderers.default = 'iframe' # or 'notebook' or 'colab'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "868db1e7-a953-4f4c-b6bf-4594d66d3ab0",
   "metadata": {},
   "source": [
    "### PCA \"n_tunnels\",\"n_connections\", \"duration_sum\",\"sentbyte_sum\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "03196db3-f636-4914-8bd0-0950b0c08c88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"100%\"\n",
       "    height=\"545px\"\n",
       "    src=\"iframe_figures/figure_28.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = px.scatter(dfjoin, x=\"x\", y=\"y\", color='remip', title='Scatter VPN connections PCA \"n_tunnels\",\"n_connections\", \"duration_sum\",\"sentbyte_sum\"',\n",
    "                 color_discrete_sequence=[\"blue\"],\n",
    "             color_discrete_map={\n",
    "                \"191.101.209.126\": \"red\",\n",
    "                 \"191.101.209.72\":\"red\"\n",
    "             })\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "219ec6bd-d6be-4628-b248-aeb76244c1d9",
   "metadata": {},
   "source": [
    "### Without PCA duration, sentbyte"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ed645b1c-0f0a-4ca6-b98d-cf37910b2588",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"100%\"\n",
       "    height=\"545px\"\n",
       "    src=\"iframe_figures/figure_29.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = px.scatter(df_final, x=\"duration_sum\", y=\"n_connections\", color='remip', title='Scatter VPN connections without PCA Connections VS Duration',\n",
    "                 color_discrete_sequence=[\"blue\"],\n",
    "             color_discrete_map={\n",
    "                \"191.101.209.126\": \"red\",\n",
    "                 \"191.101.209.72\":\"red\"\n",
    "             })\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6e708dd2-6dfb-431b-b800-59023029ac60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"100%\"\n",
       "    height=\"545px\"\n",
       "    src=\"iframe_figures/figure_17.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = px.scatter(df_final, x=\"duration_sum\", y=\"sentbyte_sum\", color='remip', title='Scatter VPN connections without PCA, Duration VS Sentbyte',\n",
    "                 color_discrete_sequence=[\"blue\"],\n",
    "             color_discrete_map={\n",
    "                \"191.101.209.126\": \"red\",\n",
    "                 \"191.101.209.72\":\"red\"\n",
    "             })\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "44bae1ff-dfc7-44fb-8667-91a124608ff8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"100%\"\n",
       "    height=\"545px\"\n",
       "    src=\"iframe_figures/figure_18.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = px.scatter(df_final, x=\"n_connections\", y=\"sentbyte_sum\", color='remip', title='Scatter VPN connections without PCA Connections VS Sentbyte',\n",
    "                 color_discrete_sequence=[\"blue\"],\n",
    "             color_discrete_map={\n",
    "                \"191.101.209.126\": \"red\",\n",
    "                 \"191.101.209.72\":\"red\"\n",
    "             })\n",
    "fig.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}