Notebook Version: 1.0
\n",
"Python Version: Python 3.6
\n",
"Data Sources Required: MDATP SecurityAlert, W3CIIS Log (or similar web logging)
This notebook investigates Microsoft Defender Advanced Threat Protection (MDATP) webshell alerts. The notebook will guide you through steps to collect MDATP alerts for webshell activity and link them to server access logs to identify potential attackers.
\n", "\n", "Configuration Required!
\n", "This Notebook presumes you have Azure Sentinel Workspace settings configured in a config file. If you do not have this in place please read the docs and use this notebook to test.
\n", "This notebook provides a step-by-step investigation to understand MDATP webshell alerts on your server. While our example uses IIS logging this notebook can be converted to support any web log type.
\n", "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.
\n", "Below you'll find a more detailed description of the two types of investigation
\n", "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.
\n", "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.
\n", "For both of the above alert types this notebook will allow you to find the following information:
\n", "Once we have that information this notebook will allow you to investigate the attacker IP, User Agent or both to discover:\n", "
This cell:\n", "\n", "
The following alert types have been found on your server:'))\n", "alertout = qry_prov.exec_query(alert_summary_query)\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
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.
\n", "If you have alerts you have a couple of different options.
You can click the links to jump to the start of the investigation.
Shell file alert Investigation: 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\"
\n", "
Shell command alert Investigation: 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\"
\n", "
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 MDATP.
" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# First the notebook collects alerts from MDATP with the following query\n", "display(HTML('
Below you can see the filename, the directory it was found in, and the time it was found.
Please select a webshell to investigate before you continue:
'))\n", " display(aspx_data)\n", " display(pick_shell)\n", " display(HTML('Now we will enrich this webshell event with additional information before continuing to find the attacker.
'))\n", "else:\n", " md_warn('No relevant alerts were found in your MDATP 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('To begin the investigation into a command that has been executed by a webshell on your network, we will begin by collecting MDATP data.
" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": false }, "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('''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.
\n", "Note: The GUID generated here will change with each execution and is used only by the notebook.
'''))\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('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
'))\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('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.
By default the notebook will only show files that have been accessed by a single client IP or UA.
'))\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('Now it is time to hone in on our attacker. If you have multiple attacker indicators you can repeat from this step.
Select parameters to investigate, the default selection is the earliest access within the alert window:
'))\n", "display(HBox([pick_ip, pick_ua, pick_investigation]))\n", "widgets.jslink((pick_ip, 'index'), (pick_ua, 'index'))\n", "\n", "display(HTML('To determine what files were accessed immediately before the shell, please pick the window we\\'ll use to look back:
'))\n", "display(pick_window)\n", "\n", "display(HTML('Finally execute the below cell to collect additional details about the attacker.
'))" ] }, { "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", "if investigation_param == 1:\n", " display(HTML('Querying for attacker IP
'))\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('Querying for attacker UA
'))\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('Querying for attacker IP and UA
'))\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('Continue to generate your report
Attacker IP: {attackerip}
\n", "Attacker user agent: {attackerua}
\n", "Webshell installed: {shell_location}
\n", "Victim site: {sitename}
\n", "In the last 30 days the earliest known access to the server from the attacker IP was:
'))\n", " display(first_server_access_data[0])\n", " \n", "elif investigation_param == 2:\n", " display(HTML('In the last 30 days the earliest known access to the server from the attacker UA was:
'))\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('In the last 30 days the earliest known access to the server from the attacker IP was:
'))\n", " display(first_server_access_data[0])\n", " display(HTML('In the last 30 days the earliest known access to the server from the attacker UA was:
'))\n", " display(first_server_access_data[1])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "webshell_investigation", "language": "python", "name": "webshell_investigation" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.2" } }, "nbformat": 4, "nbformat_minor": 4 }