Entity Explorer - IP Address.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Title: IP Explorer\n",
    "<details>\n",
    "    <summary> <u>Details...</u></summary>\n",
    "    \n",
    "**Notebook Version:** 1.0<br>\n",
    "**Python Version:** Python 3.7 (including Python 3.6 - AzureML)<br>\n",
    "**Required Packages**: kqlmagic, msticpy, pandas, numpy, matplotlib, networkx, ipywidgets, ipython, scikit_learn, dnspython, ipwhois, folium, holoviews<br>\n",
    "**Platforms Supported**:\n",
    "- Azure Notebooks Free Compute\n",
    "- Azure Notebooks DSVM\n",
    "- OS Independent\n",
    "\n",
    "**Data Sources Required**:\n",
    "- Log Analytics \n",
    "    - Heartbeat\n",
    "    - SecurityAlert\n",
    "    - SecurityEvent\n",
    "    - AzureNetworkAnalytics_CL\n",
    "    \n",
    "- (Optional)  \n",
    "    - VirusTotal (with API key)\n",
    "    - Alienvault OTX (with API key) \n",
    "    - IBM Xforce (with API key) \n",
    "    - CommonSecurityLog\n",
    "</details>\n",
    "\n",
    "\n",
    "Brings together a series of queries and visualizations to help you assess the security state of an IP address. It works with both internal addresses and public addresses. \n",
    "<br> For internal addresses it focuses on traffic patterns and behavior of the host using that IP address. \n",
    "<br> For public IPs it lets you perform threat intelligence lookups, passive dns, whois and other checks. \n",
    "<br>It also allows you to examine any network traffic between the external IP address and your resources."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Hunting-Hypothesis\" data-toc-modified-id=\"Hunting-Hypothesis-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Hunting Hypothesis</a></span><ul class=\"toc-item\"><li><span><a href=\"#IP-Explorer-Mindmap\" data-toc-modified-id=\"IP-Explorer-Mindmap-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>IP Explorer Mindmap</a></span></li><li><span><a href=\"#Notebook-initialization\" data-toc-modified-id=\"Notebook-initialization-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Notebook initialization</a></span></li><li><span><a href=\"#Get-WorkspaceId-and-Authenticate-to-Log-Analytics\" data-toc-modified-id=\"Get-WorkspaceId-and-Authenticate-to-Log-Analytics-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Get WorkspaceId and Authenticate to Log Analytics</a></span></li></ul></li><li><span><a href=\"#Enter-the-IP-Address-and-query-time-window\" data-toc-modified-id=\"Enter-the-IP-Address-and-query-time-window-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Enter the IP Address and query time window</a></span></li><li><span><a href=\"#Determine-IP-Address-Type\" data-toc-modified-id=\"Determine-IP-Address-Type-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Determine IP Address Type</a></span></li><li><span><a href=\"#External-IP\" data-toc-modified-id=\"External-IP-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>External IP</a></span><ul class=\"toc-item\"><li><span><a href=\"#GeoIP-Lookups-for-External-IP-Addresses\" data-toc-modified-id=\"GeoIP-Lookups-for-External-IP-Addresses-4.1\"><span class=\"toc-item-num\">4.1&nbsp;&nbsp;</span>GeoIP Lookups for External IP Addresses</a></span></li><li><span><a href=\"#Whois-Registrars-for-External-IP-Addresses\" data-toc-modified-id=\"Whois-Registrars-for-External-IP-Addresses-4.2\"><span class=\"toc-item-num\">4.2&nbsp;&nbsp;</span>Whois Registrars for External IP Addresses</a></span></li><li><span><a href=\"#Opensource-and-Azure-Sentinel-ThreatIntel-Lookups\" data-toc-modified-id=\"Opensource-and-Azure-Sentinel-ThreatIntel-Lookups-4.3\"><span class=\"toc-item-num\">4.3&nbsp;&nbsp;</span>Opensource and Azure Sentinel ThreatIntel Lookups</a></span><ul class=\"toc-item\"><li><span><a href=\"#Configure-your-TI-Provider-settings\" data-toc-modified-id=\"Configure-your-TI-Provider-settings-4.3.1\"><span class=\"toc-item-num\">4.3.1&nbsp;&nbsp;</span>Configure your TI Provider settings</a></span></li></ul></li><li><span><a href=\"#Passive-DNS-lookups-for-External-IP-Addresses\" data-toc-modified-id=\"Passive-DNS-lookups-for-External-IP-Addresses-4.4\"><span class=\"toc-item-num\">4.4&nbsp;&nbsp;</span>Passive DNS lookups for External IP Addresses</a></span></li></ul></li><li><span><a href=\"#Internal-IP-Address\" data-toc-modified-id=\"Internal-IP-Address-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Internal IP Address</a></span><ul class=\"toc-item\"><li><span><a href=\"#Data-Sources-available-to-query-related-to-IP\" data-toc-modified-id=\"Data-Sources-available-to-query-related-to-IP-5.1\"><span class=\"toc-item-num\">5.1&nbsp;&nbsp;</span>Data Sources available to query related to IP</a></span></li><li><span><a href=\"#Check-if-IP-is-assigned-to-multiple-hostnames\" data-toc-modified-id=\"Check-if-IP-is-assigned-to-multiple-hostnames-5.2\"><span class=\"toc-item-num\">5.2&nbsp;&nbsp;</span>Check if IP is assigned to multiple hostnames</a></span></li><li><span><a href=\"#System-Info\" data-toc-modified-id=\"System-Info-5.3\"><span class=\"toc-item-num\">5.3&nbsp;&nbsp;</span>System Info</a></span></li><li><span><a href=\"#ServiceMap---Get-List-of-Services-for-Host\" data-toc-modified-id=\"ServiceMap---Get-List-of-Services-for-Host-5.4\"><span class=\"toc-item-num\">5.4&nbsp;&nbsp;</span>ServiceMap - Get List of Services for Host</a></span></li></ul></li><li><span><a href=\"#Related-Alerts\" data-toc-modified-id=\"Related-Alerts-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Related Alerts</a></span><ul class=\"toc-item\"><li><span><a href=\"#Visualization---Timeline-of-Related-Alerts\" data-toc-modified-id=\"Visualization---Timeline-of-Related-Alerts-6.1\"><span class=\"toc-item-num\">6.1&nbsp;&nbsp;</span>Visualization - Timeline of Related Alerts</a></span></li><li><span><a href=\"#Browse-List-of-Related-Alerts\" data-toc-modified-id=\"Browse-List-of-Related-Alerts-6.2\"><span class=\"toc-item-num\">6.2&nbsp;&nbsp;</span>Browse List of Related Alerts</a></span></li></ul></li><li><span><a href=\"#Related-Hosts\" data-toc-modified-id=\"Related-Hosts-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Related Hosts</a></span><ul class=\"toc-item\"><li><ul class=\"toc-item\"><li><span><a href=\"#NOTE---the-following-sections-are-only-relevant-for-Internal-IP-Addresses.\" data-toc-modified-id=\"NOTE---the-following-sections-are-only-relevant-for-Internal-IP-Addresses.-7.0.1\"><span class=\"toc-item-num\">7.0.1&nbsp;&nbsp;</span><strong>NOTE - the following sections are only relevant for Internal IP Addresses.</strong></a></span></li></ul></li><li><span><a href=\"#Visualization---Networkx-Graph\" data-toc-modified-id=\"Visualization---Networkx-Graph-7.1\"><span class=\"toc-item-num\">7.1&nbsp;&nbsp;</span>Visualization - Networkx Graph</a></span></li></ul></li><li><span><a href=\"#Related-Accounts\" data-toc-modified-id=\"Related-Accounts-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>Related Accounts</a></span><ul class=\"toc-item\"><li><span><a href=\"#Visualization---Networkx-Graph\" data-toc-modified-id=\"Visualization---Networkx-Graph-8.1\"><span class=\"toc-item-num\">8.1&nbsp;&nbsp;</span>Visualization - Networkx Graph</a></span></li></ul></li><li><span><a href=\"#Logon-Summary-for-Related-Entities\" data-toc-modified-id=\"Logon-Summary-for-Related-Entities-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Logon Summary for Related Entities</a></span><ul class=\"toc-item\"><li><span><a href=\"#HeatMap-for-Weekly-failed-logons\" data-toc-modified-id=\"HeatMap-for-Weekly-failed-logons-9.1\"><span class=\"toc-item-num\">9.1&nbsp;&nbsp;</span>HeatMap for Weekly failed logons</a></span></li><li><span><a href=\"#Host-Logons-Timeline\" data-toc-modified-id=\"Host-Logons-Timeline-9.2\"><span class=\"toc-item-num\">9.2&nbsp;&nbsp;</span>Host Logons Timeline</a></span></li><li><span><a href=\"#Failed-Logons-Timeline\" data-toc-modified-id=\"Failed-Logons-Timeline-9.3\"><span class=\"toc-item-num\">9.3&nbsp;&nbsp;</span>Failed Logons Timeline</a></span></li></ul></li><li><span><a href=\"#Network-Connection-Analysis\" data-toc-modified-id=\"Network-Connection-Analysis-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>Network Connection Analysis</a></span><ul class=\"toc-item\"><li><span><a href=\"#Network-Check-Communications-with-Other-Hosts\" data-toc-modified-id=\"Network-Check-Communications-with-Other-Hosts-10.1\"><span class=\"toc-item-num\">10.1&nbsp;&nbsp;</span>Network Check Communications with Other Hosts</a></span></li><li><span><a href=\"#Query-Flows-by-IP-Address\" data-toc-modified-id=\"Query-Flows-by-IP-Address-10.2\"><span class=\"toc-item-num\">10.2&nbsp;&nbsp;</span>Query Flows by IP Address</a></span></li><li><span><a href=\"#Bulk-whois-lookup\" data-toc-modified-id=\"Bulk-whois-lookup-10.3\"><span class=\"toc-item-num\">10.3&nbsp;&nbsp;</span>Bulk whois lookup</a></span></li><li><span><a href=\"#Choose-ASNs/IPs-to-Check-for-Threat-Intel-Reports\" data-toc-modified-id=\"Choose-ASNs/IPs-to-Check-for-Threat-Intel-Reports-10.4\"><span class=\"toc-item-num\">10.4&nbsp;&nbsp;</span>Choose ASNs/IPs to Check for Threat Intel Reports</a></span></li><li><span><a href=\"#GeoIP-Map-of-External-IPs\" data-toc-modified-id=\"GeoIP-Map-of-External-IPs-10.5\"><span class=\"toc-item-num\">10.5&nbsp;&nbsp;</span>GeoIP Map of External IPs</a></span></li><li><span><a href=\"#Outbound-Data-transfer-Time-Series-Anomalies\" data-toc-modified-id=\"Outbound-Data-transfer-Time-Series-Anomalies-10.6\"><span class=\"toc-item-num\">10.6&nbsp;&nbsp;</span>Outbound Data transfer Time Series Anomalies</a></span></li></ul></li><li><span><a href=\"#Conclusion\" data-toc-modified-id=\"Conclusion-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Conclusion</a></span><ul class=\"toc-item\"><li><span><a href=\"#List-of-Suspicious-Activities/-Observables/Hunting-bookmarks\" data-toc-modified-id=\"List-of-Suspicious-Activities/-Observables/Hunting-bookmarks-11.1\"><span class=\"toc-item-num\">11.1&nbsp;&nbsp;</span>List of Suspicious Activities/ Observables/Hunting bookmarks</a></span></li></ul></li><li><span><a href=\"#Appendices\" data-toc-modified-id=\"Appendices-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Appendices</a></span></li><li><span><a href=\"#Available-DataFrames\" data-toc-modified-id=\"Available-DataFrames-13\"><span class=\"toc-item-num\">13&nbsp;&nbsp;</span>Available DataFrames</a></span><ul class=\"toc-item\"><li><span><a href=\"#Saving-Data-to-Excel\" data-toc-modified-id=\"Saving-Data-to-Excel-13.1\"><span class=\"toc-item-num\">13.1&nbsp;&nbsp;</span>Saving Data to Excel</a></span></li></ul></li><li><span><a href=\"#Configuration\" data-toc-modified-id=\"Configuration-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Configuration</a></span><ul class=\"toc-item\"><li><span><a href=\"#msticpyconfig.yaml-configuration-File\" data-toc-modified-id=\"msticpyconfig.yaml-configuration-File-14.1\"><span class=\"toc-item-num\">14.1&nbsp;&nbsp;</span><code>msticpyconfig.yaml</code> configuration File</a></span></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Hunting Hypothesis\n",
    "Our broad initial hunting hypothesis is that a we have received IP address entity which is suspected to be compromized internal host or external public address to whom internal hosts are communicating in malicious manner, we will need to hunt from a range of different positions to validate or disprove this hypothesis.\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### IP Explorer Mindmap\n",
    "Below mindmap diagram shows hunting workflow depending upon the type of IP address provided\n",
    "\n",
    "![IPExplorerMindMap](https://github.com/Azure/Azure-Sentinel-Notebooks/raw/master/images/nb_ipexplorer-mindmap.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### Notebook initialization\n",
    "The next cell:\n",
    "- Checks for the correct Python version\n",
    "- Checks versions and optionally installs required packages\n",
    "- Imports the required packages into the notebook\n",
    "- Sets a number of configuration options.\n",
    "\n",
    "This should complete without errors. If you encounter errors or warnings look at the following two notebooks:\n",
    "- [TroubleShootingNotebooks](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/TroubleShootingNotebooks.ipynb)\n",
    "- [ConfiguringNotebookEnvironment](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb)\n",
    "\n",
    "If you are running in the Azure Sentinel Notebooks environment (Azure Notebooks or Azure ML) you can run live versions of these notebooks:\n",
    "- [Run TroubleShootingNotebooks](./TroubleShootingNotebooks.ipynb)\n",
    "- [Run ConfiguringNotebookEnvironment](./ConfiguringNotebookEnvironment.ipynb)\n",
    "\n",
    "You may also need to do some additional configuration to successfully use functions such as Threat Intelligence service lookup and Geo IP lookup. \n",
    "There are more details about this in the `ConfiguringNotebookEnvironment` notebook and in these documents:\n",
    "- [msticpy configuration](https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html)\n",
    "- [Threat intelligence provider configuration](https://msticpy.readthedocs.io/en/latest/data_acquisition/TIProviders.html#configuration-file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:01:51.949751Z",
     "start_time": "2020-05-15T23:01:51.909753Z"
    }
   },
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import os\n",
    "import sys\n",
    "import warnings\n",
    "from IPython.display import display, HTML, Markdown\n",
    "\n",
    "REQ_PYTHON_VER=(3, 6)\n",
    "REQ_MSTICPY_VER=(0, 6, 0)\n",
    "\n",
    "display(HTML(\"<h3>Starting Notebook setup...</h3>\"))\n",
    "if Path(\"./utils/nb_check.py\").is_file():\n",
    "    from utils.nb_check import check_python_ver, check_mp_ver\n",
    "\n",
    "    check_python_ver(min_py_ver=REQ_PYTHON_VER)\n",
    "    try:\n",
    "        check_mp_ver(min_msticpy_ver=REQ_MSTICPY_VER)\n",
    "    except ImportError:\n",
    "        !pip install --upgrade msticpy\n",
    "        if \"msticpy\" in sys.modules:\n",
    "            importlib.reload(sys.modules[\"msticpy\"])\n",
    "        else:\n",
    "            import msticpy\n",
    "        check_mp_ver(REQ_MSTICPY_VER)\n",
    "            \n",
    "\n",
    "# If not using Azure Notebooks, install msticpy with\n",
    "# !pip install msticpy\n",
    "from msticpy.nbtools import nbinit\n",
    "extra_imports = [\n",
    "    \"msticpy.nbtools.entityschema, IpAddress\",\n",
    "    \"msticpy.nbtools.entityschema, GeoLocation\",\n",
    "    \"msticpy.sectools.ip_utils, create_ip_record\",\n",
    "    \"msticpy.sectools.ip_utils, get_ip_type\",\n",
    "    \"msticpy.sectools.ip_utils, get_whois_info\",\n",
    "]\n",
    "nbinit.init_notebook(\n",
    "    namespace=globals(),\n",
    "    extra_imports=extra_imports,\n",
    ");\n",
    "WIDGET_DEFAULTS = {\n",
    "    \"layout\": widgets.Layout(width=\"95%\"),\n",
    "    \"style\": {\"description_width\": \"initial\"},\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Get WorkspaceId and Authenticate to Log Analytics \n",
    "<details>\n",
    "    <summary> <u>Details...</u></summary>\n",
    "If you are using user/device authentication, run the following cell. \n",
    "- Click the 'Copy code to clipboard and authenticate' button.\n",
    "- This will pop up an Azure Active Directory authentication dialog (in a new tab or browser window). The device code will have been copied to the clipboard. \n",
    "- Select the text box and paste (Ctrl-V/Cmd-V) the copied value. \n",
    "- You should then be redirected to a user authentication page where you should authenticate with a user account that has permission to query your Log Analytics workspace.\n",
    "\n",
    "Use the following syntax if you are authenticating using an Azure Active Directory AppId and Secret:\n",
    "```\n",
    "%kql loganalytics://tenant(aad_tenant).workspace(WORKSPACE_ID).clientid(client_id).clientsecret(client_secret)\n",
    "```\n",
    "instead of\n",
    "```\n",
    "%kql loganalytics://code().workspace(WORKSPACE_ID)\n",
    "```\n",
    "\n",
    "Note: you may occasionally see a JavaScript error displayed at the end of the authentication - you can safely ignore this.<br>\n",
    "On successful authentication you should see a ```popup schema``` button.\n",
    "To find your Workspace Id go to [Log Analytics](https://ms.portal.azure.com/#blade/HubsExtension/Resources/resourceType/Microsoft.OperationalInsights%2Fworkspaces). Look at the workspace properties to find the ID.\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:02:52.662562Z",
     "start_time": "2020-05-15T23:02:52.653563Z"
    }
   },
   "outputs": [],
   "source": [
    "#See if we have an Azure Sentinel Workspace defined in our config file, if not let the user specify Workspace and Tenant IDs\n",
    "from msticpy.nbtools.wsconfig import WorkspaceConfig\n",
    "ws_config = WorkspaceConfig()\n",
    "try:\n",
    "    ws_id = ws_config['workspace_id']\n",
    "    ten_id = ws_config['tenant_id']\n",
    "    config = True\n",
    "    md(\"Workspace details collected from config file\")\n",
    "except KeyError:\n",
    "    md(('Please go to your Log Analytics workspace, copy the workspace ID'\n",
    "                 ' and/or tenant Id and paste here to enable connection to the workspace and querying of it..<br> '))\n",
    "    ws_id_wgt = nbwidgets.GetEnvironmentKey(env_var='WORKSPACE_ID',\n",
    "                                        prompt='Please enter your Log Analytics Workspace Id:', auto_display=True)\n",
    "    ten_id_wgt = nbwidgets.GetEnvironmentKey(env_var='TENANT_ID',\n",
    "                                         prompt='Please enter your Log Analytics Tenant Id:', auto_display=True)\n",
    "    config = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:03:22.552179Z",
     "start_time": "2020-05-15T23:02:56.043852Z"
    }
   },
   "outputs": [],
   "source": [
    "# Authentication\n",
    "qry_prov = QueryProvider(data_environment=\"LogAnalytics\")\n",
    "qry_prov.connect(connection_str=ws_config.code_connect_str)\n",
    "table_index = qry_prov.schema_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Enter the IP Address and query time window\n",
    "\n",
    "Type the IP address you want to search for and the time bounds over which search.\n",
    "\n",
    "You can specify the IP address value in the widget e.g. 192.168.1.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:03:22.632179Z",
     "start_time": "2020-05-15T23:03:22.619179Z"
    }
   },
   "outputs": [],
   "source": [
    "ipaddr_text = widgets.Text(\n",
    "    description=\"Enter the IP Address to search for:\", **WIDGET_DEFAULTS\n",
    ")\n",
    "display(ipaddr_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:03:56.698491Z",
     "start_time": "2020-05-15T23:03:56.631491Z"
    }
   },
   "outputs": [],
   "source": [
    "query_times = nbwidgets.QueryTime(units=\"day\", max_before=20, before=5, max_after=7)\n",
    "query_times.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Determine IP Address Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:04:47.927548Z",
     "start_time": "2020-05-15T23:04:43.963316Z"
    }
   },
   "outputs": [],
   "source": [
    "# Set up function to allow easy reference to common parameters for queries throughout the notebook\n",
    "def ipaddr_query_params():\n",
    "    return {\n",
    "        \"start\": query_times.start,\n",
    "        \"end\": query_times.end,\n",
    "        \"ip_address\": ipaddr_text.value.strip()\n",
    "    }\n",
    "\n",
    "ipaddr_type = get_ip_type(ipaddr_query_params()['ip_address'])\n",
    "\n",
    "md(f'Depending on the IP Address origin, different sections of this notebook are applicable', styles=[\"bold\", \"large\"])\n",
    "md(f'Please follow either the Interal IP Address or External IP Address sections based on below Recommendation', styles=[\"bold\"])\n",
    "\n",
    "#Get details from Heartbeat table for the given IP Address and Time Parameters\n",
    "heartbeat_df = qry_prov.Heartbeat.get_info_by_ipaddress(**ipaddr_query_params())\n",
    "\n",
    "# Set hostnames retrived from Heartbeat table if available\n",
    "if not heartbeat_df.empty:\n",
    "    hostname = heartbeat_df[\"Computer\"][0]\n",
    "else:\n",
    "    hostname = \"\"\n",
    "    \n",
    "if not heartbeat_df.empty:\n",
    "    ipaddr_origin = \"Internal\"\n",
    "    md(f'IP Address type based on subnet: {ipaddr_type} & IP Address Owner based on available logs : {ipaddr_origin}', styles=[\"blue\",\"bold\"])\n",
    "    display(Markdown('#### Recommendation - Go to section [InternalIP](#goto_internalIP)'))\n",
    "elif ipaddr_type==\"Private\" and heartbeat_df.empty:\n",
    "    ipaddr_origin = \"Unknown\"\n",
    "    md(f'IP Address type based on subnet: {ipaddr_type} & IP Address Owner based on available logs : {ipaddr_origin}', styles=[\"blue\",\"bold\"])\n",
    "    display(Markdown('#### Recommendation - Go to section [InternalIP](#goto_internalIP)'))\n",
    "else:\n",
    "    ipaddr_origin = \"External\"\n",
    "    md(f'IP Address type based on subnet: {ipaddr_type} & IP Address Owner based on available logs : {ipaddr_origin}', styles=[\"blue\",\"bold\"])\n",
    "    display(Markdown('#### Recommendation - Go to section [ExternalIP](#goto_externalIP)'))\n",
    "    \n",
    "#Populate related IP addresses for the calculated hostname\n",
    "az_net_df = pd.DataFrame()\n",
    "if \"AzureNetworkAnalytics_CL\" in table_index:\n",
    "    aznet_query = f\"\"\"\n",
    "    AzureNetworkAnalytics_CL | where ResourceType == 'NetworkInterface' \n",
    "    | where SubType_s == \"Topology\" \n",
    "    | search \\'{ipaddr_text.value}\\' \n",
    "    | where TimeGenerated >= datetime({query_times.start}) \n",
    "    | where TimeGenerated <= datetime({query_times.end}) \n",
    "    | where VirtualMachine_s has '{hostname}' \n",
    "    | top 1 by TimeGenerated desc \n",
    "    | project PrivateIPAddresses = PrivateIPAddresses_s, PublicIPAddresses = PublicIPAddresses_s\"\"\"\n",
    "    az_net_df = qry_prov.exec_query(query=aznet_query)\n",
    "    \n",
    "# Create IP Entity record using available dataframes or input ip address if nothing present\n",
    "if az_net_df.empty and heartbeat_df.empty:\n",
    "    ip_entity = IpAddress()\n",
    "    ip_entity['Address'] = ipaddr_query_params()['ip_address']\n",
    "    ip_entity['Type'] = 'ipaddress'\n",
    "    ip_entity['OSType'] = 'Unknown'\n",
    "    md('No Heartbeat Data and Network topology data found')\n",
    "elif not heartbeat_df.empty:\n",
    "    if az_net_df.empty:\n",
    "        ip_entity = create_ip_record(\n",
    "            heartbeat_df=heartbeat_df)\n",
    "    else:\n",
    "        ip_entity = create_ip_record(\n",
    "            heartbeat_df=heartbeat_df, az_net_df=az_net_df)\n",
    "#Display IP Entity\n",
    "md(\"Displaying IP Entity\", styles=[\"green\",\"bold\"])\n",
    "print(ip_entity)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='goto_externalIP'></a>\n",
    "## External IP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### GeoIP Lookups for External IP Addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-04-27T08:33:37.478812Z",
     "start_time": "2020-04-27T08:33:37.470173Z"
    }
   },
   "outputs": [],
   "source": [
    "# msticpy- geoip module to retrieving Geo Location for Public IP addresses\n",
    "# To force Threatinel lookup for Internal public IP, replace and with or in if condition\n",
    "if ipaddr_type == \"Public\" and ipaddr_origin == \"External\" :\n",
    "    iplocation = GeoLiteLookup()\n",
    "\n",
    "    loc_results, ext_ip_entity = iplocation.lookup_ip(ip_address=ipaddr_query_params()['ip_address'])\n",
    "    md(\n",
    "            'Geo Location for the IP Address ::', styles=[\"bold\",\"green\"]\n",
    "        )\n",
    "    print(ext_ip_entity[0])\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Whois Registrars for External IP Addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-04-27T08:33:39.572115Z",
     "start_time": "2020-04-27T08:33:39.566009Z"
    }
   },
   "outputs": [],
   "source": [
    "# ipwhois module to retrieve whois registrar for Public IP addresses\n",
    "# To force Threatinel lookup for Internal public IP, replace and with or in if condition\n",
    "if ipaddr_type == \"Public\" and ipaddr_origin == \"External\" :\n",
    "    from ipwhois import IPWhois\n",
    "\n",
    "    whois = IPWhois(ipaddr_query_params()['ip_address'])\n",
    "    whois_result = whois.lookup_whois()\n",
    "    if whois_result:\n",
    "        md(f'Whois Registrar Info ::', styles=[\"bold\",\"green\"])\n",
    "        display(whois_result)\n",
    "    else:\n",
    "        md(\n",
    "                f'No whois records available', styles=[\"bold\",\"orange\"]\n",
    "            )\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Opensource and Azure Sentinel ThreatIntel Lookups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Configure your TI Provider settings\n",
    "If you have not used threat intelligence lookups before you will need to supply API keys for the \n",
    "TI Providers that you want to use. Please see the section on configuring [msticpyconfig.yaml](#msticpyconfig.yaml-configuration-File)\n",
    "\n",
    "Then reload provider settings:\n",
    "```\n",
    "mylookup = TILookup()\n",
    "mylookup.reload_provider_settings()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-04-27T08:33:43.562087Z",
     "start_time": "2020-04-27T08:33:43.554830Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# To force Threatinel lookup for Internal public IP, replace and with or in if condition\n",
    "if ipaddr_type == \"Public\" and ipaddr_origin == \"External\" :\n",
    "    mylookup = TILookup()\n",
    "    mylookup.loaded_providers\n",
    "    resp = mylookup.lookup_ioc(observable=ipaddr_query_params()['ip_address'], ioc_type=\"ipv4\")\n",
    "    md(f'ThreatIntel Lookup for IP ::', styles=[\"bold\",\"green\"])\n",
    "    display(mylookup.result_to_df(resp).T)\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Passive DNS lookups for External IP Addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-04-27T08:33:45.838706Z",
     "start_time": "2020-04-27T08:33:45.829919Z"
    }
   },
   "outputs": [],
   "source": [
    "# To force Passive DNS lookup for Internal public IP, change and with or in if\n",
    "if ipaddr_type == \"Public\" and ipaddr_origin == \"External\" :\n",
    "    # retrieve passive dns from TI Providers\n",
    "    pdns = mylookup.lookup_ioc(\n",
    "        observable=ipaddr_query_params()['ip_address'],\n",
    "        ioc_type=\"ipv4\",\n",
    "        ioc_query_type=\"passivedns\",\n",
    "        providers=[\"XForce\"],\n",
    "    )\n",
    "    pdns_df = mylookup.result_to_df(pdns)\n",
    "    if not pdns_df.empty and pdns_df[\"RawResult\"][0] and \"RDNS\" in pdns_df[\"RawResult\"][0]:\n",
    "        pdnsdomains = pdns_df[\"RawResult\"][0][\"RDNS\"]\n",
    "        md(\n",
    "                'Passive DNS domains for IP: {pdnsdomains}',styles=[\"bold\",\"green\"]\n",
    "            )\n",
    "        display(mylookup.result_to_df(pdns).T)\n",
    "    else:\n",
    "        md(\n",
    "                'No passive domains found from the providers', styles=[\"bold\",\"orange\"]\n",
    "            )\n",
    "else:\n",
    "     md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='goto_internalIP'></a>\n",
    "## Internal IP Address"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Data Sources available to query related to IP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:04:59.773853Z",
     "start_time": "2020-05-15T23:04:53.039482Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin in [\"Internal\",\"Unknown\"]:\n",
    "    # KQL query for full text search of IP address and display all datatypes populated for the time period\n",
    "    datasource_status = \"\"\"\n",
    "    search \\'{ip_address}\\' or \\'{hostname}\\'\n",
    "    | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "    | summarize RowCount=count() by Table=$table\n",
    "    \"\"\".format(\n",
    "        **ipaddr_query_params(), hostname=hostname\n",
    "    )\n",
    "    datasource_status_df = qry_prov.exec_query(datasource_status)\n",
    "\n",
    "    # Display result as transposed matrix of datatypes availabel to query for the query period\n",
    "    if not datasource_status_df.empty:\n",
    "        available_datasets = datasource_status_df['Table'].values\n",
    "        md(\"Datasources available to query for IP ::\", styles=[\"green\",\"bold\"])\n",
    "        display(datasource_status_df)\n",
    "    else:\n",
    "        md_warn(\"No datasources contain given IP address for the query period\")\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address type is: {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Check if IP is assigned to multiple hostnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:03.895367Z",
     "start_time": "2020-05-15T23:05:02.486243Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\" or not datasource_status_df.empty:\n",
    "    # Get single event - try process creation\n",
    "    if ip_entity['OSType'] =='Windows':\n",
    "        if \"SecurityEvent\" not in available_datasets:\n",
    "            raise ValueError(\"No Windows event log data available in the workspace\")\n",
    "        host_name = None\n",
    "        matching_hosts_df = qry_prov.WindowsSecurity.list_host_processes(\n",
    "            query_times, host_name=hostname, add_query_items=\"| distinct Computer\"\n",
    "        )\n",
    "    elif ip_entity['OSType'] =='Linux':\n",
    "        if \"Syslog\" not in available_datasets:\n",
    "            raise ValueError(\"No Linux syslog data available in the workspace\")\n",
    "        else:\n",
    "            linux_syslog_query = f\"\"\" Syslog | where TimeGenerated >= datetime({query_times.start}) | where TimeGenerated <= datetime({query_times.end}) | where HostIP == '{ipaddr_text.value}' | distinct Computer \"\"\"\n",
    "            matching_hosts_df = qry_prov.exec_query(query=linux_syslog_query)\n",
    "\n",
    "    if len(matching_hosts_df) > 1:\n",
    "        print(f\"Multiple matches for '{hostname}'. Please select a host from the list.\")\n",
    "        choose_host = nbwidgets.SelectItem(\n",
    "            item_list=list(matching_hosts_df[\"Computer\"].values),\n",
    "            description=\"Select the host.\",\n",
    "            auto_display=True,\n",
    "        )\n",
    "    elif not matching_hosts_df.empty:\n",
    "        host_name = matching_hosts_df[\"Computer\"].iloc[0]\n",
    "        print(f\"Unique host found for IP: {hostname}\")\n",
    "elif datasource_status_df.empty:\n",
    "    md_warn(\"No datasources contain given IP address for the query period\")\n",
    "else:   \n",
    "    md(f'Analysis section Not Applicable since IP address type is : {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### System Info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:07.346683Z",
     "start_time": "2020-05-15T23:05:07.330684Z"
    }
   },
   "outputs": [],
   "source": [
    "# Retrieving System info from internal table if IP address is not Public\n",
    "if ipaddr_origin == \"Internal\" and not heartbeat_df.empty:\n",
    "    md(\n",
    "            'System Info retrieved from Heartbeat table ::', styles=[\"green\",\"bold\"]\n",
    "        )\n",
    "    display(heartbeat_df.T)\n",
    "else:\n",
    "        md_warn(\n",
    "            'No records available in HeartBeat table'\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Contents](#toc)\n",
    "### ServiceMap - Get List of Services for Host"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:10.389939Z",
     "start_time": "2020-05-15T23:05:10.369939Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\":\n",
    "    if \"ServiceMapProcess_CL\" not in available_datasets:\n",
    "            md_warn(\"ServiceMap data is not enabled\")\n",
    "            md(\n",
    "                f\"Enable ServiceMap Solution from Azure marketplce: <br>\"\n",
    "                +\"https://docs.microsoft.com/en-us/azure/azure-monitor/insights/service-map#enable-service-map\",\n",
    "                styles=[\"bold\"]\n",
    "            )\n",
    "\n",
    "    else:\n",
    "        servicemap_proc_query = \"\"\"\n",
    "        ServiceMapProcess_CL\n",
    "        | where Computer == \\'{hostname}\\'\n",
    "        | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "        | project Computer, Services_s, DisplayName_s, ExecutableName_s , ExecutablePath_s \n",
    "            \"\"\".format(\n",
    "            hostname=hostname, **ipaddr_query_params()\n",
    "        )\n",
    "\n",
    "        servicemap_proc_df = qry_prov.exec_query(servicemap_proc_query)\n",
    "        display(servicemap_proc_df)\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address type is {ipaddr_type}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Related Alerts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:14.185177Z",
     "start_time": "2020-05-15T23:05:14.123178Z"
    }
   },
   "outputs": [],
   "source": [
    "ra_query_times = nbwidgets.QueryTime(\n",
    "    units=\"day\",\n",
    "    origin_time=query_times.origin_time,\n",
    "    max_before=28,\n",
    "    max_after=5,\n",
    "    before=5,\n",
    "    auto_display=True,\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualization - Timeline of Related Alerts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:19.536611Z",
     "start_time": "2020-05-15T23:05:17.943028Z"
    }
   },
   "outputs": [],
   "source": [
    "#Provide hostname if present to the query\n",
    "if hostname:\n",
    "    md(f\"Searching for alerts related to {hostname}...\")\n",
    "    related_alerts = qry_prov.SecurityAlert.list_related_alerts(\n",
    "        ra_query_times, host_name=hostname\n",
    "    )\n",
    "else:\n",
    "    md(f\"Searching for alerts related to ip address(es) {ipaddr_query_params()['ip_address']}\")\n",
    "    related_alerts = qry_prov.SecurityAlert.list_alerts_for_ip(\n",
    "        ra_query_times, source_ip_list=ipaddr_query_params()['ip_address']\n",
    "    )\n",
    "\n",
    "\n",
    "def print_related_alerts(alertDict, entityType, entityName):\n",
    "    if len(alertDict) > 0:\n",
    "        md(\n",
    "                f\"Found {len(alertDict)} different alert types related to this {entityType} (`{entityName}`)\",styles=[\"bold\",\"orange\"]\n",
    "            )\n",
    "        for (k, v) in alertDict.items():\n",
    "            print(f\"- {k}, # Alerts: {v}\")\n",
    "    else:\n",
    "        print(f\"No alerts for {entityType} entity `{entityName}`\")\n",
    "\n",
    "\n",
    "if isinstance(related_alerts, pd.DataFrame) and not related_alerts.empty:\n",
    "    host_alert_items = (\n",
    "        related_alerts[[\"AlertName\", \"TimeGenerated\"]]\n",
    "        .groupby(\"AlertName\")\n",
    "        .TimeGenerated.agg(\"count\")\n",
    "        .to_dict()\n",
    "    )\n",
    "    print_related_alerts(host_alert_items, \"host\", hostname)\n",
    "    nbdisplay.display_timeline(\n",
    "        data=related_alerts, title=\"Alerts\", source_columns=[\"AlertName\"], height=200\n",
    "    )\n",
    "else:\n",
    "    md(\"No related alerts found.\",styles=[\"bold\",\"green\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ### Browse List of Related Alerts\n",
    " Select an Alert to view details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:24.530275Z",
     "start_time": "2020-05-15T23:05:24.464277Z"
    }
   },
   "outputs": [],
   "source": [
    "def disp_full_alert(alert):\n",
    "    global related_alert\n",
    "    related_alert = SecurityAlert(alert)\n",
    "    nbdisplay.display_alert(related_alert, show_entities=True)\n",
    "\n",
    "recenter_wgt = widgets.Checkbox(\n",
    "    value=True,\n",
    "    description='Center subsequent query times round selected Alert?',\n",
    "    disabled=False,\n",
    "    **WIDGET_DEFAULTS\n",
    ")\n",
    "if related_alerts is not None and not related_alerts.empty:\n",
    "    related_alerts[\"CompromisedEntity\"] = related_alerts[\"Computer\"]\n",
    "    md(\"Click on alert to view details.\", styles=[\"bold\"])\n",
    "    display(recenter_wgt)\n",
    "    rel_alert_select = nbwidgets.SelectAlert(\n",
    "        alerts=related_alerts,\n",
    "        action=disp_full_alert,\n",
    "    )\n",
    "    rel_alert_select.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Related Hosts\n",
    "**Hypothesis:** That an attacker has gained  access to the host, compromized credentials for the accounts and laterally moving to the network gaining access to more hosts.\n",
    "\n",
    "This section provides related hosts of IP address which is being investigated. .If you wish to expand the scope of hunting then investigate each hosts in detail, it is recommended that to use the **Host Explorer (Windows/Linux)**\n",
    " - [Entity Explorer - Windows Host](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/Entity%20Explorer%20-%20Windows%20Host.ipynb)\n",
    " - [Entity Explorer - Linux Host](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/Entity%20Explorer%20-%20Linux%20Host.ipynb)\n",
    " \n",
    "If you are running in the Azure Sentinel Notebooks environment (Azure Notebooks or Azure ML) you can run live versions of these notebooks:\n",
    " - [Run Entity Explorer - Windows Host](./Entity%20Explorer%20-%20Windows%20Host.ipynb)\n",
    " - [Run Entity Explorer - Linux Host](./Entity%20Explorer%20-%20Linux%20Host.ipynb)\n",
    "\n",
    "#### __NOTE - the following sections are only relevant for Internal IP Addresses.__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Visualization - Networkx Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:30.863302Z",
     "start_time": "2020-05-15T23:05:29.870080Z"
    }
   },
   "outputs": [],
   "source": [
    "import networkx as nx\n",
    "if ipaddr_origin == \"Internal\":\n",
    "    # Retrived relatd accounts from SecurityEvent table for Windows OS\n",
    "    if ip_entity['OSType'] =='Windows':\n",
    "        if \"SecurityEvent\" not in available_datasets:\n",
    "            raise ValueError(\"No Windows event log data available in the workspace\")\n",
    "        else:\n",
    "            related_hosts = \"\"\"\n",
    "               SecurityEvent\n",
    "               | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "               | where IpAddress == \\'{ip_address}\\' or Computer == \\'{hostname}\\'      \n",
    "               | summarize count() by Computer, IpAddress\n",
    "            \"\"\".format(\n",
    "                **ipaddr_query_params(), hostname=hostname\n",
    "            )\n",
    "\n",
    "            related_hosts_df = qry_prov.exec_query(related_hosts)\n",
    "\n",
    "    elif ip_entity['OSType'] =='Linux':\n",
    "        if \"Syslog\" not in available_datasets:\n",
    "            raise ValueError(\"No Linux syslog data available in the workspace\")\n",
    "        else:\n",
    "            related_hosts_df = qry_prov.LinuxSyslog.list_logons_for_source_ip(invest_times, ip_address=ipaddr_query_params()['ip_address'],add_query_items='extend IpAddress = HostIP | summarize count() by Computer, IpAddress')\n",
    "\n",
    "    # Displaying networkx - static graph. for interactive graph uncomment and run next block of code.\n",
    "    plt.figure(10, figsize=(22, 14))\n",
    "    g = nx.from_pandas_edgelist(related_hosts_df, \"IpAddress\", \"Computer\")\n",
    "    md('Entity Relationship Graph - Related Hosts :: ',styles=[\"bold\",\"green\"])\n",
    "    nx.draw_circular(g, with_labels=True, size=40, font_size=12, font_color=\"blue\")\n",
    "\n",
    "\n",
    "    # Uncomment below cells if you want to dispaly interactive graphs using Pyvis library, Azure notebook free tier may not render the graph correctly.\n",
    "    # logonpyvis_graph = Network(notebook=True, height=\"750px\", width=\"100%\", bgcolor=\"#222222\", font_color=\"white\")\n",
    "\n",
    "    # # set the physics layout of the network\n",
    "    # logonpyvis_graph.barnes_hut()\n",
    "\n",
    "    # sources = related_hosts_df['Computer']\n",
    "    # targets = related_hosts_df['IpAddress']\n",
    "    # weights = related_hosts_df['count_']\n",
    "\n",
    "    # edge_data = zip(sources, targets, weights)\n",
    "\n",
    "    # for e in edge_data:\n",
    "    #     src = e[0]\n",
    "    #     dst = e[1]\n",
    "    #     w = e[2]\n",
    "\n",
    "    # logonpyvis_graph.add_node(src, src, title=src)\n",
    "    # logonpyvis_graph.add_node(dst, dst, title=dst)\n",
    "    # logonpyvis_graph.add_edge(src, dst, value=w)\n",
    "\n",
    "    # neighbor_map = logonpyvis_graph.get_adj_list()\n",
    "\n",
    "    # # add neighbor data to node hover data\n",
    "    # for node in logonpyvis_graph.nodes:\n",
    "    #     node[\"title\"] += \" Neighbors:<br>\" + \"<br>\".join(neighbor_map[node[\"id\"]])\n",
    "    #     node[\"value\"] = len(neighbor_map[node[\"id\"]]) \n",
    "\n",
    "    # logonpyvis_graph.show(\"hostlogonpyvis_graph.html\")\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Related Accounts\n",
    "**Hypothesis:** That an attacker has gained  access to the host, compromized credentials for the accounts on it and laterally moving to the network gaining access to more accounts.\n",
    "\n",
    "This section provides related accounts of IP address which is being investigated. .If you wish to expand the scope of hunting then investigate each accounts in detail, it is recommended that to use the **Account Explorer.**\n",
    " - [Entity Explorer - Account](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/Entity%20Explorer%20-%20Account.ipynb)\n",
    "\n",
    "If you are running in the Azure Sentinel Notebooks environment (Azure Notebooks or Azure ML) you can run live versions of these notebooks:\n",
    " - [Run Entity Explorer - Account](./Entity%20Explorer%20-%20Account.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-10T20:12:42.022358Z",
     "start_time": "2019-09-10T20:12:42.010961Z"
    }
   },
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Visualization - Networkx Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:36.951055Z",
     "start_time": "2020-05-15T23:05:35.741976Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\":\n",
    "    # Retrived relatd accounts from SecurityEvent table for Windows OS\n",
    "    if ip_entity['OSType'] =='Windows':\n",
    "        if \"SecurityEvent\" not in available_datasets:\n",
    "            raise ValueError(\"No Windows event log data available in the workspace\")\n",
    "        else:\n",
    "            related_accounts = \"\"\"\n",
    "               SecurityEvent\n",
    "               | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "               | where IpAddress == \\'{ip_address}\\' or Computer == \\'{hostname}\\'       \n",
    "               | summarize count() by Account, Computer\n",
    "            \"\"\".format(\n",
    "                **ipaddr_query_params(), hostname=hostname\n",
    "            )\n",
    "            related_accounts_df = qry_prov.exec_query(related_accounts)\n",
    "\n",
    "    elif ip_entity['OSType'] =='Linux':\n",
    "        if \"Syslog\" not in available_datasets:\n",
    "            raise ValueError(\"No Linux syslog data available in the workspace\")\n",
    "        else:\n",
    "            related_accounts_df = qry_prov.LinuxSyslog.list_logons_for_source_ip(invest_times, ip_address=ipaddr_query_params()['ip_address'],add_query_items='extend Account = AccountName | summarize count() by Account, Computer')\n",
    "\n",
    "\n",
    "    # Uncomment- below cells if above visualization does not render - Networkx connected Graph\n",
    "    plt.figure(10, figsize=(22, 14))\n",
    "    g = nx.from_pandas_edgelist(related_accounts_df, \"Computer\", \"Account\")\n",
    "    md('Entity Relationship Graph - Related Accounts :: ',styles=[\"bold\",\"green\"])\n",
    "    nx.draw_circular(g, with_labels=True, size=40, font_size=12, font_color=\"blue\")\n",
    "\n",
    "    # Uncomment below cells if you want to display interactive graphs using Pyvis library, Azure notebook free tier may not render the graph correctly.\n",
    "    # acclogon_pyvisgraph = Network(notebook=True, height=\"750px\", width=\"100%\", bgcolor=\"#222222\", font_color=\"white\")\n",
    "\n",
    "    # # set the physics layout of the network\n",
    "    # acclogon_pyvisgraph.barnes_hut()\n",
    "\n",
    "\n",
    "    # sources = related_accounts_df['Computer']\n",
    "    # targets = related_accounts_df['Account']\n",
    "    # weights = related_accounts_df['count_']\n",
    "\n",
    "    # edge_data = zip(sources, targets, weights)\n",
    "\n",
    "    # for e in edge_data:\n",
    "    #     src = e[0]\n",
    "    #     dst = e[1]\n",
    "    #     w = e[2]\n",
    "\n",
    "    # acclogon_pyvisgraph.add_node(src, src, title=src)\n",
    "    # acclogon_pyvisgraph.add_node(dst, dst, title=dst)\n",
    "    # acclogon_pyvisgraph.add_edge(src, dst, value=w)\n",
    "\n",
    "    # neighbor_map = acclogon_pyvisgraph.get_adj_list()\n",
    "\n",
    "    # # add neighbor data to node hover data\n",
    "    # for node in acclogon_pyvisgraph.nodes:\n",
    "    #     node[\"title\"] += \" Neighbors:<br>\" + \"<br>\".join(neighbor_map[node[\"id\"]])\n",
    "    #     node[\"value\"] = len(neighbor_map[node[\"id\"]]) # this value attrribute for the node affects node size\n",
    "\n",
    "    # acclogon_pyvisgraph.show(\"accountlogonpyvis_graph.html\")\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-30T15:50:05.854226Z",
     "start_time": "2019-08-30T15:50:04.517392Z"
    }
   },
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Logon Summary for Related Entities\n",
    "**Hypothesis:** By analyzing logon activities of the related entities, we can identify change in logon patterns and narrow down the entities to few suspicious logon patterns.\n",
    "\n",
    "This section provides various visualization of logon attributes such as \n",
    "- Weekly Failed Logon trend\n",
    "- Logon Types \n",
    "- Logon Processes\n",
    "\n",
    "If you wish to expand the scope of hunting then investigate specific host in detail, it is recommended that to use the **Host Explorer (Windows/Linux)**\n",
    "\n",
    " - [Entity Explorer - Windows Host](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/Entity%20Explorer%20-%20Windows%20Host.ipynb)\n",
    " - [Entity Explorer - Linux Host](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/Entity%20Explorer%20-%20Linux%20Host.ipynb)\n",
    " \n",
    "If you are running in the Azure Sentinel Notebooks environment (Azure Notebooks or Azure ML) you can run live versions of these notebooks:\n",
    " - [Run Entity Explorer - Windows Host](./Entity%20Explorer%20-%20Windows%20Host.ipynb)\n",
    " - [Run Entity Explorer - Linux Host](./Entity%20Explorer%20-%20Linux%20Host.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-10T20:18:33.673179Z",
     "start_time": "2019-09-10T20:18:33.670042Z"
    }
   },
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### HeatMap for Weekly failed logons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:46.615934Z",
     "start_time": "2020-05-15T23:05:44.570772Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\":\n",
    "    # Retrived related accounts from SecurityEvent table for Windows OS\n",
    "    if ip_entity['OSType'] =='Windows':\n",
    "        if \"SecurityEvent\" not in available_datasets:\n",
    "            raise ValueError(\"No Windows event log data available in the workspace\")\n",
    "        else:\n",
    "            failed_logons = \"\"\"\n",
    "               SecurityEvent\n",
    "                | where EventID in (4624,4625) | where IpAddress == \\'{ip_address}\\' or Computer == \\'{hostname}\\'  \n",
    "                | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "                | extend DayofWeek = case(dayofweek(TimeGenerated) == time(1.00:00:00), \"Monday\", \n",
    "                                    dayofweek(TimeGenerated) == time(2.00:00:00), \"Tuesday\",\n",
    "                                    dayofweek(TimeGenerated) == time(3.00:00:00), \"Wednesday\",\n",
    "                                    dayofweek(TimeGenerated) == time(4.00:00:00), \"Thursday\",\n",
    "                                    dayofweek(TimeGenerated) == time(5.00:00:00), \"Friday\",\n",
    "                                    dayofweek(TimeGenerated) == time(6.00:00:00), \"Saturday\",\n",
    "                                    \"Sunday\")\n",
    "                | summarize LogonCount=count() by DayofWeek, HourOfDay=format_datetime(bin(TimeGenerated,1h),'HH:mm')\n",
    "            \"\"\".format(\n",
    "                **ipaddr_query_params(), hostname=hostname\n",
    "            )\n",
    "            failed_logons_df = qry_prov.exec_query(failed_logons)\n",
    "\n",
    "    elif ip_entity['OSType'] =='Linux':\n",
    "        if \"Syslog\" not in available_datasets:\n",
    "            raise ValueError(\"No Linux syslog data available in the workspace\")\n",
    "        else:   \n",
    "            failed_logons_df = qry_prov.LinuxSyslog.user_logon(invest_times, account_name ='', add_query_items=\"\"\"| where HostIP == '{ipaddr_text.value}' |extend Account = AccountName | extend DayofWeek = case(dayofweek(TimeGenerated) == time(1.00:00:00), \"Monday\", dayofweek(TimeGenerated) == time(2.00:00:00), \"Tuesday\",\n",
    "                                    dayofweek(TimeGenerated) == time(3.00:00:00), \"Wednesday\",\n",
    "                                    dayofweek(TimeGenerated) == time(4.00:00:00), \"Thursday\",\n",
    "                                    dayofweek(TimeGenerated) == time(5.00:00:00), \"Friday\",\n",
    "                                    dayofweek(TimeGenerated) == time(6.00:00:00), \"Saturday\", \"Sunday\") | summarize LogonCount=count() by DayofWeek, HourOfDay=format_datetime(bin(TimeGenerated,1h),'HH:mm')\"\"\")\n",
    "\n",
    "    # Plotting hearmap using seaborn library if there are failed logons\n",
    "    if len(failed_logons_df) > 0:\n",
    "        df_pivot = (\n",
    "            failed_logons_df.reset_index()\n",
    "            .pivot_table(index=\"DayofWeek\", columns=\"HourOfDay\", values=\"LogonCount\")\n",
    "            .fillna(0)\n",
    "        )\n",
    "        display(\n",
    "            Markdown(\n",
    "                f'###  <span style=\"color:blue\"> Heatmap - Weekly Failed Logon Trend :: </span>'\n",
    "            )\n",
    "        )\n",
    "        f, ax = plt.subplots(figsize=(16, 8))\n",
    "        hm1 = sns.heatmap(df_pivot, cmap=\"YlGnBu\", ax=ax)\n",
    "        plt.xticks(rotation=45)\n",
    "        plt.yticks(rotation=30)\n",
    "    else:\n",
    "        linux_logons=qry_prov.LinuxSyslog.list_logons_for_source_ip(**ipaddr_query_params())\n",
    "        failed_logons = (logon_events[logon_events['LogonResult'] == 'Failure'])\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Host Logons Timeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:49.256466Z",
     "start_time": "2020-05-15T23:05:49.190460Z"
    }
   },
   "outputs": [],
   "source": [
    "# set the origin time to the time of our alert\n",
    "try:\n",
    "    origin_time = (related_alert.TimeGenerated \n",
    "                   if recenter_wgt.value \n",
    "                   else query_times.origin_time)\n",
    "except NameError:\n",
    "    origin_time = query_times.origin_time\n",
    "    \n",
    "logon_query_times = nbwidgets.QueryTime(\n",
    "    units=\"day\",\n",
    "    origin_time=origin_time,\n",
    "    before=5,\n",
    "    after=1,\n",
    "    max_before=20,\n",
    "    max_after=20,\n",
    ")\n",
    "logon_query_times.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:05:55.096129Z",
     "start_time": "2020-05-15T23:05:52.661823Z"
    }
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\":\n",
    "    host_logons = qry_prov.WindowsSecurity.list_host_logons(\n",
    "        logon_query_times, host_name=hostname\n",
    "    )\n",
    "\n",
    "    if host_logons is not None and not host_logons.empty:\n",
    "        display(Markdown(\"### Logon timeline.\"))\n",
    "        tooltip_cols = [\n",
    "            \"TargetUserName\",\n",
    "            \"TargetDomainName\",\n",
    "            \"SubjectUserName\",\n",
    "            \"SubjectDomainName\",\n",
    "            \"LogonType\",\n",
    "            \"IpAddress\",\n",
    "        ]\n",
    "        nbdisplay.display_timeline(\n",
    "            data=host_logons,\n",
    "            group_by=\"TargetUserName\",\n",
    "            source_columns=tooltip_cols,\n",
    "            legend=\"right\", yaxis=True\n",
    "        )\n",
    "\n",
    "        display(Markdown(\"### Counts of logon events by logon type.\"))\n",
    "        display(Markdown(\"Min counts for each logon type highlighted.\"))\n",
    "        logon_by_type = (\n",
    "            host_logons[[\"Account\", \"LogonType\", \"EventID\"]]\n",
    "            .astype({'LogonType': 'int32'})\n",
    "            .merge(right=pd.Series(data=nbdisplay._WIN_LOGON_TYPE_MAP, name=\"LogonTypeDesc\"),\n",
    "                   left_on=\"LogonType\", right_index=True)\n",
    "            .drop(columns=\"LogonType\")\n",
    "            .groupby([\"Account\", \"LogonTypeDesc\"])\n",
    "            .count()\n",
    "            .unstack()\n",
    "            .rename(columns={\"EventID\": \"LogonCount\"})\n",
    "            .fillna(0)\n",
    "            .style\n",
    "            .background_gradient(cmap=\"viridis\", low=0.5, high=0)\n",
    "            .format(\"{0:0>3.0f}\")\n",
    "        )\n",
    "        display(logon_by_type)\n",
    "    else:\n",
    "        display(Markdown(\"No logon events found for host.\"))\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Failed Logons Timeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:06:01.493064Z",
     "start_time": "2020-05-15T23:05:59.580819Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "if ipaddr_origin == \"Internal\":\n",
    "    failedLogons = qry_prov.WindowsSecurity.list_host_logon_failures(\n",
    "        logon_query_times, host_name=ip_entity.hostname\n",
    "    )\n",
    "    if failedLogons.empty:\n",
    "        print(\"No logon failures recorded for this host between \",\n",
    "              f\" {logon_query_times.start} and {logon_query_times.end}\"\n",
    "            )\n",
    "    else:\n",
    "        nbdisplay.display_timeline(\n",
    "            data=host_logons.query('TargetLogonId != \"0x3e7\"'),\n",
    "            overlay_data=failedLogons,\n",
    "            alert=related_alert,\n",
    "            title=\"Logons (blue=user-success, green=failed)\",\n",
    "            source_columns=tooltip_cols,\n",
    "            height=200,\n",
    "        )\n",
    "        display(failedLogons\n",
    "     .astype({'LogonType': 'int32'})\n",
    "     .merge(right=pd.Series(data=nbdisplay._WIN_LOGON_TYPE_MAP, name=\"LogonTypeDesc\"),\n",
    "                   left_on=\"LogonType\", right_index=True)\n",
    "     [['Account', 'EventID', 'TimeGenerated',\n",
    "      'Computer', 'SubjectUserName', 'SubjectDomainName',\n",
    "       'TargetUserName', 'TargetDomainName',\n",
    "       'LogonTypeDesc','IpAddress', 'WorkstationName'\n",
    "      ]])\n",
    "else:\n",
    "    md(f'Analysis section Not Applicable since IP address owner is {ipaddr_origin}', styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-30T15:52:54.700099Z",
     "start_time": "2019-08-30T15:52:54.661189Z"
    }
   },
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Network Connection Analysis\n",
    "\n",
    "**Hypothesis:** That an attacker is remotely communicating with the host in order to compromise the host or for outbound communication to C2 for data exfiltration purposes after compromising the host.\n",
    "\n",
    "This section provides an overview of network activity to and from the host during hunting time frame, the purpose of this is for the identification of anomalous network traffic. If you wish to investigate a specific IP in detail it is recommended that to use another instance of this notebook with each IP addresses.\n",
    "\n",
    "> Note: this query can return a lot of data for active hosts\n",
    "> If your query times out, try reducing the time range, breaking the analysis\n",
    "> into chunks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Network Check Communications with Other Hosts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:06:06.486183Z",
     "start_time": "2020-05-15T23:06:06.429184Z"
    }
   },
   "outputs": [],
   "source": [
    "ip_q_times = nbwidgets.QueryTime(\n",
    "    label=\"Set time bounds for network queries\",\n",
    "    units=\"hour\",\n",
    "    max_before=120,\n",
    "    before=5,\n",
    "    after=5,\n",
    "    max_after=60,\n",
    "    origin_time=logon_query_times.origin_time\n",
    ")\n",
    "ip_q_times.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Query Flows by IP Address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:06:22.160247Z",
     "start_time": "2020-05-15T23:06:10.292782Z"
    }
   },
   "outputs": [],
   "source": [
    "if \"AzureNetworkAnalytics_CL\" not in available_datasets:\n",
    "    md_warn(\"No network flow data available.\")\n",
    "    md(\"Please skip the remainder of this section and go to [Time-Series-Anomalies](#Outbound-Data-transfer-Time-Series-Anomalies)\")\n",
    "    az_net_comms_df = None\n",
    "else:\n",
    "    all_host_ips = (\n",
    "        ip_entity['private_ips'] + ip_entity['public_ips']\n",
    "    )\n",
    "    host_ips = [i.Address for i in all_host_ips]\n",
    "\n",
    "    az_net_comms_df = qry_prov.Network.list_azure_network_flows_by_ip(\n",
    "        ip_q_times, ip_address_list=host_ips\n",
    "    )\n",
    "\n",
    "    if isinstance(az_net_comms_df, pd.DataFrame) and not az_net_comms_df.empty:\n",
    "        az_net_comms_df['TotalAllowedFlows'] = az_net_comms_df['AllowedOutFlows'] + az_net_comms_df['AllowedInFlows']\n",
    "        nbdisplay.display_timeline(\n",
    "            data=az_net_comms_df,\n",
    "            group_by=\"L7Protocol\",\n",
    "            title=\"Network Flows by Protocol\",\n",
    "            time_column=\"FlowStartTime\",\n",
    "            source_columns=[\"FlowType\", \"AllExtIPs\", \"L7Protocol\", \"FlowDirection\"],\n",
    "            height=300,\n",
    "            legend=\"right\",\n",
    "            yaxis=True\n",
    "        )\n",
    "        nbdisplay.display_timeline(\n",
    "            data=az_net_comms_df,\n",
    "            group_by=\"FlowDirection\",\n",
    "            title=\"Network Flows by Direction\",\n",
    "            time_column=\"FlowStartTime\",\n",
    "            source_columns=[\"FlowType\", \"AllExtIPs\", \"L7Protocol\", \"FlowDirection\"],\n",
    "            height=300,\n",
    "            legend=\"right\",\n",
    "            yaxis=True\n",
    "        )\n",
    "    else:\n",
    "        md_warn(\"No network data for specified time range.\")\n",
    "        md(\"Please skip the remainder of this section and go to [Time-Series-Anomalies](#Outbound-Data-transfer-Time-Series-Anomalies)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:06:50.373391Z",
     "start_time": "2020-05-15T23:06:50.084392Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    flow_plot = nbdisplay.display_timeline_values(\n",
    "        data=az_net_comms_df,\n",
    "        group_by=\"L7Protocol\",\n",
    "        source_columns=[\"FlowType\", \n",
    "                      \"AllExtIPs\", \n",
    "                      \"L7Protocol\", \n",
    "                      \"FlowDirection\", \n",
    "                      \"TotalAllowedFlows\"],\n",
    "        time_column=\"FlowStartTime\",\n",
    "        y=\"TotalAllowedFlows\",\n",
    "        legend=\"right\",\n",
    "        height=500,\n",
    "        kind=[\"vbar\", \"circle\"],\n",
    "    );\n",
    "except NameError as err:\n",
    "    md(f\"Error Occured, Make sure to execute previous cells in notebook: {err}\",styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:06:55.928554Z",
     "start_time": "2020-05-15T23:06:55.790553Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    if az_net_comms_df is not None and not az_net_comms_df.empty:\n",
    "        cm = sns.light_palette(\"green\", as_cmap=True)\n",
    "\n",
    "        cols = [\n",
    "            \"VMName\",\n",
    "            \"VMIPAddress\",\n",
    "            \"PublicIPs\",\n",
    "            \"SrcIP\",\n",
    "            \"DestIP\",\n",
    "            \"L4Protocol\",\n",
    "            \"L7Protocol\",\n",
    "            \"DestPort\",\n",
    "            \"FlowDirection\",\n",
    "            \"AllExtIPs\",\n",
    "            \"TotalAllowedFlows\",\n",
    "        ]\n",
    "        flow_index = az_net_comms_df[cols].copy()\n",
    "\n",
    "        def get_source_ip(row):\n",
    "            if row.FlowDirection == \"O\":\n",
    "                return row.VMIPAddress if row.VMIPAddress else row.SrcIP\n",
    "            else:\n",
    "                return row.AllExtIPs if row.AllExtIPs else row.DestIP\n",
    "\n",
    "        def get_dest_ip(row):\n",
    "            if row.FlowDirection == \"O\":\n",
    "                return row.AllExtIPs if row.AllExtIPs else row.DestIP\n",
    "            else:\n",
    "                return row.VMIPAddress if row.VMIPAddress else row.SrcIP\n",
    "    \n",
    "        flow_index[\"source\"] = flow_index.apply(get_source_ip, axis=1)\n",
    "        flow_index[\"dest\"] = flow_index.apply(get_dest_ip, axis=1)\n",
    "        display(flow_index)\n",
    "\n",
    "        # Uncomment to view flow_index results\n",
    "    #     with warnings.catch_warnings():\n",
    "    #         warnings.simplefilter(\"ignore\")\n",
    "    #         display(\n",
    "    #             flow_index[\n",
    "    #                 [\"source\", \"dest\", \"L7Protocol\", \"FlowDirection\", \"TotalAllowedFlows\"]\n",
    "    #             ]\n",
    "    #             .groupby([\"source\", \"dest\", \"L7Protocol\", \"FlowDirection\"])\n",
    "    #             .sum()\n",
    "    #             .reset_index()\n",
    "    #             .style.bar(subset=[\"TotalAllowedFlows\"], color=\"#d65f5f\")\n",
    "    #         )\n",
    "except NameError as err:\n",
    "    md(f\"Error Occured, Make sure to execute previous cells in notebook: {err}\",styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Bulk whois lookup "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:08:00.744206Z",
     "start_time": "2020-05-15T23:07:01.493951Z"
    }
   },
   "outputs": [],
   "source": [
    "# Bulk WHOIS lookup function\n",
    "from functools import lru_cache\n",
    "from ipwhois import IPWhois\n",
    "from ipaddress import ip_address\n",
    "\n",
    "try:\n",
    "    # Add ASN informatio from Whois\n",
    "    flows_df = (\n",
    "        flow_index[[\"source\", \"dest\", \"L7Protocol\", \"FlowDirection\", \"TotalAllowedFlows\"]]\n",
    "        .groupby([\"source\", \"dest\", \"L7Protocol\", \"FlowDirection\"])\n",
    "        .sum()\n",
    "        .reset_index()\n",
    "    )\n",
    "\n",
    "    num_ips = len(flows_df[\"source\"].unique()) + len(flows_df[\"dest\"].unique())\n",
    "    print(f\"Performing WhoIs lookups for {num_ips} IPs \", end=\"\")\n",
    "    #flows_df = flows_df.assign(DestASN=\"\", DestASNFull=\"\", SourceASN=\"\", SourceASNFull=\"\")\n",
    "    flows_df[\"DestASN\"] = flows_df.apply(lambda x: get_whois_info(x.dest, True), axis=1)\n",
    "    flows_df[\"SourceASN\"] = flows_df.apply(lambda x: get_whois_info(x.source, True), axis=1)\n",
    "    print(\"done\")\n",
    "\n",
    "    # Split the tuple returned by get_whois_info into separate columns\n",
    "    flows_df[\"DestASNFull\"] = flows_df.apply(lambda x: x.DestASN[1], axis=1)\n",
    "    flows_df[\"DestASN\"] = flows_df.apply(lambda x: x.DestASN[0], axis=1)\n",
    "    flows_df[\"SourceASNFull\"] = flows_df.apply(lambda x: x.SourceASN[1], axis=1)\n",
    "    flows_df[\"SourceASN\"] = flows_df.apply(lambda x: x.SourceASN[0], axis=1)\n",
    "\n",
    "    our_host_asns = [get_whois_info(ip.Address)[0] for ip in ip_entity.public_ips]\n",
    "    md(f\"Host {ip_entity.hostname} ASNs:\", \"bold\")\n",
    "    md(str(our_host_asns))\n",
    "\n",
    "    flow_sum_df = flows_df.groupby([\"DestASN\", \"SourceASN\"]).agg(\n",
    "        TotalAllowedFlows=pd.NamedAgg(column=\"TotalAllowedFlows\", aggfunc=\"sum\"),\n",
    "        L7Protocols=pd.NamedAgg(column=\"L7Protocol\", aggfunc=lambda x: x.unique().tolist()),\n",
    "        source_ips=pd.NamedAgg(column=\"source\", aggfunc=lambda x: x.unique().tolist()),\n",
    "        dest_ips=pd.NamedAgg(column=\"dest\", aggfunc=lambda x: x.unique().tolist()),\n",
    "    ).reset_index()\n",
    "    display(flow_sum_df)\n",
    "except NameError as err:\n",
    "    md(f\"Error Occured, Make sure to execute previous cells in notebook: {err}\",styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Choose ASNs/IPs to Check for Threat Intel Reports\n",
    "Choose from the list of Selected ASNs for the IPs you wish to check on.\n",
    "The Source list is been pre-populated with all ASNs found in the network flow summary.\n",
    "\n",
    "As an example, we've populated the `Selected` list with the ASNs that have the lowest number of flows to and from the host. We also remove the ASN that matches the ASN of the host we are investigating.\n",
    "\n",
    "Please edit this list, using flow summary data above as a guide and leaving only ASNs that you are suspicious about. Typicially these would be ones with relatively low `TotalAllowedFlows` and possibly with unusual `L7Protocols`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:08:01.347207Z",
     "start_time": "2020-05-15T23:08:01.287206Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    if isinstance(flow_sum_df, pd.DataFrame) and not flow_sum_df.empty:\n",
    "        all_asns = list(flow_sum_df[\"DestASN\"].unique()) + list(flow_sum_df[\"SourceASN\"].unique())\n",
    "        all_asns = set(all_asns) - set([\"private address\"])\n",
    "\n",
    "        # Select the ASNs in the 25th percentile (lowest number of flows)\n",
    "        quant_25pc = flow_sum_df[\"TotalAllowedFlows\"].quantile(q=[0.25]).iat[0]\n",
    "        quant_25pc_df = flow_sum_df[flow_sum_df[\"TotalAllowedFlows\"] <= quant_25pc]\n",
    "        other_asns = list(quant_25pc_df[\"DestASN\"].unique()) + list(quant_25pc_df[\"SourceASN\"].unique())\n",
    "        other_asns = set(other_asns) - set(our_host_asns)\n",
    "        md(\"Choose IPs from Selected ASNs to look up for Threat Intel.\", \"bold\")\n",
    "        sel_asn = nbwidgets.SelectSubset(source_items=all_asns, default_selected=other_asns)\n",
    "except NameError as err:\n",
    "    md(f\"Error Occured, Make sure to execute previous cells in notebook: {err}\",styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:08:14.516746Z",
     "start_time": "2020-05-15T23:08:01.935205Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    if isinstance(flow_sum_df, pd.DataFrame) and not flow_sum_df.empty:\n",
    "        ti_lookup = TILookup()\n",
    "        from itertools import chain\n",
    "        dest_ips = set(chain.from_iterable(flow_sum_df[flow_sum_df[\"DestASN\"].isin(sel_asn.selected_items)][\"dest_ips\"]))\n",
    "        src_ips = set(chain.from_iterable(flow_sum_df[flow_sum_df[\"SourceASN\"].isin(sel_asn.selected_items)][\"source_ips\"]))\n",
    "        selected_ips = dest_ips | src_ips\n",
    "        print(f\"{len(selected_ips)} unique IPs in selected ASNs\")\n",
    "\n",
    "        # Add the IoCType to save cost of inferring each item\n",
    "        selected_ip_dict = {ip: \"ipv4\" for ip in selected_ips}\n",
    "        ti_results = ti_lookup.lookup_iocs(data=selected_ip_dict)\n",
    "\n",
    "        print(f\"{len(ti_results)} results received.\")\n",
    "\n",
    "        # ti_results_pos = ti_results[ti_results[\"Severity\"] > 0]\n",
    "        #####\n",
    "        # WARNING - faking results for illustration purposes\n",
    "        #####\n",
    "        ti_results_pos = ti_results.sample(n=2)\n",
    "\n",
    "        print(f\"{len(ti_results_pos)} positive results found.\")\n",
    "\n",
    "\n",
    "        if not ti_results_pos.empty:\n",
    "            src_pos = flows_df.merge(ti_results_pos, left_on=\"source\", right_on=\"Ioc\")\n",
    "            dest_pos = flows_df.merge(ti_results_pos, left_on=\"dest\", right_on=\"Ioc\")\n",
    "            ti_ip_results = pd.concat([src_pos, dest_pos])\n",
    "            md_warn(\"Positive Threat Intel Results found for the following flows\")\n",
    "            md(\"Please examine these IP flows using the IP Explorer notebook.\", \"bold, large\")\n",
    "            display(ti_ip_results)\n",
    "except NameError as err:\n",
    "    md(f\"Error Occured, Make sure to execute previous cells in notebook: {err}\",styles=[\"bold\",\"red\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ### GeoIP Map of External IPs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:08:16.023912Z",
     "start_time": "2020-05-15T23:08:15.611915Z"
    }
   },
   "outputs": [],
   "source": [
    "iplocation = GeoLiteLookup()\n",
    "def format_ip_entity(row, ip_col):\n",
    "    ip_entity = entities.IpAddress(Address=row[ip_col])\n",
    "    iplocation.lookup_ip(ip_entity=ip_entity)\n",
    "    ip_entity.AdditionalData[\"protocol\"] = row.L7Protocol\n",
    "    if \"severity\" in row:\n",
    "        ip_entity.AdditionalData[\"threat severity\"] = row[\"severity\"]\n",
    "    if \"Details\" in row:\n",
    "        ip_entity.AdditionalData[\"threat details\"] = row[\"Details\"]\n",
    "    return ip_entity\n",
    "\n",
    "# from msticpy.nbtools.foliummap import FoliumMap\n",
    "folium_map = FoliumMap()\n",
    "if az_net_comms_df is None or az_net_comms_df.empty:\n",
    "    print(\"No network flow data available.\")\n",
    "else:\n",
    "    # Get the flow records for all flows not in the TI results\n",
    "    selected_out = flows_df[flows_df[\"DestASN\"].isin(sel_asn.selected_items)]\n",
    "    selected_out = selected_out[~selected_out[\"dest\"].isin(ti_ip_results[\"Ioc\"])]\n",
    "    if selected_out.empty:\n",
    "        ips_out = []\n",
    "    else:\n",
    "        ips_out = list(selected_out.apply(lambda x: format_ip_entity(x, \"dest\"), axis=1))\n",
    "    \n",
    "    selected_in = flows_df[flows_df[\"SourceASN\"].isin(sel_asn.selected_items)]\n",
    "    selected_in = selected_in[~selected_in[\"source\"].isin(ti_ip_results[\"Ioc\"])]\n",
    "    if selected_in.empty:\n",
    "        ips_in = []\n",
    "    else:\n",
    "        ips_in = list(selected_in.apply(lambda x: format_ip_entity(x, \"source\"), axis=1))\n",
    "\n",
    "    ips_threats = list(ti_ip_results.apply(lambda x: format_ip_entity(x, \"Ioc\"), axis=1))\n",
    "\n",
    "    display(HTML(\"<h3>External IP Addresses communicating with host</h3>\"))\n",
    "    display(HTML(\"Numbered circles indicate multiple items - click to expand\"))\n",
    "    display(HTML(\"Location markers: <br>Blue = outbound, Purple = inbound, Green = Host, Red = Threats\"))\n",
    "\n",
    "    icon_props = {\"color\": \"green\"}\n",
    "    for ips in ip_entity.public_ips:\n",
    "        ips.AdditionalData[\"host\"] = ip_entity.hostname\n",
    "    folium_map.add_ip_cluster(ip_entities=ip_entity.public_ips, **icon_props)\n",
    "    icon_props = {\"color\": \"blue\"}\n",
    "    folium_map.add_ip_cluster(ip_entities=ips_out, **icon_props)\n",
    "    icon_props = {\"color\": \"purple\"}\n",
    "    folium_map.add_ip_cluster(ip_entities=ips_in, **icon_props)\n",
    "    icon_props = {\"color\": \"red\"}\n",
    "    folium_map.add_ip_cluster(ip_entities=ips_threats, **icon_props)\n",
    "    \n",
    "    display(folium_map)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-05T18:03:37.980223Z",
     "start_time": "2019-09-05T18:03:37.804856Z"
    }
   },
   "source": [
    "<a></a>[Contents](#toc)\n",
    "### Outbound Data transfer Time Series Anomalies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section will look into the network datasources to check outbound data transfer trends. \n",
    "You can also use time series analysis using below built-in KQL query example to analyze anamalous data transfer trends.below example shows sample dataset trends comparing with actual vs baseline traffic trends."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-05-15T23:08:42.937737Z",
     "start_time": "2020-05-15T23:08:41.794266Z"
    }
   },
   "outputs": [],
   "source": [
    "if \"VMConnection\" in table_index or \"CommonSecurityLog\" in table_index:\n",
    "    # KQL query for full text search of IP address and display all datatypes\n",
    "    dataxfer_stats = \"\"\"\n",
    "      union isfuzzy=true\n",
    "      (\n",
    "      CommonSecurityLog \n",
    "      | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})\n",
    "      | where isnotempty(DestinationIP) and isnotempty(SourceIP)\n",
    "      | where SourceIP == \\'{ip_address}\\'\n",
    "      | extend SentBytesinKB = (SentBytes / 1024), ReceivedBytesinKB = (ReceivedBytes / 1024)\n",
    "      | summarize DailyCount = count(), ListOfDestPorts = make_set(DestinationPort), TotalSentBytesinKB = sum(SentBytesinKB), TotalReceivedBytesinKB = sum(ReceivedBytesinKB) by SourceIP, DestinationIP, DeviceVendor, bin(TimeGenerated,1d)\n",
    "      | project DeviceVendor, TimeGenerated, SourceIP, DestinationIP, ListOfDestPorts, TotalSentBytesinKB, TotalReceivedBytesinKB \n",
    "      ),\n",
    "      (\n",
    "      VMConnection \n",
    "      | where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end}) \n",
    "      | where isnotempty(DestinationIp) and isnotempty(SourceIp)\n",
    "      | where SourceIp == \\'{ip_address}\\'\n",
    "      | extend DeviceVendor = \"VMConnection\", SourceIP = SourceIp, DestinationIP = DestinationIp\n",
    "      | extend SentBytesinKB = (BytesSent / 1024), ReceivedBytesinKB = (BytesReceived / 1024)\n",
    "      | summarize DailyCount = count(), ListOfDestPorts = make_set(DestinationPort), TotalSentBytesinKB = sum(SentBytesinKB),TotalReceivedBytesinKB = sum(ReceivedBytesinKB) by SourceIP, DestinationIP, DeviceVendor, bin(TimeGenerated,1d)\n",
    "      | project DeviceVendor, TimeGenerated, SourceIP, DestinationIP, ListOfDestPorts, TotalSentBytesinKB, TotalReceivedBytesinKB \n",
    "      )\n",
    "    \"\"\".format(**ipaddr_query_params())\n",
    "\n",
    "    dataxfer_stats_df = qry_prov.exec_query(dataxfer_stats)\n",
    "\n",
    "#Display result as transposed matrix of datatypes availabel to query for the query period\n",
    "if len(dataxfer_stats_df) > 0:\n",
    "    md(\n",
    "            'Data transfer daily stats for IP ::', styles=[\"bold\",\"green\"]\n",
    "        )\n",
    "    display(dataxfer_stats_df)\n",
    "else:\n",
    "    md_warn(\n",
    "            f'No Data transfer logs found for the query period'\n",
    "        )\n",
    "        #####\n",
    "        # WARNING - faking results for illustration purposes\n",
    "        #####\n",
    "md(\n",
    "            'Visualizing time series data transfer on dummy dataset for demonstration ::', styles=[\"bold\",\"green\"]\n",
    "    )\n",
    "\n",
    "# Generating graph based on dummy dataset in custom table representing Flow records outbound data transfer\n",
    "timechartquery = \"\"\"\n",
    "let TimeSeriesData = PaloAltoBytesSent_CL\n",
    "| extend TimeGenerated = todatetime(EventTime_s), TotalBytesSent = todouble(TotalBytesSent_s) \n",
    "| summarize TimeGenerated=make_list(TimeGenerated, 10000),TotalBytesSent=make_list(TotalBytesSent, 10000) by deviceVendor_s\n",
    "| project TimeGenerated, TotalBytesSent;\n",
    "TimeSeriesData\n",
    "| extend (baseline,seasonal,trend,residual) = series_decompose(TotalBytesSent)\n",
    "| mv-expand TotalBytesSent to typeof(double), TimeGenerated to typeof(datetime), baseline to typeof(long), seasonal to typeof(long), trend to typeof(long), residual to typeof(long)\n",
    "| project TimeGenerated, TotalBytesSent, baseline\n",
    "| render timechart with (title=\"Palo Alto Outbound Data Transfer Time Series decomposition\")\n",
    "\"\"\"\n",
    "%kql -query timechartquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of Suspicious Activities/ Observables/Hunting bookmarks\n",
    "- Suspicious alerts for the IP\n",
    "- Anamalous Failed Logon trend on few days at 04:00 AM\n",
    "- Anamalous spike in traffic logs on http\n",
    "- Positive TI Hit from Open source feeds.\n",
    "- Unusual data transfer deviating from normal baseline."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a></a>[Contents](#toc)\n",
    "## Appendices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Available DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-04-02T10:00:41.436112Z",
     "start_time": "2020-04-02T10:00:41.426605Z"
    }
   },
   "outputs": [],
   "source": [
    "print('List of current DataFrames in Notebook')\n",
    "print('-' * 50)\n",
    "current_vars = list(locals().keys())\n",
    "for var_name in current_vars:\n",
    "    if isinstance(locals()[var_name], pd.DataFrame) and not var_name.startswith('_'):\n",
    "        print(var_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving Data to Excel\n",
    "To save the contents of a pandas DataFrame to an Excel spreadsheet\n",
    "use the following syntax\n",
    "```\n",
    "writer = pd.ExcelWriter('myWorksheet.xlsx')\n",
    "my_data_frame.to_excel(writer,'Sheet1')\n",
    "writer.save()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration\n",
    "\n",
    "### `msticpyconfig.yaml` configuration File\n",
    "You can configure primary and secondary TI providers and any required parameters in the `msticpyconfig.yaml` file. This is read from the current directory or you can set an environment variable (`MSTICPYCONFIG`) pointing to its location.\n",
    "\n",
    "To configure this file see the [ConfigureNotebookEnvironment notebook](https://github.com/Azure/Azure-Sentinel-Notebooks/blob/master/ConfiguringNotebookEnvironment.ipynb)"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3.6",
   "language": "python",
   "name": "python36"
  },
  "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.6.7"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "299px"
   },
   "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()) "
    }
   },
   "position": {
    "height": "400px",
    "left": "1549px",
    "right": "20px",
    "top": "120px",
    "width": "351px"
   },
   "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
}