Forti_VPN_Logs_Analysis-Public.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": 2,
   "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": 4,
   "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_out\"\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": 5,
   "id": "cb52ddf6-6371-4893-856a-33624ed594e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 5min 33s\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_forti\"\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": 32,
   "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_vpn_out\\*.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_vpn_out_fix')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5f8361f-2290-4c2d-bbb0-8a5ecea376e5",
   "metadata": {},
   "source": [
    "## Reading log files with Dask from disk "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "a5535eb0-472f-4d67-9c68-949bbc9c3287",
   "metadata": {},
   "outputs": [],
   "source": [
    "import dask.dataframe as dd\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5cc4cc1c-f39f-4cb9-b48e-016185bbbfba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 53 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = 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": 63,
   "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": 64,
   "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": 65,
   "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": 66,
   "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": 68,
   "id": "fea83457-d953-4358-ad39-7da60ffa6602",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1min 29s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_exf = df[[\"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": 184,
   "id": "6c48a768-55b4-41da-a4a6-34ea4dd47ffa",
   "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>level_0</th>\n",
       "      <th>index</th>\n",
       "      <th>remip</th>\n",
       "      <th>tunnelid</th>\n",
       "      <th>sentbyte</th>\n",
       "      <th>h_sentbyte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>495</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>164</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>214</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>291</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>361</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>293</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>37</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>32</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>13</td>\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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   level_0  index            remip      tunnelid      sentbyte h_sentbyte\n",
       "0      495    495    103.6.139.246  2973221928.0  3.470560e+12     3.2TiB\n",
       "1        1      1    103.6.139.246  3054928288.0  1.646262e+12     1.5TiB\n",
       "2      164    164  191.101.209.126  1826793719.0  3.506824e+10    32.7GiB\n",
       "3      214    214   191.101.209.72  1826793700.0  2.084387e+10    19.4GiB\n",
       "4      291    291   191.101.209.72  1826793661.0  1.833587e+10    17.1GiB\n",
       "5      361    361  115.134.131.154  3068782335.0  1.560168e+10    14.5GiB\n",
       "6      293    293   171.99.130.158  3069050613.0  8.586856e+09     8.0GiB\n",
       "7       37     37   210.61.150.144  3067193495.0  7.414605e+09     6.9GiB\n",
       "8       32     32   202.188.229.70  3027033592.0  5.857543e+09     5.5GiB\n",
       "9       13     13  122.103.241.185  3069552181.0  4.883923e+09     4.5GiB"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ext_10"
   ]
  },
  {
   "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": 134,
   "id": "6bb98067-21a6-44dd-82ec-d024be327658",
   "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",
       "    </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",
       "      <td>Hong Kong</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",
       "      <td>Hong Kong</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",
       "      <td>United Kingdom</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",
       "      <td>United Kingdom</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",
       "      <td>United Kingdom</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",
       "      <td>Malaysia</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",
       "      <td>Thailand</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",
       "      <td>Taiwan</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",
       "      <td>Malaysia</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",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index            remip      tunnelid      sentbyte h_sentbyte                                   ASN   remip_country\n",
       "495    495    103.6.139.246  2973221928.0  3.470560e+12     3.2TiB  HKBN Enterprise Solutions HK Limited       Hong Kong\n",
       "1        1    103.6.139.246  3054928288.0  1.646262e+12     1.5TiB  HKBN Enterprise Solutions HK Limited       Hong Kong\n",
       "164    164  191.101.209.126  1826793719.0  3.506824e+10    32.7GiB               GTT Communications Inc.  United Kingdom\n",
       "214    214   191.101.209.72  1826793700.0  2.084387e+10    19.4GiB               GTT Communications Inc.  United Kingdom\n",
       "291    291   191.101.209.72  1826793661.0  1.833587e+10    17.1GiB               GTT Communications Inc.  United Kingdom\n",
       "361    361  115.134.131.154  3068782335.0  1.560168e+10    14.5GiB     TM Net, Internet Service Provider        Malaysia\n",
       "293    293   171.99.130.158  3069050613.0  8.586856e+09     8.0GiB                TRUE INTERNET Co.,Ltd.        Thailand\n",
       "37      37   210.61.150.144  3067193495.0  7.414605e+09     6.9GiB     Data Communication Business Group          Taiwan\n",
       "32      32   202.188.229.70  3027033592.0  5.857543e+09     5.5GiB     TM Net, Internet Service Provider        Malaysia\n",
       "13      13  122.103.241.185  3069552181.0  4.883923e+09     4.5GiB          ARTERIA Networks Corporation           Japan"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 173,
   "id": "6b3083b9-71b6-4752-8e16-bedbc9240e83",
   "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>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",
       "      <td>Hong Kong</td>\n",
       "      <td>False</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",
       "      <td>Hong Kong</td>\n",
       "      <td>False</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",
       "      <td>United Kingdom</td>\n",
       "      <td>False</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",
       "      <td>United Kingdom</td>\n",
       "      <td>False</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",
       "      <td>United Kingdom</td>\n",
       "      <td>False</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",
       "      <td>Malaysia</td>\n",
       "      <td>False</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",
       "      <td>Thailand</td>\n",
       "      <td>False</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",
       "      <td>Taiwan</td>\n",
       "      <td>False</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",
       "      <td>Malaysia</td>\n",
       "      <td>False</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",
       "      <td>Japan</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index            remip      tunnelid      sentbyte h_sentbyte                                   ASN   remip_country is_tor_node\n",
       "495    495    103.6.139.246  2973221928.0  3.470560e+12     3.2TiB  HKBN Enterprise Solutions HK Limited       Hong Kong       False\n",
       "1        1    103.6.139.246  3054928288.0  1.646262e+12     1.5TiB  HKBN Enterprise Solutions HK Limited       Hong Kong       False\n",
       "164    164  191.101.209.126  1826793719.0  3.506824e+10    32.7GiB               GTT Communications Inc.  United Kingdom       False\n",
       "214    214   191.101.209.72  1826793700.0  2.084387e+10    19.4GiB               GTT Communications Inc.  United Kingdom       False\n",
       "291    291   191.101.209.72  1826793661.0  1.833587e+10    17.1GiB               GTT Communications Inc.  United Kingdom       False\n",
       "361    361  115.134.131.154  3068782335.0  1.560168e+10    14.5GiB     TM Net, Internet Service Provider        Malaysia       False\n",
       "293    293   171.99.130.158  3069050613.0  8.586856e+09     8.0GiB                TRUE INTERNET Co.,Ltd.        Thailand       False\n",
       "37      37   210.61.150.144  3067193495.0  7.414605e+09     6.9GiB     Data Communication Business Group          Taiwan       False\n",
       "32      32   202.188.229.70  3027033592.0  5.857543e+09     5.5GiB     TM Net, Internet Service Provider        Malaysia       False\n",
       "13      13  122.103.241.185  3069552181.0  4.883923e+09     4.5GiB          ARTERIA Networks Corporation           Japan       False"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": "a3b24f98-175a-4cf0-8dc3-899e23f60cbf",
   "metadata": {},
   "source": [
    "## [TODO] Research communications by user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c2120a-3e60-4ef9-b4f5-a4ca45e701f8",
   "metadata": {},
   "source": [
    "## [TODO] Upload logs to Timesketch"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "333831d8-462c-4a45-98a0-dcbd3ce1c87e",
   "metadata": {},
   "source": [
    "## [TODO] Use of ML to find communication anomalies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0f1c532-88ab-40e1-8be6-02e076ba7080",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}