scenario-notebooks/Guided Investigation - MDE Webshell Alerts.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<h1>Guided Webshell Investigation - MDE Microsoft Sentinel Enrichments</h1>\n",
        "<p><b>Notebook Version:</b> 1.0<br>\n",
        "<b>Python Version:</b> Python 3.6<br>\n",
        "<b>Data Sources Required:</b> MDE SecurityAlert, W3CIIS Log (or similar web logging)</p>\n",
        "\n",
        "<p>This notebook investigates Microsoft Defender for Endpoint (MDE) webshell alerts. The notebook will guide you through steps to collect MDE alerts for webshell activity and link them to server access logs to identify potential attackers.</p>\n",
        "\n",
        "<p><b>Configuration Required!</b></p>\n",
        "<p>This Notebook presumes you have Microsoft Sentinel Workspace settings configured in a config file. If you do not have this in place please <a href =\"https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html#\">read the docs</a> and <a href=\"https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb\">use this notebook</a> to test.</p>\n",
        "<h2>How to use:</h2>\n",
        "<p>This notebook provides a step-by-step investigation to understand MDE webshell alerts on your server. While our example uses IIS logging this notebook can be converted to support any web log type.</p>\n",
        "<p>After congiuration you can investigate two scenarios, a webshell file alert or a webshell command execution alert. For each of these we will need to retrieve different data, the notebook contains branching execution at Step 3 to enable this.</p>\n",
        "<p>Below you'll find a more detailed description of the two types of investigation</p>\n",
        "<ul>\n",
        "    <h4><u>Shell File Alert</u></h4>\n",
        "    <p>This alert type will fire when a file that is suspected to be a webshell appears on disk. For this investigation we will start with a known filename that is a suspected shell (e.g. Setconfigure.aspx) and we will try to understand how this webshell was placed on the server.</p>\n",
        "    <h4><u>Shell Command Execution Alert</u></h4>\n",
        "    <p>This alert type will fire when a command is executed on your web server that is suspicious. For this investigation we start with the command line that was executed and the time window that execution took place.</p>\n",
        "</ul>\n",
        "<p><b>For both of the above alert types this notebook will allow you to find the following information:</b></p>\n",
        "<ul>\n",
        "    <li>The attacker IP</li>\n",
        "    <li>The attacker User Agent</li>\n",
        "    <li>The website name the attacker interacted with</li>\n",
        "    <li>The location of the shell on your server</li>\n",
        "</ul>\n",
        "<p>Once we have that information this notebook will allow you to investigate the attacker IP, User Agent or both to discover:\n",
        "<ul>\n",
        "    <li>The files the attacker accessed prior to the installation of the shell</li>\n",
        "    <li>The first time the attacker accessed your server</li>\n",
        "</ul>\n",
        "\n",
        "<hr>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<h1>Notebook Initialization</h1>\n",
        "<p>This cell:\n",
        "\n",
        "<ul>\n",
        "<li>Checks for the correct Python version</li>\n",
        "<li>Checks versions and optionally installs required packages</li>\n",
        "<li>Imports the required packages into the notebook</li>\n",
        "<li>Sets a number of configuration options.</li>\n",
        "</ul>\n",
        "This should complete without errors. If you encounter errors or warnings look at the following two notebooks:</p>\n",
        "\n",
        "<ul>\n",
        "<li><a href=\"https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/TroubleShootingNotebooks.ipynb\">TroubleShootingNotebooks</a></li>\n",
        "<li><a href=\"https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb\">ConfiguringNotebookEnvironment</a></li>\n",
        "</ul>\n",
        "You may also need to do some additional configuration to successfully use functions such as Threat Intelligence service lookup and Geo IP lookup. See the <a href=\"https://app.reviewnb.com/Azure/Azure-Sentinel-Notebooks/commit/0ba12819a4bf3d9e1c167ca3b1a9738d6df3be35/#Configuration\">Configuration</a> section at the end of the notebook and the <a href=\"https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb\">ConfiguringNotebookEnvironment<a>."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T01:53:14.901515Z",
          "start_time": "2020-09-09T01:53:07.553744Z"
        },
        "gather": {
          "logged": 1617840098683
        },
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "from pathlib import Path\n",
        "from IPython.display import display, HTML\n",
        "import ipywidgets as widgets\n",
        "\n",
        "REQ_PYTHON_VER = \"3.10\"\n",
        "REQ_MSTICPY_VER = \"2.12.0\"\n",
        "\n",
        "display(HTML(\"<h3>Starting Notebook setup...</h3>\"))\n",
        "\n",
        "# If not using Azure Notebooks, install msticpy with\n",
        "# %pip install msticpy\n",
        "import msticpy as mp\n",
        "mp.init_notebook(\n",
        "    namespace=globals()\n",
        ");"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T01:03:53.479037Z",
          "start_time": "2020-09-09T01:03:40.001Z"
        },
        "gather": {
          "logged": 1617840102896
        }
      },
      "outputs": [],
      "source": [
        "import ipywidgets as widgets\n",
        "from ipywidgets import HBox\n",
        "\n",
        "try:\n",
        "    pick_time_range = widgets.Dropdown(\n",
        "        options=['30d', '60d', '90d'],\n",
        "        decription=\"Time range\",\n",
        "        disabled=False,\n",
        "     )\n",
        "\n",
        "    workspaces_available = mp.WorkspaceConfig().list_workspaces()\n",
        "    if not workspaces_available:\n",
        "        def_config = mp.WorkspaceConfig()\n",
        "        workspaces_available = {\n",
        "            def_config[\"workspace_id\"]: def_config[\"workspace_name\"]\n",
        "        }\n",
        "\n",
        "    target_workspace = widgets.Dropdown(\n",
        "        options=workspaces_available.keys(),\n",
        "        decription=\"Workspace\")\n",
        "\n",
        "    display(HBox([pick_time_range, target_workspace]))\n",
        "except RuntimeError:\n",
        "    md(\"\"\"You do not have any Workspaces configured in your config files.\n",
        "       Please run the https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb\n",
        "       to setup these files before proceeding\"\"\" ,'bold')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T00:42:44.560039Z",
          "start_time": "2020-09-09T00:42:44.551141Z"
        },
        "gather": {
          "logged": 1617840261679
        }
      },
      "outputs": [],
      "source": [
        "# This time period is used to determine how far back the analytic looks, e.g. 1h, 3d, 7d, 1w\n",
        "# If you expereince timeout errors or the notebook is returning too much data, try lowering this\n",
        "time_range = pick_time_range.value\n",
        "workspace_name = target_workspace.value"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T00:44:24.896063Z",
          "start_time": "2020-09-09T00:42:46.430442Z"
        },
        "gather": {
          "logged": 1617840280001
        }
      },
      "outputs": [],
      "source": [
        "# Collect Microsoft Sentinel Workspace Details from our config file and use them to connect\n",
        "import warnings\n",
        "try:\n",
        "    # Update to WorkspaceConfig(workspace=\"WORKSPACE_NAME\") to get alerts from a Workspace other than your default one.\n",
        "    # Run WorkspaceConfig().list_workspaces() to see a list of configured workspaces\n",
        "    ws_config = mp.WorkspaceConfig(workspace=workspace_name)\n",
        "    ws_id = ws_config['workspace_id']\n",
        "    ten_id = ws_config['tenant_id']\n",
        "    md(\"Workspace details collected from config file\")\n",
        "    with warnings.catch_warnings():\n",
        "        warnings.simplefilter(action=\"ignore\")\n",
        "        qry_prov = mp.QueryProvider(data_environment='LogAnalytics')\n",
        "    qry_prov.connect(connection_str=ws_config.code_connect_str)\n",
        "except RuntimeError:\n",
        "    md(\"\"\"You do not have any Workspaces configured in your config files.\n",
        "       Please run the https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb\n",
        "       to setup these files before proceeding\"\"\" ,'bold')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T00:44:54.977443Z",
          "start_time": "2020-09-09T00:44:41.927908Z"
        },
        "gather": {
          "logged": 1617840287011
        }
      },
      "outputs": [],
      "source": [
        "# This cell will collect an alert summary to help you decide which investigation to launch\n",
        "alert_summary_query = f'''\n",
        "let timeRange = {time_range};\n",
        "SecurityAlert\n",
        "| where ProviderName =~ \"MDATP\"\n",
        "| where DisplayName has_any(\"Possible IIS web shell\", \"Possible IIS compromise\", \"Suspicious processes indicative of a web shell\", \"A suspicious web script was created\", \"Possible web shell installation\")\n",
        "| extend AlertType = iff(DisplayName has_any(\"Possible IIS web shell\", \"Possible IIS compromise\", \"Suspicious processes indicative of a web shell\", \"A suspicious web script was created\"), \"Webshell Command Alerts\", \"Webshell File Alerts\")\n",
        "| summarize count(AlertType) by AlertType\n",
        "| project AlertType, NumberOfAlerts=count_AlertType\n",
        "'''\n",
        "display(HTML('<h2>Alert Summary</h2><p>The following alert types have been found on your server:'))\n",
        "alertout = qry_prov.exec_query(alert_summary_query)\n",
        "display(alertout)\n",
        "\n",
        "if (not isinstance(alertout, pd.DataFrame)) or alertout.empty:\n",
        "    print(\"No MDE Webshell alerts found.\")\n",
        "    print(\"If you think that this is not correct, please check the time range you are using.\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<div style=\"border-left: 6px solid #ccc; border-left-width: 6px; border-left-style: solid; padding: 0.01em 16px; border-color: # #0080ff; background-color:#e6f2ff;\">\n",
        "<h1>Before you continue!</h1>\n",
        "<p> Now it's time to select which type of investigation you would like to try. Above we have provided a summary of the high-level alert types present on your server, if the above table is blank no alerts were found.</p>\n",
        "    <p><b><i>If the table is empty, this notebook has no alerts to work with and will produce errors in subsequent cells.</i></b></p>\n",
        "<p>If you have alerts you have a couple of different options.<br> You can <b>click the links</b> to jump to the start of the investigation. </p>\n",
        "<p><b><a href=\"#Step-3:-Begin-File-Investigation\">Shell file alert Investigation</a>:</b> If you would like to conduct an investigation into an ASPX file that has been detected by Microsoft Defender ATP please run the code block beneath \"Begin File Investigation\"<p>\n",
        "<p><b><a href=\"#Step-3:-Begin-Command-Investigation\">Shell command alert Investigation</a>:</b> If you would like to conduct an investigation into suspicious command execution on your web server please run the code block below \"Begin Command Investigation\"<p>\n",
        "</div>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<h2>Step 3: Begin File Investigation</h2>\n",
        "<p>We can now begin our investigation into a webshell file that has been placed on a system in your network. We'll start by collecting relevant events from MDE.<p>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "ExecuteTime": {
          "end_time": "2020-09-09T00:58:17.298672Z",
          "start_time": "2020-09-09T00:58:17.024681Z"
        }
      },
      "outputs": [],
      "source": [
        "# First the notebook collects alerts from MDE with the following query\n",
        "display(HTML('<h3>Collecting relevant alerts from MDE</h3>'))\n",
        "\n",
        "mde_events_query = f'''\n",
        "let timeRange = {time_range};\n",
        "let scriptExtensions = dynamic([\".php\", \".jsp\", \".js\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "SecurityAlert\n",
        "| where TimeGenerated > ago(timeRange)\n",
        "| where ProviderName == \"MDATP\"\n",
        "| where DisplayName =~ \"Possible web shell installation\"\n",
        "| extend alertData = parse_json(Entities)\n",
        "| mvexpand alertData\n",
        "| where alertData.Type == \"file\"\n",
        "| where alertData.Name has_any(scriptExtensions)\n",
        "| extend filename = alertData.Name, directory = alertData.Directory\n",
        "| project TimeGenerated, filename, directory\n",
        "'''\n",
        "\n",
        "aspx_data = qry_prov.exec_query(mde_events_query)\n",
        "if (not isinstance(aspx_data, pd.DataFrame)) or aspx_data.empty:\n",
        "    print(\"No MDE Webshell alerts found. Please try a different time range.\")\n",
        "    raise ValueError(\"No MDE Webshell alerts found. Cannot continue\")\n",
        "\n",
        "shells = aspx_data['filename']\n",
        "\n",
        "# Everything below is presentational\n",
        "pick_shell = widgets.Dropdown(\n",
        "    options=shells,\n",
        "    decription=\"Webshells\",\n",
        "    disabled=False,\n",
        " )\n",
        "\n",
        "if isinstance(aspx_data, pd.DataFrame) and not aspx_data.empty:\n",
        "    display(HTML('<p>Below you can see the filename, the directory it was found in, and the time it was found.</p><p>Please select a webshell to investigate before you continue:</p>'))\n",
        "    display(aspx_data)\n",
        "    display(pick_shell)\n",
        "    display(HTML('<hr>'))\n",
        "    display(HTML('<h1>Collect Enrichment Events</h1>'))\n",
        "    display(HTML('<p>Now we will enrich this webshell event with additional information before continuing to find the attacker.</p>'))\n",
        "else:\n",
        "    md_warn('No relevant alerts were found in your MDE logs, try expanding your timeframe in the config.')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# Now collect enrichments from the W3CIIS log table\n",
        "dfindex = pick_shell.index\n",
        "filename = aspx_data.loc[[dfindex]]['filename'].values[0]\n",
        "directory = aspx_data.loc[[dfindex]]['directory'].values[0]\n",
        "timegenerated = aspx_data.loc[[dfindex]]['TimeGenerated'].values[0]\n",
        "\n",
        "# Check the directory matches\n",
        "directory_split = directory.split(\"\\\\\")\n",
        "first_directory = directory_split[-1]\n",
        "\n",
        "# This query will collect  file accessed on the server within the same time window\n",
        "iis_query = f'''\n",
        "let scriptExtensions = dynamic([\".php\", \".jsp\", \".js\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "W3CIISLog\n",
        "| where TimeGenerated >= datetime(\"{timegenerated}\") - 10s\n",
        "| where TimeGenerated <= datetime(\"{timegenerated}\") + 10s\n",
        "| where csUriStem has_any(scriptExtensions)\n",
        "| extend splitUriStem = split(csUriStem, \"/\")\n",
        "| extend FileName = splitUriStem[-1] | extend firstDir = splitUriStem[-2]\n",
        "| where FileName == \"{filename}\" and firstDir == \"{first_directory}\"\n",
        "| summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated) by AttackerIP=cIP, AttackerUserAgent=csUserAgent, SiteName=sSiteName, ShellLocation=csUriStem\n",
        "| order by StartTime asc\n",
        "'''\n",
        "\n",
        "if isinstance(iis_data, pd.DataFrame) and not iis_data.empty:\n",
        "    iis_data = qry_prov.exec_query(iis_query)\n",
        "    display(HTML('<div style=\"border-left: 6px solid #ccc; border-left-width: 6px; border-left-style: solid; padding: 0.01em 16px; border-color:#00cc69; background-color:#e6fff3;\"><h3>Enrichment complete!<br> Please <a href=\"#Step-1:-Find-the-Attacker\">click here</a> to continue your investigation.</h3><br></div><hr>'))\n",
        "else:\n",
        "    if iis_data.empty:\n",
        "        md_warn('No events were found in W3CIISLog')\n",
        "    else:\n",
        "        md_warn('The query failed, it may have timed out')\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<h2>Step 3: Begin Command Investigation</h2>\n",
        "<p>To begin the investigation into a command that has been executed by a webshell on your network, we will begin by collecting MDE data.</p>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "command_investigation_query = f'''\n",
        "let timeRange = {time_range};\n",
        "let alerts = SecurityAlert\n",
        "| where TimeGenerated > ago(timeRange)\n",
        "| extend alertData = parse_json(Entities), recordGuid = new_guid();\n",
        "let shellAlerts = alerts\n",
        "| where ProviderName =~ \"MDATP\"\n",
        "| mvexpand alertData\n",
        "| where alertData.Type == \"file\" and alertData.Name == \"w3wp.exe\"\n",
        "| distinct SystemAlertId\n",
        "| join kind=inner (alerts) on SystemAlertId;\n",
        "let alldata = shellAlerts\n",
        "| mvexpand alertData\n",
        "| extend Type = alertData.Type;\n",
        "let filedata = alldata\n",
        "| extend id = tostring(alertData.$id)\n",
        "| extend ImageName = alertData.Name\n",
        "| where Type == \"file\" and ImageName != \"w3wp.exe\"\n",
        "| extend imagefileref = id;\n",
        "let commanddata = alldata\n",
        "| extend CommandLine = tostring(alertData.CommandLine)\n",
        "| extend creationtime = tostring(alertData.CreationTimeUtc)\n",
        "| where Type =~ \"process\"\n",
        "| where isnotempty(CommandLine)\n",
        "| extend imagefileref = tostring(alertData.ImageFile.$ref);\n",
        "let hostdata = alldata\n",
        "| where Type =~ \"host\"\n",
        "| project HostName = tostring(alertData.HostName), DnsDomain = tostring(alertData.DnsDomain), SystemAlertId\n",
        "| distinct HostName, DnsDomain, SystemAlertId;\n",
        "filedata\n",
        "| join kind=inner (\n",
        "commanddata\n",
        ") on imagefileref\n",
        "| join kind=inner (hostdata) on SystemAlertId\n",
        "| project DisplayName, recordGuid, TimeGenerated, ImageName, CommandLine, HostName, DnsDomain\n",
        "'''\n",
        "\n",
        "cmd_data = qry_prov.exec_query(command_investigation_query)\n",
        "\n",
        "if isinstance(cmd_data, pd.DataFrame) and not cmd_data.empty:\n",
        "    display(HTML('''<h2>Step 3.1: Select a command to investigate</h2>\n",
        "    <p>Below you will find the suspicious commands that were executed. Matching GUIDs indicate that the events were linked and likely executed within seconds of each other,\n",
        "    for the purpose of the investigation you can select either as the default time windows are wide enough to encapsulate both events. There's a full breakdown of the fields below.</p>\n",
        "    <ul>\n",
        "    <li>DisplayName: The MDE alert display name</li>\n",
        "    <li>recordGuid: A GUID used to track previoulsy linked events</li>\n",
        "    <li>TimeGenerated: The time the log entry was made</li>\n",
        "    <li>ImageName: The executing process image name</li>\n",
        "    <li>CommandLine: The command line that was executed</li>\n",
        "    <li>HostName: The host name of the impacted machine</li>\n",
        "    <li>DnsDomain: The domain of the impacted machine</li>\n",
        "    </ul>\n",
        "    <p>Note: The GUID generated here will change with each execution and is used only by the notebook.</p>'''))\n",
        "\n",
        "\n",
        "    command = cmd_data['recordGuid']\n",
        "\n",
        "    pick_cmd = widgets.Dropdown(\n",
        "        options=command,\n",
        "        decription=\"Commands\",\n",
        "        disabled=False,\n",
        "     )\n",
        "\n",
        "    display(HTML('<h3>Select the GUID associated with the command you wish to investigate.</h3>'))\n",
        "    display(pick_cmd)\n",
        "    display(HTML('<hr><h2>Step 3.2: Execute to Collect Events</h2><p>Please select an access threshold, by default the script will look for files on the server that have been accessed by fewer than 3 IP addresses</p>'))\n",
        "\n",
        "    access_threshold = widgets.IntSlider(\n",
        "        value=3,\n",
        "        min=0,\n",
        "        max=15,\n",
        "        step=1,\n",
        "        decription=\"Access Threshold\",\n",
        "        disabled=False,\n",
        "        orientation='horizontal',\n",
        "        readout=True,\n",
        "        readout_format='d'\n",
        "     )\n",
        "    display(access_threshold)\n",
        "else:\n",
        "    if iis_data.empty:\n",
        "        md_warn('No events were found in SecurityAlert. Continuing will result in errors.')\n",
        "    else:\n",
        "        md_warn('The query failed, it may have timed out. Continuing will result in errors.')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dfindex = pick_cmd.index\n",
        "imagename = cmd_data.loc[[dfindex]]['ImageName'].values[0]\n",
        "commandline = cmd_data.loc[[dfindex]]['CommandLine'].values[0]\n",
        "creationtime = cmd_data.loc[[dfindex]]['TimeGenerated'].values[0]\n",
        "\n",
        "# Retrieves access to script files on the web server using logs stored in W3CIIS.\n",
        "# Checks for how many unique client IP addresses access the file, uses access_threshold\n",
        "script_data_query = f'''\n",
        "let scriptExtensions = dynamic([\".php\", \".jsp\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "let alldata = W3CIISLog\n",
        "| where TimeGenerated >= datetime(\"{creationtime}\") - 30s\n",
        "| where TimeGenerated <= datetime(\"{creationtime}\") + 30s\n",
        "| where csUriStem has_any(scriptExtensions)\n",
        "| extend splitUriStem = split(csUriStem, \"/\")\n",
        "| extend FileName = splitUriStem[-1] | extend firstDir = splitUriStem[-2]\n",
        "| summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated) by AttackerIP=cIP, AttackerUserAgent=csUserAgent, csUriStem, filename=tostring(FileName), tostring(firstDir)\n",
        "| order by StartTime asc;\n",
        "let fileprev = W3CIISLog\n",
        "| summarize accessCount=dcount(cIP) by csUriStem;\n",
        "alldata\n",
        "| join (\n",
        "    fileprev\n",
        ") on csUriStem\n",
        "| extend ShellLocation = csUriStem\n",
        "| project-away csUriStem, csUriStem1\n",
        "| where accessCount <= {access_threshold}\n",
        "'''\n",
        "aspx_data = qry_prov.exec_query(script_data_query)\n",
        "\n",
        "if isinstance(aspx_data, pd.DataFrame) and not aspx_data.empty:\n",
        "    display(HTML('<h2>Step 3.3: File to investigate</h2><p>The files in the drop down below were accessed on the web server (and are therefore in W3CIIS Log) within 30 seconds of the command executing.</p><p>By default the notebook will only show files that have been accessed by a single client IP or UA.</p>'))\n",
        "\n",
        "\n",
        "    shells = aspx_data['ShellLocation']\n",
        "\n",
        "    pick_shell = widgets.Dropdown(\n",
        "        options=shells,\n",
        "        decription=\"Webshells\",\n",
        "        disabled=False,\n",
        "     )\n",
        "\n",
        "    aspx_data_display = aspx_data\n",
        "    aspx_data_display = aspx_data_display.drop(['AttackerIP', 'AttackerUserAgent', 'firstDir', 'EndTime'], axis=1)\n",
        "    aspx_data_display.rename(columns={'filename':'ShellName', 'StartTime':'AccessTime'}, inplace=True)\n",
        "\n",
        "\n",
        "    display(HTML('Please select which file you would like to investigate:'))\n",
        "    #display(aspx_data)\n",
        "    display(pick_shell)\n",
        "    display(HTML('<hr><h2>Step 3.4: Enrich</h2>'))\n",
        "else:\n",
        "    if aspx_data.empty:\n",
        "        md_warn('No events were found in W3CIISLog. Continuing will result in errors.')\n",
        "    else:\n",
        "        md_warn('The query failed, it may have timed out. Continuing will result in errors.')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "if isinstance(aspx_data, pd.DataFrame) and not aspx_data.empty:\n",
        "    dfindex = pick_shell.index\n",
        "    filename = aspx_data.loc[[dfindex]]['filename'].values[0]\n",
        "    timegenerated = aspx_data.loc[[dfindex]]['StartTime'].values[0]\n",
        "\n",
        "    #Check the directory matches\n",
        "    first_directory = aspx_data.loc[[dfindex]]['firstDir'].values[0]\n",
        "\n",
        "    iis_query = f'''\n",
        "    let scriptExtensions = dynamic([\".php\", \".jsp\", \".js\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "    W3CIISLog\n",
        "    | where TimeGenerated >= datetime(\"{timegenerated}\") - 30s\n",
        "    | where TimeGenerated <= datetime(\"{timegenerated}\") + 30s\n",
        "    | where csUriStem has_any(scriptExtensions)\n",
        "    | extend splitUriStem = split(csUriStem, \"/\")\n",
        "    | extend FileName = splitUriStem[-1] | extend firstDir = splitUriStem[-2]\n",
        "    | where FileName == \"{filename}\" and firstDir == \"{first_directory}\"\n",
        "    | summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated) by AttackerIP=cIP, AttackerUserAgent=csUserAgent, SiteName=sSiteName, ShellLocation=csUriStem\n",
        "    | order by StartTime asc\n",
        "    '''\n",
        "\n",
        "    iis_data = qry_prov.exec_query(iis_query)\n",
        "else:\n",
        "    md_warn('There is no data in an object that should have data. A previous step has likely failed, we cannot continue.')\n",
        "\n",
        "if isinstance(iis_data, pd.DataFrame) and not iis_data.empty:\n",
        "    display(HTML('<div style=\"border-left: 6px solid #ccc; border-left-width: 6px; border-left-style: solid; padding: 0.01em 16px; border-color:#00cc69; background-color:#e6fff3;\"><h3>Enrichment complete!<br> Please <a href=\"#Step-4:-Find-the-Attacker\">click here</a> to continue your investigation.</h3><br></div><hr>'))\n",
        "else:\n",
        "    if iis_data.empty:\n",
        "        md_warn('No events were found in W3CIISLog. Continuing will result in errors.')\n",
        "    else:\n",
        "        md_warn('The query failed, it may have timed out. Continuing will result in errors.')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<h2>Step 4: Find the Attacker</h2>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "attackerip = iis_data['AttackerIP']\n",
        "attackerua = iis_data['AttackerUserAgent']\n",
        "\n",
        "pick_ip = widgets.Dropdown(\n",
        "    options=attackerip,\n",
        "    decription=\"IP Addresses\",\n",
        "    disabled=False,\n",
        " )\n",
        "pick_ua = widgets.Dropdown(\n",
        "    options=attackerua,\n",
        "    decription=\"User Agents\",\n",
        "    disabled=False,\n",
        " )\n",
        "pick_window = widgets.Dropdown(\n",
        "    options=['30m','1h','5h','7h', '1d', '3d','7d'],\n",
        "    decription=\"Window\",\n",
        "    disabled=False,\n",
        " )\n",
        "pick_investigation = widgets.Dropdown(\n",
        "    options=['Investigate Both', 'Investigate IP','Investigate Useragent'],\n",
        "    decription=\"What should we investigatew?\",\n",
        "    disabled=False,\n",
        " )\n",
        "\n",
        "display(HTML('<h2>Candidate Attacker IP Addresses</h2>'))\n",
        "md('The following attacker IP addresses accessed the webshell during the alert window, continue to Step 5 to choose which to investigate.')\n",
        "display(iis_data)\n",
        "\n",
        "display(HTML('<hr><br><h2>Step 5: Select Investigation Parameters</h2>'))\n",
        "display(HTML('<h3>Attacker To Investigate</h3><p>Now it is time to hone in on our attacker. If you have multiple attacker indicators you can repeat from this step.</p><p>Select parameters to investigate, the default selection is the earliest access within the alert window:</p>'))\n",
        "display(HBox([pick_ip, pick_ua, pick_investigation]))\n",
        "widgets.jslink((pick_ip, 'index'), (pick_ua, 'index'))\n",
        "\n",
        "display(HTML('<h3>Previous file access window</h3><p>To determine what files were accessed immediately before the shell, please pick the window we\\'ll use to look back:</p>'))\n",
        "display(pick_window)\n",
        "\n",
        "display(HTML('<hr><h2>Step 6: Collect Attacker Enrichments</h2><p>Finally execute the below cell to collect additional details about the attacker.</p>'))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "queryWindow = pick_window.value # Lookback window\n",
        "investigation_param = pick_investigation.index # 0 = both, 1 = ip, 2 = ua\n",
        "dfindex = pick_ip.index # contains dataframe index (int)\n",
        "\n",
        "attackerip = str(pick_ip.value)\n",
        "attackerua = iis_data.loc[[dfindex]]['AttackerUserAgent'].values[0]\n",
        "attackertime = iis_data.loc[[dfindex]]['StartTime'].values[0]\n",
        "sitename = iis_data.loc[[dfindex]]['SiteName'].values[0]\n",
        "shell_location = iis_data.loc[[dfindex]]['ShellLocation'].values[0]\n",
        "\n",
        "access_data = ['','']\n",
        "first_server_access_data = ['','']\n",
        "\n",
        "def iis_access_ip():\n",
        "    iis_access_ip = f'''\n",
        "    let scriptExtensions = dynamic([\".php\", \".jsp\", \".js\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "    W3CIISLog\n",
        "    | where TimeGenerated >= datetime(\"{attackertime}\") - {queryWindow}\n",
        "    | where TimeGenerated <= datetime(\"{attackertime}\")\n",
        "    | where sSiteName == \"{sitename}\"\n",
        "    | where cIP == \"{attackerip}\"\n",
        "    | order by TimeGenerated desc\n",
        "    | project TimeAccessed=TimeGenerated, SiteName=sSiteName, ServerIP=sIP, FilesTouched=csUriStem, AttackerP=cIP\n",
        "    | where FilesTouched has_any(scriptExtensions)\n",
        "    | order by TimeAccessed asc\n",
        "    '''\n",
        "\n",
        "    #Find the first time the attacker accessed the webserver\n",
        "    first_server_access_ip = f'''\n",
        "    W3CIISLog\n",
        "    | where TimeGenerated > ago(30d)\n",
        "    | where sSiteName == \"{sitename}\"\n",
        "    | where cIP == \"{attackerip}\"\n",
        "    | order by TimeGenerated asc\n",
        "    | take 1\n",
        "    | project TimeAccessed=TimeGenerated, Site=sSiteName, FileAccessed=csUriStem\n",
        "    | order by TimeAccessed asc\n",
        "    '''\n",
        "\n",
        "    access_data = qry_prov.exec_query(iis_access_ip)\n",
        "\n",
        "    first_server_access_data = qry_prov.exec_query(first_server_access_ip)\n",
        "\n",
        "    return access_data, first_server_access_data\n",
        "\n",
        "def iis_access_ua():\n",
        "    iis_access_ua = f'''\n",
        "    let scriptExtensions = dynamic([\".php\", \".jsp\", \".js\", \".aspx\", \".asmx\", \".asax\", \".cfm\", \".shtml\"]);\n",
        "    W3CIISLog\n",
        "    | where TimeGenerated >= datetime(\"{attackertime}\") - {queryWindow}\n",
        "    | where TimeGenerated <= datetime(\"{attackertime}\")\n",
        "    | where sSiteName == \"{sitename}\"\n",
        "    | where csUserAgent == \"{attackerua}\"\n",
        "    | order by TimeGenerated desc\n",
        "    | project TimeAccessed=TimeGenerated, SiteName=sSiteName, ServerIP=sIP, FilesTouched=csUriStem, AttackerP=cIP, AttackerUserAgent=csUserAgent\n",
        "    | where FilesTouched has_any(scriptExtensions)\n",
        "    | order by TimeAccessed asc\n",
        "    '''\n",
        "\n",
        "    #Find the first time the attacker accessed the webserver\n",
        "    first_server_access_ua = f'''\n",
        "    W3CIISLog\n",
        "    | where TimeGenerated > ago(30d)\n",
        "    | where sSiteName == \"{sitename}\"\n",
        "    | where csUserAgent == \"{attackerua}\"\n",
        "    | order by TimeGenerated asc\n",
        "    | take 1\n",
        "    | project TimeAccessed=TimeGenerated, Site=sSiteName, FileAccessed=csUriStem\n",
        "    | order by TimeAccessed asc\n",
        "    '''\n",
        "\n",
        "    access_data =  qry_prov.exec_query(iis_access_ua)\n",
        "    first_server_access_data =  qry_prov.exec_query(first_server_access_ua)\n",
        "\n",
        "    return access_data, first_server_access_data\n",
        "\n",
        "first_shell_index = None\n",
        "if investigation_param == 1:\n",
        "    display(HTML('<p>Querying for attacker IP</p>'))\n",
        "    result = iis_access_ip()\n",
        "    access_data[0] = result[0]\n",
        "    first_server_access_data[0] = result[1]\n",
        "    first_shell_index = access_data[0][access_data[0].FilesTouched==shell_location].first_valid_index()\n",
        "\n",
        "elif investigation_param == 2:\n",
        "    display(HTML('<p>Querying for attacker UA</p>'))\n",
        "    result = iis_access_ua()\n",
        "    access_data[1] = result[0]\n",
        "    first_server_access_data[1] = result[1]\n",
        "    first_shell_index = access_data[1][access_data[1].FilesTouched==shell_location].first_valid_index()\n",
        "\n",
        "elif investigation_param == 0:\n",
        "    display(HTML('<p>Querying for attacker IP and UA</p>'))\n",
        "    result_ip = iis_access_ip()\n",
        "    result_ua = iis_access_ua()\n",
        "\n",
        "    access_data[0] = result_ip[0]\n",
        "    access_data[1] = result_ua[0]\n",
        "\n",
        "    first_server_access_data[0] = result_ip[1]\n",
        "    first_server_access_data[1] = result_ua[1]\n",
        "\n",
        "    first_shell_index = access_data[0][access_data[0].FilesTouched==shell_location].first_valid_index()\n",
        "    first_shell_index_ua = access_data[1][access_data[1].FilesTouched==shell_location].first_valid_index()\n",
        "\n",
        "display(HTML('<div style=\"border-left: 6px solid #ccc; border-left-width: 6px; border-left-style: solid; padding: 0.01em 16px; border-color:#00cc69; background-color:#e6fff3;\"><h3>Enrichment complete!</h3><p>Continue to generate your report</p><br></div><hr><h2>Step 7: Generate Report</h2>'))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "attackerua = attackerua.replace(\"+\", \" \")\n",
        "display(HTML(f'''\n",
        "<h2>  Attack Summary</h2>\n",
        "<div style=\"border-left: 6px solid #ccc; border-left-width: 6px; border-left-style: solid; padding: 0.01em 16px; border-color:#1aa3ff; background-color: #f2f2f2;\">\n",
        "<p></p>\n",
        "<p><b>Attacker IP: </b>{attackerip}</p>\n",
        "<p><b>Attacker user agent: </b>{attackerua}</p>\n",
        "<p><b>Webshell installed: </b>{shell_location}</p>\n",
        "<p><b>Victim site: </b>{sitename}</p>\n",
        "<br>\n",
        "</div>\n",
        "'''))\n",
        "\n",
        "look_back = 0\n",
        "# No results\n",
        "if first_shell_index is None:\n",
        "    first_shell_index = 0\n",
        "# Our default look back is 5 files, if there are not 5 files we take what we can get\n",
        "elif first_shell_index < 5:\n",
        "    look_back = first_shell_index\n",
        "else:\n",
        "    look_back = 5\n",
        "\n",
        "if investigation_param == 1:\n",
        "    display(HTML('<h2>File history</h2>'))\n",
        "    if first_shell_index > 0:\n",
        "        print('The files the attacker IP \\\"'+attackerip+'\\\" accessed prior to the webshell installation were:')\n",
        "        display(access_data[0][first_shell_index-look_back:first_shell_index+1])\n",
        "    else:\n",
        "        print(f'No files were access by the attacker prior to webshell install, try expaning the query window (currently:{queryWindow})')\n",
        "\n",
        "    display(HTML('<h2>Earliest access</h2><p>In the last 30 days the earliest known access to the server from the attacker IP was:</p>'))\n",
        "    display(first_server_access_data[0])\n",
        "\n",
        "elif investigation_param == 2:\n",
        "    display(HTML('<h2>File history</h2>'))\n",
        "    if first_shell_index > 0:\n",
        "        print('The files the attacker UA \\\"'+attackerua+'\\\" accessed prior to the webshell installation were:')\n",
        "        display(access_data[1][first_shell_index-look_back:first_shell_index+1])\n",
        "    else:\n",
        "        print(f'No files were access by the attacker prior to webshell install, try expaning the query window (currently:{queryWindow})')\n",
        "\n",
        "    display(HTML('<h2>Earliest access</h2><p>In the last 30 days the earliest known access to the server from the attacker UA was:</p>'))\n",
        "    display(first_server_access_data[1])\n",
        "\n",
        "elif investigation_param == 0:\n",
        "\n",
        "    look_back_ua = 0\n",
        "    if first_shell_index_ua is None:\n",
        "        first_shell_index_ua = 0\n",
        "    elif first_shell_index_ua < 5:\n",
        "        look_back_ua = first_shell_index_ua\n",
        "    else:\n",
        "        look_back_ua = 5\n",
        "\n",
        "    display(HTML('<h2>File history</h2>'))\n",
        "    if first_shell_index > 0 or first_shell_index_ua > 0:\n",
        "        print('The files the attacker IP \\\"'+attackerip+'\\\" accessed prior to the webshell installation were:')\n",
        "        display(access_data[0][first_shell_index-look_back:first_shell_index+1])\n",
        "        print('The files the attacker UA \\\"'+attackerua+'\\\" accessed prior to the webshell installation were:')\n",
        "        display(access_data[1][first_shell_index_ua-look_back_ua:first_shell_index_ua+1])\n",
        "    else:\n",
        "        print(f'No files were access by the attacker prior to webshell install, try expanding the query window (currently:{queryWindow})')\n",
        "\n",
        "    display(HTML('<h2>Earliest access</h2><p>In the last 30 days the earliest known access to the server from the attacker IP was:</p>'))\n",
        "    display(first_server_access_data[0])\n",
        "    display(HTML('<p>In the last 30 days the earliest known access to the server from the attacker UA was:</p>'))\n",
        "    display(first_server_access_data[1])"
      ]
    }
  ],
  "metadata": {
    "hide_input": false,
    "kernel_info": {
      "name": "python38-azureml"
    },
    "kernelspec": {
      "display_name": "Python 3.8 - AzureML",
      "language": "python",
      "name": "python38-azureml"
    },
    "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.1"
    },
    "nteract": {
      "version": "nteract-front-end@1.0.0"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": false,
      "sideBar": true,
      "skip_h1_title": false,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": true
    },
    "varInspector": {
      "cols": {
        "lenName": 16,
        "lenType": 16,
        "lenVar": 40
      },
      "kernels_config": {
        "python": {
          "delete_cmd_postfix": "",
          "delete_cmd_prefix": "del ",
          "library": "var_list.py",
          "varRefreshCmd": "print(var_dic_list())"
        },
        "r": {
          "delete_cmd_postfix": ") ",
          "delete_cmd_prefix": "rm(",
          "library": "var_list.r",
          "varRefreshCmd": "cat(var_dic_list()) "
        }
      },
      "types_to_exclude": [
        "module",
        "function",
        "builtin_function_or_method",
        "instance",
        "_Feature"
      ],
      "window_display": false
    },
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "state": {},
        "version_major": 2,
        "version_minor": 0
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}