BD-Threat Hunting for Malware Beacons.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Threat Hunting Notebook: Malware Beacons by Network Pattern Analysis\n",
    "This notebook connects to Microsoft Azure Sentinel, queries records of network connections from Sysmon (Event ID 3), or Microsoft Defender for Endpoint (DeviceNetworkEvents) and analyzes the pattern of time between network connections to find communication that looks like a regularly repeating beacon used for Command and Control (C2). These are likely to be either an endpoint agent checking in (which should be well known and documented) or malware checking in with its C2 server."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Step 1: Run the code block below to import all the Python modules we need to start. If there are any errors, stop and fix them now before going on.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys, math,requests, json, datetime\n",
    "\n",
    "MIN_REQ_PYTHON = (3,6)\n",
    "if sys.version_info < MIN_REQ_PYTHON:\n",
    "    print('Check the Kernel->Change Kernel menu and ensure that Python 3.6')\n",
    "    print('or later is selected as the active kernel.')\n",
    "    sys.exit(\"Python %s.%s or later is required.\\n\" % MIN_REQ_PYTHON)\n",
    "\n",
    "#imports\n",
    "import yaml\n",
    "import msticpy.nbtools as nbtools\n",
    "\n",
    "#data library imports\n",
    "from msticpy.data.data_providers import QueryProvider\n",
    "import msticpy.data.data_query_reader as QueryReader\n",
    "from msticpy.data.param_extractor import extract_query_params\n",
    "import msticpy.nbtools as mas\n",
    "\n",
    "from msticpy.common.wsconfig import WorkspaceConfig # workspace configurations stored in msticpyconfig.yaml\n",
    "##from msticpy.sectools.geoip import GeoLiteLookup # Maxmind GeoCityLite database stored in ~/.msticpy/\n",
    "##geoip = GeoLiteLookup()\n",
    "\n",
    "##import vt # virus total api\n",
    "##import shodan\n",
    "from ipwhois import IPWhois\n",
    "from ipwhois.utils import get_countries\n",
    "import ipaddress\n",
    "\n",
    "from datetime import datetime, timedelta, timezone\n",
    "import pytz # handle timezones the same way Pandas does\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Import module for progress bars\n",
    "from tqdm.notebook import tqdm, trange\n",
    "\n",
    "# Import the modules needed to create an interactive data frame with IPyWidgets\n",
    "import ipywidgets as widgets\n",
    "from ipywidgets import interactive\n",
    "\n",
    "print('Imports Complete! If there are no errors reported, continue on!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Optional: Update KQLMagic extension ####\n",
    "Every once in a while, it's a good idea to update KQLMagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install Kqlmagic --no-cache-dir --upgrade\n",
    "%reload_ext Kqlmagic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to Workspace for data\n",
    "The next step is to connect this Notebook to your data source. All of the Workspace connection information should be set up in the msticpyconfig.yaml and config.json files, but you still need to authenticate with your user credentials that have read access to the Workspace you wish to connect to.\n",
    "\n",
    "Unless you specify otherwise, you'll be connected to the default workspace that you configured in your msticpyconfig YAML file.\n",
    "\n",
    "_Instructions_:\n",
    "1. Run the code block below\n",
    "2. Wait for the KQL Magic output to appear\n",
    "3. Click the button at the bottom of the output to copy the app code and open the authentication window\n",
    "4. Authenticate with your user account, then continue running this Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Create a QueryProvider object for running queries in our LogAnalytics workspace\n",
    "qry_prov = QueryProvider(data_environment='LogAnalytics')\n",
    "## Use the workspace configuration we've set up in msticpyconfig.yaml (you can also choose another workspace here)\n",
    "ws_config = WorkspaceConfig(workspace=\"Default\")\n",
    "qry_prov.connect(connection_str=ws_config.code_connect_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gather Network Event Data ##\n",
    "Before we can analyze network event data patterns, we need to get the raw information about timing of connection events. This notebook has KQL queries to get the data from Microsoft Defender for Endpoint if you have that, or Sysmon if you prefer to use that. All you really need from each event is the time, source IP and destination IP, so you could consume logs from Packetbeat or many other sources. If you have Zeek logs, you don't need this and should just use RITA to process those logs directly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query for Microsoft Defender for Endpoint ####\n",
    "Run the code block below if you use Microsoft Defender for Endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Microsoft Defender for Endpoint query that uses DeviceNetworkEvents\n",
    "network_conn_query = '''DeviceNetworkEvents\n",
    "| where TimeGenerated between (datetime(%s) .. datetime(%s))\n",
    "| where ActionType !in (\"InboundConnectionAccepted\", \"ListeningConnectionCreated\")\n",
    "| where isnotempty(RemoteIP)\n",
    "| project TimeGenerated, LocalIP, RemoteIP\n",
    "'''\n",
    "process_query = '''DeviceNetworkEvents\n",
    "| where TimeGenerated > ago(7d)\n",
    "| where LocalIP==\"%s\"\n",
    "| where RemoteIP==\"%s\"\n",
    "| extend Hostname = tostring(split(DeviceName, \".\")[0])\n",
    "| summarize count() by Hostname, \n",
    "UserName=InitiatingProcessAccountName, \n",
    "FileName=InitiatingProcessFileName,\n",
    "CommandLine=InitiatingProcessCommandLine, \n",
    "ProcessId=InitiatingProcessId, \n",
    "LocalIP, RemoteIP, RemotePort\n",
    "'''\n",
    "print(\"Defender for Endpoint query loaded.\")\n",
    "print(\"Do NOT run the next code block for Sysmon if you are using Defender for Endpoint. Skip ahead to next block.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query for Sysmon ####\n",
    "If you use Sysmon to collect Event ID 3 (Network Connections) then uncomment the code block below and run it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sysmon Event ID 3 -- if you already have a custom function\n",
    "# to parse Sysmon events, you may simplify this query by using\n",
    "# that. This notebook makes no assumptions or requirements for \n",
    "# custom queries that you might have set up and parses the raw event.\n",
    "# If you're looking for a good Sysmon parser custom function, see this:\n",
    "# https://github.com/Azure/Azure-Sentinel/tree/master/Parsers/Sysmon\n",
    "# or this:\n",
    "# https://github.com/BlueTeamLabs/sentinel-attack/tree/master/parser\n",
    "network_conn_query = '''Event\n",
    "| where Source == \"Microsoft-Windows-Sysmon\"\n",
    "| where EventID == 3\n",
    "| where TimeGenerated between (datetime(%s) .. datetime(%s))\n",
    "| extend EvData = parse_xml(EventData)\n",
    "| extend EventDetail = EvData.DataItem.EventData.Data\n",
    "| project-away EventData, EvData\n",
    "| extend NetworkConnectionInitiated = tobool(EventDetail.[7].[\"#text\"])\n",
    "| extend LocalIP = tostring(EventDetail.[9].[\"#text\"])\n",
    "| extend RemoteIP = tostring(EventDetail.[14].[\"#text\"])\n",
    "| where isnotempty(RemoteIP)\n",
    "| where NetworkConnectionInitiated == true\n",
    "| project TimeGenerated, LocalIP, RemoteIP\n",
    "'''\n",
    "process_query = '''\n",
    "Event\n",
    "| where Source == \"Microsoft-Windows-Sysmon\"\n",
    "| where EventID == 3\n",
    "| extend EvData = parse_xml(EventData)\n",
    "| extend EventDetail = EvData.DataItem.EventData.Data\n",
    "| project-away EventData, EvData\n",
    "| extend Hostname = tostring(split(Computer, \".\")[0])\n",
    "| extend NetworkConnectionInitiated = tobool(EventDetail.[7].[\"#text\"])\n",
    "| extend LocalIP = tostring(EventDetail.[9].[\"#text\"])\n",
    "| extend RemoteIP = tostring(EventDetail.[14].[\"#text\"])\n",
    "| extend RemotePort = tostring(EventDetail.[16].[\"#text\"])\n",
    "| extend ProcessId = tostring(EventDetail.[3].[\"#text\"])\n",
    "| extend ProcessPath = tostring(EventDetail.[4].[\"#text\"])\n",
    "| extend FileName = tostring(split(ProcessPath, @\"\\\\\")[-1])\n",
    "| extend UserName = tostring(EventDetail.[5].[\"#text\"])\n",
    "| extend NetworkProtocol = tostring(EventDetail.[6].[\"#text\"])\n",
    "| where LocalIP==\"%s\" and RemoteIP==\"%s\"\n",
    "| project-away EventDetail\n",
    "| summarize count() by Hostname, UserName, ProcessPath, \n",
    "FileName, ProcessId, LocalIP, RemoteIP, RemotePort\n",
    "'''\n",
    "print(\"KQL queries for Sysmon loaded. If you're querying Sysmon data, proceed.\")\n",
    "print(\"If you meant to query Defender for Endpoint (ATP) go back and run that cell.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## First get raw timing of network connections\n",
    "## This is such a massive amount of data that we can only query it\n",
    "## for about one hour's worth of results at a time\n",
    "num_hours_to_search = 24\n",
    "current_time = datetime.now(tz=pytz.utc)\n",
    "# If you don't want to query up to the current time but instead\n",
    "# you wish to go back a few days and query up to a different time,\n",
    "# just change the current_time variable. There's an example below\n",
    "#current_time = datetime.now(tz=pytz.utc) - timedelta(days=6)\n",
    "connection_timing = {}\n",
    "total_connections = 0\n",
    "\n",
    "for hour in trange(num_hours_to_search):\n",
    "    ##print(\"Querying network connections between %d and %d hours ago...\" % (hour+1, hour))\n",
    "    date1 = current_time - timedelta(hours=hour+1)\n",
    "    date2 = current_time - timedelta(hours=hour)\n",
    "    hour_query = network_conn_query % (date1, date2)\n",
    "    #print(hour_query)\n",
    "    df_hour_network_conns = qry_prov.exec_query(query=hour_query)\n",
    "    for index, row in df_hour_network_conns.iterrows():\n",
    "        key = tuple([row['LocalIP'], row['RemoteIP']])\n",
    "        if not key in connection_timing:\n",
    "            connection_timing[key] = []\n",
    "        connection_timing[key].append(row['TimeGenerated']) # add connection time to list for this IP pair\n",
    "        total_connections += 1\n",
    "    \n",
    "print(\"There were %d unique host pairs with %d total connections\" % (len(connection_timing), total_connections))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hunting for Network Beacons like RITA ###\n",
    "This code is inspired by and adapted from the algorithm in the open source project RITA from Active Countermeasures: \n",
    "https://github.com/activecm/rita\n",
    "The algorithm that analyzes network traffic to compute a beacon score can be found in this file:\n",
    "https://github.com/activecm/rita/blob/master/pkg/beacon/analyzer.go\n",
    "\n",
    "This works by looking at each pair of communicating IP addresses and the timing of all the network connections between them. It computes the difference in time between each connection and then scores them based on the following factors:\n",
    "* How regularly spaced apart are the connections?\n",
    "* What much dispersion is there between median timing and outliers?\n",
    "* How many connections are there over time?\n",
    "\n",
    "Run the code block below to do all of the calculations for you. It ignores any pairs of hosts that had fewer than six total connections.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "beacon_scores = []\n",
    "ip_pair_score_list = [] # for building a selection drop down later\n",
    "# current_time was set in last code cell\n",
    "earliest_time = current_time\n",
    "print(\"Hang on... this might take a minute...\")\n",
    "# compute the beacon score for each pair of communicating IPs\n",
    "for ip_pair in connection_timing.keys():\n",
    "    try:\n",
    "        local_ip = ipaddress.ip_address(ip_pair[0])\n",
    "        remote_ip = ipaddress.ip_address(ip_pair[1])\n",
    "    except:\n",
    "        continue\n",
    "    if local_ip.is_private and remote_ip.is_private:\n",
    "        continue\n",
    "    if local_ip.is_loopback and remote_ip.is_loopback:\n",
    "        continue\n",
    "    timing_list = connection_timing[ip_pair]\n",
    "    if len(timing_list) >= 15:\n",
    "        timing_list.sort()\n",
    "        if timing_list[0] < earliest_time:\n",
    "            earliest_time = timing_list[0]\n",
    "        time_span = timing_list[-1] - timing_list[0]\n",
    "        time_span_seconds = time_span.total_seconds()\n",
    "        diffs = []\n",
    "        for i in range(len(timing_list)-1):\n",
    "            timediff = timing_list[i+1] - timing_list[i] # results in a Timedelta instance\n",
    "            if timediff.total_seconds() > 1.0:\n",
    "                diffs.append(timediff.total_seconds()) # keep track of the time differences in seconds\n",
    "        if len(diffs) < 6:\n",
    "            continue # if we don't have at least 6 connections separated by at least one second, skip this\n",
    "        # sort the time diffs so we can easily find the low, mid and high values\n",
    "        diffs.sort()\n",
    "        # Compute the Bowley number and Bowley density\n",
    "        tsLow = np.percentile(diffs, 25)\n",
    "        tsMid = np.percentile(diffs, 50)\n",
    "        tsHigh = np.percentile(diffs, 75)\n",
    "        tsBowleyNum = tsLow + tsHigh - 2*tsMid\n",
    "        tsBowleyDen = tsHigh - tsLow\n",
    "        # tsSkew should equal zero if the denominator equals zero\n",
    "        # bowley skew is unreliable if Q2 = Q1 or Q2 = Q3\n",
    "        if tsBowleyDen != 0 and tsMid != tsLow and tsMid != tsHigh:\n",
    "            tsSkew = float(tsBowleyNum) / float(tsBowleyDen)\n",
    "        else:\n",
    "            tsSkew = 0\n",
    "        # perfect beacons should have very low dispersion around the\n",
    "        # median of their delta times\n",
    "        # Median Absolute Deviation About the Median\n",
    "        # is used to check dispersion\n",
    "        deviations = []\n",
    "        for diff in diffs:\n",
    "            deviations.append(abs(diff - tsMid))\n",
    "        deviations.sort() \n",
    "        tsMadm = np.percentile(deviations, 50) # tsMadm is Median absolute deviation about the median\n",
    "        # compute range of intervals for human analysis\n",
    "        tsIntervalRange = diffs[-1] - diffs[0]\n",
    "        \n",
    "        # more skewed distributions receive a lower score\n",
    "        # less skewed distributions receive a higher score\n",
    "        tsSkewScore = 1.0 - abs(tsSkew) #smush tsSkew\n",
    "\n",
    "        # lower dispersion is better, cutoff dispersion scores at 30 seconds\n",
    "        tsMadmScore = 1.0 - float(tsMadm)/30.0\n",
    "        if tsMadmScore < 0: \n",
    "            tsMadmScore = 0\n",
    "        \n",
    "        # connection count scoring: max score of 1.0 means 1+ conn every (mediam beacon interval) seconds or less\n",
    "        tsTimespanDiv = float((current_time - timing_list[0]).total_seconds()) / tsMid\n",
    "        tsConnCountScore = float(len(timing_list)) / tsTimespanDiv\n",
    "        if tsConnCountScore > 1.0:\n",
    "            tsConnCountScore = 1.0 \n",
    "            \n",
    "        # Compute combined score average\n",
    "        tsSum = tsSkewScore + tsMadmScore + tsConnCountScore\n",
    "        tsScore = math.ceil((tsSum/3.0)*1000) / 1000\n",
    "        \n",
    "        stats = {'score':tsScore, \n",
    "                 'ip_pair': tuple(ip_pair),\n",
    "                 'skew_score': tsSkewScore,\n",
    "                 'madm_score': tsMadmScore,\n",
    "                 'count_score': tsConnCountScore,\n",
    "                 'beacon_interval_high':np.percentile(diffs, 90), \n",
    "                 #'beacon_interval_mid':tsMid,\n",
    "                 'beacon_interval_low':np.percentile(diffs, 10), \n",
    "                 'earliest_conn':timing_list[0], \n",
    "                 'latest_conn':timing_list[-1], \n",
    "                 'num_conns': len(diffs)}\n",
    "        beacon_scores.append(stats)\n",
    "        ip_pair_score_list.append(\"%.2f:%s-%s\" % (tsScore, ip_pair[0], ip_pair[1]))\n",
    "        \n",
    "def createIPPairScoreListFromDataFrame(df):\n",
    "    score_list = []\n",
    "    for index, row in df.iterrows():\n",
    "        score_list.append(\"%.2f:%s-%s\" % (row[\"score\"], row[\"ip_pair\"][0], row[\"ip_pair\"][1]))\n",
    "    score_list.sort(reverse=True)\n",
    "    return score_list\n",
    "                                              \n",
    "ip_pair_score_list.sort(reverse=True)\n",
    "beacons_df = pd.DataFrame(beacon_scores)\n",
    "beacons_df['earliest_conn'] = pd.to_datetime(beacons_df[\"earliest_conn\"].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "beacons_df['latest_conn'] = pd.to_datetime(beacons_df[\"latest_conn\"].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "print(\"Potential beacons analyzed and stored in DataFrame beacons_df\")\n",
    "print(\"You may now proceed to run the next cells to view the results\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Top Potential Beacons by Overall Score\n",
    "Now it is time to review the results. We can look at the data in different ways, but the most useful way to sort is by the beacon scores computed above, with the highest scores on top.\n",
    "Run the code block below to view the dataframe sorted by score. Adjust the number in the dataframe head() function below to get the top 10, top 25 or whatever number you want to review."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "beacons_df.set_index(\"score\")\n",
    "beacons_df.sort_values(\"score\", ascending=False, inplace=True)\n",
    "\n",
    "num_conns_slider = widgets.IntSlider(min=5, max=200, step=5, value=10)\n",
    "num_results_slider = widgets.IntSlider(min=1, max=50, step=1, value=10)\n",
    " \n",
    "def filter_beacons(min_conns=10,num_results=10):\n",
    "    filtered = beacons_df[beacons_df['num_conns']>=min_conns].head(num_results)\n",
    "    filtered.set_index(\"score\")\n",
    "    global ip_pair_score_list\n",
    "    ip_pair_score_list = createIPPairScoreListFromDataFrame(filtered)\n",
    "    return filtered\n",
    " \n",
    "widgets.interact_manual(filter_beacons,min_conns=num_conns_slider,num_results=num_results_slider)\n",
    "\n",
    "# If you don't want the interactive sliders, comment out above and replace with two lines below:\n",
    "#beacons_df.sort_values(\"score\", ascending=False, inplace=True)\n",
    "#beacons_df.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's get Visual ####\n",
    ">\"Those who do not learn from histograms are destined to keep repeating their analyses\"\n",
    "\n",
    "Select an IP pair from the list below and click the \"Run interact\" button to generate a histogram of the connection times. The graph shows how many connections were detected in each 10 minute bucket. So, if connections happened once a minute, you should see vertical bars closely spaced, all at the 10 count level. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "current_ip_pair = None\n",
    "def viewplot(ip_info):\n",
    "    # \"linking function with output\"\n",
    "    score, ip_pair = ip_info.split(\":\", 1)\n",
    "    global current_ip_pair\n",
    "    current_ip_pair = tuple(ip_pair.split(\"-\", 1))\n",
    "    plot = pd.DataFrame(connection_timing[current_ip_pair],  columns=['time'])\n",
    "    # Setting the date as the index since the Grouper works on Index, \n",
    "    # the date column is not dropped to be able to count\n",
    "    plot.set_index('time', drop=False, inplace=True)\n",
    "    # Get the histogram\n",
    "    mid_time = earliest_time + (current_time-earliest_time)/2\n",
    "    plot.groupby(pd.Grouper(freq='10Min')).count().plot(kind='bar', \n",
    "                                                        xticks=(),\n",
    "                                                       yticks=range(0,11), \n",
    "                                                       grid=True, legend=False,\n",
    "                                                       title=str(current_ip_pair),\n",
    "                                                       color='red',\n",
    "                                                       xlim=(earliest_time, current_time))\n",
    "    return plot\n",
    "\n",
    "# Create an interactive selector\n",
    "ip_select =  widgets.Select(options=ip_pair_score_list[:num_results_slider.value+1], \n",
    "                            width='400px', height='800px')\n",
    "widgets.interact_manual(viewplot, ip_info=ip_select)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Investigate Suspicious Processes ####\n",
    "Now that you have suspicious connections to check into, we need to see which processes were responsible for those network connections.\n",
    "\n",
    "Run the code block below to view the results for the IP pair that you selected for the histogram view above.\n",
    "\n",
    "Note that these results are for processes from the last three days that had communications between the two IP addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip_process_query = process_query % (current_ip_pair[0], current_ip_pair[1])\n",
    "df_matching_processes = qry_prov.exec_query(query=ip_process_query)\n",
    "\n",
    "df_matching_processes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to grab the KQL query to investigate further in Sentinel, run the code block below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(ip_process_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Whois that IP anyway? ####\n",
    "If you need more information about that Remote IP address in the results above, run the block below to check it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(current_ip_pair[1])\n",
    "remote_ip_result = IPWhois(current_ip_pair[1])\n",
    "remote_ip_json = remote_ip_result.lookup_rdap()\n",
    "print(json.dumps(remote_ip_json, indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check MSTIC Threat Intelligence for IP ###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(current_ip_pair[1])\n",
    "# query the remote IP in the MSTIC threat intelligence feed\n",
    "df_ip_hits = qry_prov.ThreatIntelligence.list_indicators_by_ip(observables=list(current_ip_pair[1]))\n",
    "# display the final result\n",
    "df_ip_hits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bonus: Sysmon EventID 3 fields parsed for KQL\n",
    "SysmonNetworkEventQuery = '''Event\n",
    "| where Source == \"Microsoft-Windows-Sysmon\"\n",
    "| extend RenderedDescription = tostring(split(RenderedDescription, \":\")[0])\n",
    "| where EventID == 3\n",
    "| extend EvData = parse_xml(EventData)\n",
    "| extend EventDetail = EvData.DataItem.EventData.Data\n",
    "| project-away EventData, EvData\n",
    "| extend RuleName = tostring(EventDetail.[0].[\"#text\"])\n",
    "| extend EventTime = EventDetail.[1].[\"#text\"]\n",
    "| extend ProcessGuid = EventDetail.[2].[\"#text\"]\n",
    "| extend ProcessID = EventDetail.[3].[\"#text\"]\n",
    "| extend ProcessPath = tostring(EventDetail.[4].[\"#text\"])\n",
    "| extend Username = tostring(EventDetail.[5].[\"#text\"])\n",
    "| extend NetworkProtocol = tostring(EventDetail.[6].[\"#text\"])\n",
    "| extend NetworkConnectionInitiated = EventDetail.[7].[\"#text\"]\n",
    "| extend SrcIsIpv6 = EventDetail.[8].[\"#text\"]\n",
    "| extend LocalIP = tostring(EventDetail.[9].[\"#text\"])\n",
    "| extend LocalHostName = tostring(EventDetail.[10].[\"#text\"])\n",
    "| extend LocalPort = EventDetail.[11].[\"#text\"]\n",
    "| extend LocalPortName = tostring(EventDetail.[12].[\"#text\"])\n",
    "| extend RemoteIsIpv6 = EventDetail.[13].[\"#text\"] \n",
    "| extend RemoteIP = tostring(EventDetail.[14].[\"#text\"])\n",
    "| extend RemoteHostName = tostring(EventDetail.[15].[\"#text\"])\n",
    "| extend RemotePort = EventDetail.[16].[\"#text\"]\n",
    "| extend RemotePortName = tostring(EventDetail.[17].[\"#text\"])'''"
   ]
  }
 ],
 "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.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}