AC&CD.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AC&CD: Active C&C Detector\n",
    "![ACCD](./images/ACCD.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How AC&CD works\n",
    "\n",
    "AC&CD analyzes time delta size distribution and data size(sent and received bytes) distribution of a traffic between the same source and the destination and produces an overall score.\n",
    "\n",
    "## Time delta distribution scoring\n",
    "AC&CD calculates a score based on time delta distribution. \n",
    "\n",
    "In a real attack, attackers change the sleep and jitter configuration on the fly according to their needs such as SOCKS tunneling, off-business hours, etc. The change in the sleep and jitter makes the traffic not look like beaconing and causes False Negatives. To solve the issue, AC&CD analyzes the certain part of the time delta set where higher sleep values can't cause false negatives. The certain part of the time delta set is roughly the first 60% of the time delta set. Therefore, AC&CD calculates the time delta distribution score from the first 60% of the time delta set by using 15th, 30th and 45th percentiles of the whole dataset.\n",
    "\n",
    "### Median Absolute Deviation of time delta\n",
    "When jitter is used in beaconing, the time delta set is expected to have some dispersion around the median of the time delta set. Median Absolute Deviation(MAD) is used to check dispersion. Dividing the MAD by the median of the time delta set gives a normalized dispersion value which is the jitter in the dataset. The normalized dispersion value is used to calculate time delta score.\n",
    "\n",
    "\n",
    "## Data size scoring\n",
    "AC&CD calculates an overal data size score based on data size(sent and received bytes) distribution. \n",
    "\n",
    "### Sent Bytes Distribution\n",
    "AC&CD calculates the sent bytes distribution score by using 15th, 30th and 45th percentiles of the whole dataset as in time delta distribution scoring.\n",
    "\n",
    "### Received Bytes Distribution\n",
    "In real attacks, attackers send command/tools to the beacon. Therefore, there has to be some communication with higher received bytes. AC&CD checks the maximum received bytes in the dataset and if it's less than 20.000, it reduces the data size score. \n",
    "\n",
    "## Skewness\n",
    "Jitter usage in beaconing causes random distribution of the sleep values and does not guarantee uniform distribution. Therefore, AC&CD doesn't use skewness in the scoring.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Implementing the algorithm using Jupyter notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Steps:  \n",
    "0. Prepare data\n",
    "1. Group connections between the same hosts and aggregate timestamps into a list\n",
    "2. Calculate connection count and remove short sessions\n",
    "3. Sort the connection timestamp ascending\n",
    "4. Calculate time deltas\n",
    "5. Generate variables required for score calculation (time delta and data size)\n",
    "6. Calculate the score (time delta, data size, overall score)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "# Disable warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preparing the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Timestamp</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>Protocol</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>SourceIP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7/14/2023, 1:11:02.921 AM</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>257</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>10.10.25.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7/14/2023, 1:10:10.909 AM</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>257</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>10.10.25.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7/14/2023, 1:09:13.932 AM</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>257</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>10.10.25.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7/14/2023, 1:08:06.920 AM</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>257</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>10.10.25.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7/14/2023, 1:06:58.916 AM</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>257</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>10.10.25.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp DestinationHostName  DestinationPort  \\\n",
       "0  7/14/2023, 1:11:02.921 AM      www.amazon.com              NaN   \n",
       "1  7/14/2023, 1:10:10.909 AM      www.amazon.com              NaN   \n",
       "2  7/14/2023, 1:09:13.932 AM      www.amazon.com              NaN   \n",
       "3  7/14/2023, 1:08:06.920 AM      www.amazon.com              NaN   \n",
       "4  7/14/2023, 1:06:58.916 AM      www.amazon.com              NaN   \n",
       "\n",
       "  DestinationIP  ReceivedBytes  SentBytes  Protocol RequestMethod     SourceIP  \n",
       "0     1.2.3.4.5            257        279       NaN           GET  10.10.25.52  \n",
       "1     1.2.3.4.5            257        279       NaN           GET  10.10.25.52  \n",
       "2     1.2.3.4.5            257        279       NaN           GET  10.10.25.52  \n",
       "3     1.2.3.4.5            257        279       NaN           GET  10.10.25.52  \n",
       "4     1.2.3.4.5            257        279       NaN           GET  10.10.25.52  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df = pd.read_csv('./sample-data/sample_beacon_dataset.csv')\n",
    "http_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign field/column names to variables\n",
    "f_timestamp = 'Timestamp'\n",
    "f_src_ip = 'SourceIP'\n",
    "f_dst_ip = 'DestinationIP'\n",
    "f_dst_host = 'DestinationHostName'\n",
    "f_dst_port = 'DestinationPort'\n",
    "f_http_method = 'RequestMethod'\n",
    "f_sent_bytes = 'SentBytes'\n",
    "f_received_bytes = 'ReceivedBytes'\n",
    "\n",
    "columns_to_filter = [f_timestamp, f_src_ip, f_dst_ip, f_dst_host, f_dst_port, f_http_method, f_sent_bytes, f_received_bytes]\n",
    "columns_to_groupby = [f_src_ip, f_dst_host, f_http_method]\n",
    "# columns to display after the analysis\n",
    "columns_to_display = ['Score', 'tsScore', 'dsScore', 'conn_count', 'min_execution_bytes', 'dsResponseMax', 'destination_prevalence', f_src_ip, f_dst_ip, f_dst_host, f_http_method, f_dst_port, f_sent_bytes, f_received_bytes, 'deltas']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering Required Columns\n",
    "```\n",
    "df.loc[first_row_index:last_row_index , ['column1', 'column3']]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 876 entries, 0 to 875\n",
      "Data columns (total 8 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   Timestamp            876 non-null    object \n",
      " 1   SourceIP             876 non-null    object \n",
      " 2   DestinationIP        876 non-null    object \n",
      " 3   DestinationHostName  876 non-null    object \n",
      " 4   DestinationPort      0 non-null      float64\n",
      " 5   RequestMethod        876 non-null    object \n",
      " 6   SentBytes            876 non-null    int64  \n",
      " 7   ReceivedBytes        876 non-null    int64  \n",
      "dtypes: float64(1), int64(2), object(5)\n",
      "memory usage: 54.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# get all rows and only the required colums\n",
    "http_df = http_df.loc[:,columns_to_filter]\n",
    "http_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fixing the Timestamp Column Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Timestamp</th>\n",
       "      <th>SourceIP</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-07-14 01:11:02.921</td>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>279</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-07-14 01:10:10.909</td>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>1.2.3.4.5</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GET</td>\n",
       "      <td>279</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Timestamp     SourceIP DestinationIP DestinationHostName  \\\n",
       "0 2023-07-14 01:11:02.921  10.10.25.52     1.2.3.4.5      www.amazon.com   \n",
       "1 2023-07-14 01:10:10.909  10.10.25.52     1.2.3.4.5      www.amazon.com   \n",
       "\n",
       "   DestinationPort RequestMethod  SentBytes  ReceivedBytes  \n",
       "0              NaN           GET        279            257  \n",
       "1              NaN           GET        279            257  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  unit conversion should match with the format (try ns, ms, or s)\n",
    "http_df[f_timestamp] = pd.to_datetime(http_df[f_timestamp], unit='ns')\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysing the Data\n",
    "Now the data is ready for statistical analysis. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping the Connections\n",
    "We can group the connections between the same hosts and aggregate the timestamps into a list. This way, we can use **pd.Series().diff** to easily calculate time deltas.  \n",
    "First, let's group the traffic and aggregate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">10.10.25.52</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">www.amazon.com</th>\n",
       "      <th>GET</th>\n",
       "      <td>[2023-07-14 01:11:02.921000, 2023-07-14 01:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POST</th>\n",
       "      <td>[2023-07-14 01:03:40.909000, 2023-07-14 01:02:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                       Timestamp  \\\n",
       "SourceIP    DestinationHostName RequestMethod                                                      \n",
       "10.10.25.52 www.amazon.com      GET            [2023-07-14 01:11:02.921000, 2023-07-14 01:10:...   \n",
       "                                POST           [2023-07-14 01:03:40.909000, 2023-07-14 01:02:...   \n",
       "\n",
       "                                                                                   DestinationIP  \\\n",
       "SourceIP    DestinationHostName RequestMethod                                                      \n",
       "10.10.25.52 www.amazon.com      GET            [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "                                POST           [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                                                                 DestinationPort  \\\n",
       "SourceIP    DestinationHostName RequestMethod                                                      \n",
       "10.10.25.52 www.amazon.com      GET            [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "                                POST           [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                                                                       SentBytes  \\\n",
       "SourceIP    DestinationHostName RequestMethod                                                      \n",
       "10.10.25.52 www.amazon.com      GET            [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "                                POST           [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                                                                   ReceivedBytes  \n",
       "SourceIP    DestinationHostName RequestMethod                                                     \n",
       "10.10.25.52 www.amazon.com      GET            [257, 257, 257, 257, 257, 257, 257, 257, 257, ...  \n",
       "                                POST           [257, 257, 257, 257, 257, 257, 257, 257, 257, ...  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you have a large dateset, using groupby and aggregate(list) might be slow. Consider using dask\n",
    "http_df = http_df.groupby(columns_to_groupby).agg(list)\n",
    "http_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reseting the index\n",
    "We need to reset the index to use all the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-14 01:11:02.921000, 2023-07-14 01:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-14 01:03:40.909000, 2023-07-14 01:02:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-14 01:11:02.921000, 2023-07-14 01:10:...   \n",
       "1  [2023-07-14 01:03:40.909000, 2023-07-14 01:02:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  \n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...  \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df.reset_index(inplace=True)\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculating Connection Count\n",
    "Length of the timestamp list ('ts') = connection count (since we grouped the traffic and aggregated the timestamp into a list)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-14 01:11:02.921000, 2023-07-14 01:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-14 01:03:40.909000, 2023-07-14 01:02:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-14 01:11:02.921000, 2023-07-14 01:10:...   \n",
       "1  [2023-07-14 01:03:40.909000, 2023-07-14 01:02:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848  \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...          28  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a new column 'conn_count', and for each row in the 'ts' column, apply a function and assign the returned value to the 'conn_count' column\n",
    "http_df['conn_count'] = http_df[f_timestamp].apply(lambda x: len(x))\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove short sessions\n",
    "Filter out traffic where the connection count is quite small"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 9)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df = http_df.loc[http_df['conn_count'] >= 24]\n",
    "http_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting the Timestamps\n",
    "Apply a lambda function to each row on the specified columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-13 11:10:33.076000, 2023-07-13 11:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "1  [2023-07-13 11:10:33.076000, 2023-07-13 11:10:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848  \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...          28  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df[f_timestamp] = http_df[f_timestamp].apply(lambda x: sorted(x))\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculating Time Delta\n",
    "Apply **pd.Series.diff()** to the 'ts' column, which is sorted, and assign the resulting list into a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-13 11:10:33.076000, 2023-07-13 11:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>28</td>\n",
       "      <td>[0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "1  [2023-07-13 11:10:33.076000, 2023-07-13 11:10:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...          28   \n",
       "\n",
       "                                              deltas  \n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  \n",
       "1  [0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert list into a Series object, get time delta, convert the result back into a list and assign it to the 'deltas' column\n",
    "http_df['deltas'] = http_df[f_timestamp].apply(lambda x: pd.Series(x).diff().dt.seconds.dropna().tolist())\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate variables\n",
    "We need to generate the required variables to analyze time delta distribution, median absolute deviation, and connection count."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Variables for time delta dispersion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "http_df['tsLow'] = http_df['deltas'].apply(lambda x: np.percentile(np.array(x), 15))\n",
    "http_df['tsMid'] = http_df['deltas'].apply(lambda x: np.percentile(np.array(x), 30))\n",
    "http_df['tsHigh'] = http_df['deltas'].apply(lambda x: np.percentile(np.array(x), 45))\n",
    "http_df['tsMadm'] = http_df['deltas'].apply(lambda x: np.percentile(np.absolute(np.array(x) - np.median(np.array(x))), 30))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Variables for data size dispersion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "http_df['dsLow'] = http_df[f_sent_bytes].apply(lambda x: np.percentile(np.array(x), 15))\n",
    "http_df['dsMid'] = http_df[f_sent_bytes].apply(lambda x: np.percentile(np.array(x), 30))\n",
    "http_df['dsHigh'] = http_df[f_sent_bytes].apply(lambda x: np.percentile(np.array(x), 45))\n",
    "http_df['dsMadm'] = http_df[f_sent_bytes].apply(lambda x: np.percentile(np.absolute(np.array(x) - np.median(np.array(x))), 30))\n",
    "# Get max of received bytes\n",
    "http_df['dsResponseMax'] = http_df[f_received_bytes].apply(lambda x: max(x))\n",
    "# http_df['dsResponse_last_5'] = http_df[f_received_bytes].apply(lambda x: sorted(x)[-10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "      <th>tsLow</th>\n",
       "      <th>tsMid</th>\n",
       "      <th>tsHigh</th>\n",
       "      <th>tsMadm</th>\n",
       "      <th>dsLow</th>\n",
       "      <th>dsMid</th>\n",
       "      <th>dsHigh</th>\n",
       "      <th>dsMadm</th>\n",
       "      <th>dsResponseMax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>386058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-13 11:10:33.076000, 2023-07-13 11:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>28</td>\n",
       "      <td>[0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....</td>\n",
       "      <td>47.7</td>\n",
       "      <td>59.4</td>\n",
       "      <td>66.1</td>\n",
       "      <td>13.8</td>\n",
       "      <td>595.0</td>\n",
       "      <td>595.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>61478</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "1  [2023-07-13 11:10:33.076000, 2023-07-13 11:10:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...          28   \n",
       "\n",
       "                                              deltas  tsLow  tsMid  tsHigh  \\\n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...   18.0   52.0    56.0   \n",
       "1  [0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....   47.7   59.4    66.1   \n",
       "\n",
       "   tsMadm  dsLow  dsMid  dsHigh  dsMadm  dsResponseMax  \n",
       "0     4.0  279.0  279.0   279.0     0.0         386058  \n",
       "1    13.8  595.0  595.0   944.0   420.0          61478  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Interactive Beaconing Duration and Connection Count Validation\n",
    "Calculate duration of the assumed interactive beaconing phase.  \n",
    "Based on tsHigh and sum of the deltas up to the tsHigh value in the time delta list, there should be enough connections.  \n",
    "A Session with just 5 connections in 10 minutes shouldn't be consdired as beaconing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_int_duration(deltas: list) -> float:\n",
    "    \"\"\"Calculate the interactivity duration\"\"\"\n",
    "    _tmp = np.percentile(np.array(deltas), 45)\n",
    "    # if sleep is set to 0, we may see deltas as 0 seconds but the actual time delta is around is 0.2 seconds in that situation.\n",
    "    # so, we are adding 0.2 seconds to the delta if it is less than or equal to 0, and getting the sum of all deltas up to the tsHigh which is 45th percentile.\n",
    "    return sum([i if i > 0 else 0.2 for i in [num for num in deltas if num <= _tmp]])\n",
    "\n",
    "def calc_int_conn_count(deltas: list) -> int:\n",
    "    \"\"\"Calculate the interactivity connection count\"\"\"\n",
    "    _tmp = np.percentile(np.array(deltas), 45)\n",
    "    return len([i for i in deltas if i <= _tmp])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "      <th>...</th>\n",
       "      <th>tsMid</th>\n",
       "      <th>tsHigh</th>\n",
       "      <th>tsMadm</th>\n",
       "      <th>dsLow</th>\n",
       "      <th>dsMid</th>\n",
       "      <th>dsHigh</th>\n",
       "      <th>dsMadm</th>\n",
       "      <th>dsResponseMax</th>\n",
       "      <th>interactivity_duration</th>\n",
       "      <th>interactivity_conn_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "      <td>...</td>\n",
       "      <td>52.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>386058</td>\n",
       "      <td>13475.2</td>\n",
       "      <td>389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>POST</td>\n",
       "      <td>[2023-07-13 11:10:33.076000, 2023-07-13 11:10:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[595, 425214, 595, 5116, 595, 992910, 1870023,...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>28</td>\n",
       "      <td>[0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....</td>\n",
       "      <td>...</td>\n",
       "      <td>59.4</td>\n",
       "      <td>66.1</td>\n",
       "      <td>13.8</td>\n",
       "      <td>595.0</td>\n",
       "      <td>595.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>61478</td>\n",
       "      <td>469.8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "1  10.10.25.52      www.amazon.com          POST   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "1  [2023-07-13 11:10:33.076000, 2023-07-13 11:10:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "1  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "1  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "1  [595, 425214, 595, 5116, 595, 992910, 1870023,...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "1  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...          28   \n",
       "\n",
       "                                              deltas  ...  tsMid  tsHigh  \\\n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  ...   52.0    56.0   \n",
       "1  [0.0, 0.0, 0.0, 0.0, 4100.0, 123.0, 458.0, 60....  ...   59.4    66.1   \n",
       "\n",
       "   tsMadm  dsLow  dsMid  dsHigh  dsMadm  dsResponseMax  \\\n",
       "0     4.0  279.0  279.0   279.0     0.0         386058   \n",
       "1    13.8  595.0  595.0   944.0   420.0          61478   \n",
       "\n",
       "   interactivity_duration  interactivity_conn_count  \n",
       "0                 13475.2                       389  \n",
       "1                   469.8                        12  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# interactivity_duration: seconds\n",
    "# min_required_conn_count: minimum number of connections required to be considered as a valid beacon\n",
    "http_df['interactivity_duration'] = http_df['deltas'].apply(lambda x: calc_int_duration(x))\n",
    "http_df['interactivity_conn_count'] = http_df['deltas'].apply(lambda x: calc_int_conn_count(x))\n",
    "http_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "      <th>...</th>\n",
       "      <th>tsMid</th>\n",
       "      <th>tsHigh</th>\n",
       "      <th>tsMadm</th>\n",
       "      <th>dsLow</th>\n",
       "      <th>dsMid</th>\n",
       "      <th>dsHigh</th>\n",
       "      <th>dsMadm</th>\n",
       "      <th>dsResponseMax</th>\n",
       "      <th>interactivity_duration</th>\n",
       "      <th>interactivity_conn_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "      <td>...</td>\n",
       "      <td>52.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>386058</td>\n",
       "      <td>13475.2</td>\n",
       "      <td>389</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "\n",
       "                                              deltas  ...  tsMid  tsHigh  \\\n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  ...   52.0    56.0   \n",
       "\n",
       "   tsMadm  dsLow  dsMid  dsHigh  dsMadm  dsResponseMax  \\\n",
       "0     4.0  279.0  279.0   279.0     0.0         386058   \n",
       "\n",
       "   interactivity_duration  interactivity_conn_count  \n",
       "0                 13475.2                       389  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# False Positives can be removed by filtering out the rows with 'min_interactivity_conn_count' less than a threshold.\n",
    "# Additionally, we can filter out the rows with 'interactivity_duration' less than a certain threshold.\n",
    "min_interactivity_duration = 3600 # 1 hour\n",
    "min_interactivity_conn_count = 24 # 24 connections\n",
    "http_df = http_df.loc[(http_df['interactivity_conn_count'] >= min_interactivity_conn_count) & (http_df['interactivity_duration'] >= min_interactivity_duration)]\n",
    "http_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Execution and Data Size During Interactive Beaconing\n",
    "There should be some connections with higher data size during the interactive beaconing phase that represents the command/tool execution.  \n",
    "`count` and `minimum` of the data size of those connections is used as a threshold for FP reduction. For example, if we expect an attacker to perform at least 10 executions involving tools(not just basic commands like whoami), there should be at least 10 connections with data size higher than a threshold.  \n",
    "`min_execution_count` and `min_execution_bytes_threshold` are used to reduce the FP.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_execution_count = 10\n",
    "min_execution_bytes_threshold = 20000\n",
    "# min_execution_bytes : the last 15th largest value in the list\n",
    "http_df['min_execution_bytes'] = http_df[f_received_bytes].apply(lambda x: sorted(x)[-min_execution_count])\n",
    "\n",
    "# remove the false positives\n",
    "http_df = http_df.query(\"min_execution_bytes > @min_execution_bytes_threshold\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculating the score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set jitter percentage threshold\n",
    "jitter_treshold = 55 # => 55%\n",
    "\n",
    "# Time delta score calculation\n",
    "def ts_score(tsMadm, tsMid, jitter_threshold = 55):\n",
    "    score = 0\n",
    "    if tsMid > 0:\n",
    "        if (tsMadm / tsMid) * 100 < jitter_threshold:\n",
    "            score = 1\n",
    "        else:\n",
    "            score = 1 - ((tsMadm / tsMid) * 0.4)\n",
    "    else:\n",
    "        score = 1\n",
    "    return score\n",
    "\n",
    "# Data size score calculation\n",
    "def ds_score(dsMadm, dsMid, dsResponseMax, jitter_threshold = 25):\n",
    "    score = 0\n",
    "    if dsMid > 0:\n",
    "        if (dsMadm / dsMid) * 100 < jitter_threshold:\n",
    "            score = 1\n",
    "        else:\n",
    "            score = 1 - ((dsMadm / dsMid) * 0.4)\n",
    "    else:\n",
    "        score = 1\n",
    "    if dsResponseMax < 20000:\n",
    "        score = score - 0.3\n",
    "    return score\n",
    "\n",
    "http_df['tsScore'] = http_df.apply(lambda x: ts_score(x['tsMadm'], x['tsMid']), axis=1)\n",
    "http_df['dsScore'] = http_df.apply(lambda x: ds_score(x['dsMadm'], x['dsMid'], x['dsResponseMax']), axis=1)\n",
    "\n",
    "# Final Score calculation\n",
    "http_df['Score'] = (http_df['dsScore'] + http_df['tsScore']) / 2\n",
    "\n",
    "# Sort Results\n",
    "http_df.sort_values(by= 'Score', ascending=False, inplace=True, ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter Beaconing Traffic with High Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "      <th>...</th>\n",
       "      <th>dsMid</th>\n",
       "      <th>dsHigh</th>\n",
       "      <th>dsMadm</th>\n",
       "      <th>dsResponseMax</th>\n",
       "      <th>interactivity_duration</th>\n",
       "      <th>interactivity_conn_count</th>\n",
       "      <th>min_execution_bytes</th>\n",
       "      <th>tsScore</th>\n",
       "      <th>dsScore</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "      <td>...</td>\n",
       "      <td>279.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>386058</td>\n",
       "      <td>13475.2</td>\n",
       "      <td>389</td>\n",
       "      <td>79882</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "\n",
       "                                              deltas  ...  dsMid  dsHigh  \\\n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  ...  279.0   279.0   \n",
       "\n",
       "   dsMadm  dsResponseMax  interactivity_duration  interactivity_conn_count  \\\n",
       "0     0.0         386058                 13475.2                       389   \n",
       "\n",
       "   min_execution_bytes  tsScore  dsScore  Score  \n",
       "0                79882        1        1    1.0  \n",
       "\n",
       "[1 rows x 25 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df = http_df.query(\"Score > 0.85\")\n",
    "http_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enrich Results with Destination Prevalence\n",
    "The prevalence enrichment should be applied after filtering the beaconing traffic with high scores. Otherwise, there is a high chance of false negatives."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>SourceIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>deltas</th>\n",
       "      <th>...</th>\n",
       "      <th>dsHigh</th>\n",
       "      <th>dsMadm</th>\n",
       "      <th>dsResponseMax</th>\n",
       "      <th>interactivity_duration</th>\n",
       "      <th>interactivity_conn_count</th>\n",
       "      <th>min_execution_bytes</th>\n",
       "      <th>tsScore</th>\n",
       "      <th>dsScore</th>\n",
       "      <th>Score</th>\n",
       "      <th>destination_prevalence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[2023-07-13 11:05:59.388000, 2023-07-13 11:06:...</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>848</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "      <td>...</td>\n",
       "      <td>279.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>386058</td>\n",
       "      <td>13475.2</td>\n",
       "      <td>389</td>\n",
       "      <td>79882</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SourceIP DestinationHostName RequestMethod  \\\n",
       "0  10.10.25.52      www.amazon.com           GET   \n",
       "\n",
       "                                           Timestamp  \\\n",
       "0  [2023-07-13 11:05:59.388000, 2023-07-13 11:06:...   \n",
       "\n",
       "                                       DestinationIP  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...   \n",
       "\n",
       "                                     DestinationPort  \\\n",
       "0  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "\n",
       "                                       ReceivedBytes  conn_count  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...         848   \n",
       "\n",
       "                                              deltas  ...  dsHigh  dsMadm  \\\n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  ...   279.0     0.0   \n",
       "\n",
       "   dsResponseMax  interactivity_duration  interactivity_conn_count  \\\n",
       "0         386058                 13475.2                       389   \n",
       "\n",
       "   min_execution_bytes  tsScore  dsScore  Score  destination_prevalence  \n",
       "0                79882        1        1    1.0                       1  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define columns for prevalnce calculation\n",
    "# Example: count of distinct source IPs for a destination IP AND destination host\n",
    "f_prevalence_src_group = f_src_ip # use only one column (src_ip, src_host, src_user, etc.)\n",
    "f_prevalence_dst_group = f_dst_host\n",
    "dst_prevalence = http_df.groupby(f_prevalence_dst_group)[f_prevalence_src_group].nunique().to_frame().reset_index().rename(columns={f_prevalence_src_group: 'destination_prevalence'})\n",
    "http_df = http_df.merge(dst_prevalence, on=f_prevalence_dst_group, how='left')\n",
    "http_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter Results with Low Prevalence\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>Score</th>\n",
       "      <th>tsScore</th>\n",
       "      <th>dsScore</th>\n",
       "      <th>conn_count</th>\n",
       "      <th>min_execution_bytes</th>\n",
       "      <th>dsResponseMax</th>\n",
       "      <th>destination_prevalence</th>\n",
       "      <th>SourceIP</th>\n",
       "      <th>DestinationIP</th>\n",
       "      <th>DestinationHostName</th>\n",
       "      <th>RequestMethod</th>\n",
       "      <th>DestinationPort</th>\n",
       "      <th>SentBytes</th>\n",
       "      <th>ReceivedBytes</th>\n",
       "      <th>deltas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>848</td>\n",
       "      <td>79882</td>\n",
       "      <td>386058</td>\n",
       "      <td>1</td>\n",
       "      <td>10.10.25.52</td>\n",
       "      <td>[1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...</td>\n",
       "      <td>www.amazon.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>[nan, nan, nan, nan, nan, nan, nan, nan, nan, ...</td>\n",
       "      <td>[279, 279, 279, 279, 279, 279, 279, 279, 279, ...</td>\n",
       "      <td>[257, 257, 257, 257, 257, 257, 257, 257, 257, ...</td>\n",
       "      <td>[10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Score  tsScore  dsScore  conn_count  min_execution_bytes  dsResponseMax  \\\n",
       "0    1.0        1        1         848                79882         386058   \n",
       "\n",
       "   destination_prevalence     SourceIP  \\\n",
       "0                       1  10.10.25.52   \n",
       "\n",
       "                                       DestinationIP DestinationHostName  \\\n",
       "0  [1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1.2.3.4.5, 1...      www.amazon.com   \n",
       "\n",
       "  RequestMethod                                    DestinationPort  \\\n",
       "0           GET  [nan, nan, nan, nan, nan, nan, nan, nan, nan, ...   \n",
       "\n",
       "                                           SentBytes  \\\n",
       "0  [279, 279, 279, 279, 279, 279, 279, 279, 279, ...   \n",
       "\n",
       "                                       ReceivedBytes  \\\n",
       "0  [257, 257, 257, 257, 257, 257, 257, 257, 257, ...   \n",
       "\n",
       "                                              deltas  \n",
       "0  [10.0, 155.0, 68.0, 39.0, 0.0, 8.0, 8.0, 54.0,...  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "http_df[columns_to_display].query(\"destination_prevalence < 5\")"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "8b31ef20ae276fecbdd8bf57e61935f42b8506dff183058cb685e575ebb4c990"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}