tutorials-and-examples/feature-tutorials/Data_Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
    "source": [
      "# Title: msticpy - Data\n",
      "## Description:\n",
      "This package provides functions to allow for the defining of data sources, connectors to them, and queries for them as well as the ability to call these elements to return query result from the defined data sources.\n",
      "The package currently support connections to Log Analytics/Microsoft Sentinel/Azure Security Center, and the Microsoft Security Graph."
    ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first step in using this package is to install the msticpy package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting git+https://github.com/microsoft/msticpy\n",
      "Building wheels for collected packages: msticpy\n",
      "  Building wheel for msticpy (setup.py): started\n",
      "  Building wheel for msticpy (setup.py): finished with status 'done'\n",
      "Successfully built msticpy\n",
      "Installing collected packages: msticpy\n",
      "Successfully installed msticpy-0.2.1\n"
     ]
    }
   ],
   "source": [
    "%pip install --upgrade msticpy[azsentinel]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='contents'></a>\n",
    "## Table of Contents\n",
    "* [Instantiating a Query Provider](#instantiating)\n",
    "* [Connecting to a Data Environment](#connecting)\n",
    "* [Reviewing available queries](#review_queries)\n",
    "* [Running a pre-defined query](#run_query)\n",
    "* [Running an ad-hoc query](#run_adhoc)\n",
    "* [Creating a new set of queries](#new)\n",
    "* [Adding a new set of queries and running them](#add_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Imports Complete\n"
     ]
    }
   ],
   "source": [
    "#Check we are running Python 3.6\n",
    "import sys\n",
    "MIN_REQ_PYTHON = (3,6)\n",
    "if sys.version_info < MIN_REQ_PYTHON:\n",
    "    print('Check the Kernel->Change Kernel menu and ensure that Python 3.6')\n",
    "    print('or later is selected as the active kernel.')\n",
    "    sys.exit(\"Python %s.%s or later is required.\\n\" % MIN_REQ_PYTHON)\n",
    "\n",
    "#imports\n",
    "import yaml\n",
    "import msticpy.nbtools as nbtools\n",
    "\n",
    "#data library imports\n",
    "from msticpy.data.data_providers import QueryProvider\n",
    "import msticpy.data.data_query_reader as QueryReader\n",
    "from msticpy.data.param_extractor import extract_query_params\n",
    "import msticpy.nbtools as mas\n",
    "\n",
    "print('Imports Complete')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='instantiating'></a>\n",
    "### Instantiating a Query Provider\n",
    "In order to connect to and query a data source we need to define what sort of Data Environment we want to connect to and query (in this Notebook we will use Log Analytics as an example). To view the options available you can call <code>QueryProvider.list_data_environments()</code> which will return a list of all the available options.\n",
    "\n",
    "    \n",
    "After selecting a Data Environment we can initialize our Query Provider by calling <code>QueryProvider(DATA_ENVIRONMENT)</code>. This will load the relavent driver for connecting to the data environment we have selected as well as provisioning a query store for us and adding queries from our default query directory. \n",
    "\n",
    "There are two other optional parameters we can pass when initializing our Query Providers to further customize it:\n",
    "* We can also chose to initialize our Query Provider with a driver other than the defualt one with <code>QueryProvider(data_environment=DATA_ENVIRONMENT, driver=QUERY_DRIVER)</code>\n",
    "* We can choose to import queries from a custom query directory (see - [Creating a new set of queries](#new) for more details) with <code>QueryProvider(data_environment=DATA_ENVIRONMENT, driver=QUERY_DRIVER, query_path=QUERY_DIRECTORY_PATH)</code>. \n",
    "\n",
    "For now we will simply create a Query Provider with default values.\n",
    "\n",
    "\n",
    "    Query provider interface to queries.\n",
    "\n",
    "        Parameters\n",
    "        ----------\n",
    "        data_environment : Union[str, DataEnvironment]\n",
    "            Name or Enum of environment for the QueryProvider\n",
    "        driver : DriverBase, optional\n",
    "            Override the built-in driver (query execution class)\n",
    "            and use your own driver (must inherit from\n",
    "            `DriverBase`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['LogAnalytics', 'Kusto', 'AzureSecurityCenter', 'SecurityGraph']\n",
      "Please wait. Loading Kqlmagic extension...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<html>\n",
       "            <head>\n",
       "            <style>\n",
       "            .kql-magic-banner {\n",
       "                display: flex; \n",
       "                background-color: #d9edf7;\n",
       "            }\n",
       "            .kql-magic-banner > div {\n",
       "                margin: 10px; \n",
       "                padding: 20px; \n",
       "                color: #3a87ad; \n",
       "                font-size: 13px;\n",
       "            }\n",
       "            </style>\n",
       "            </head>\n",
       "            <body>\n",
       "                <div class='kql-magic-banner'>\n",
       "                    <div><img src='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALsAAACcCAYAAAAnOdrkAAAQeklEQVR42u2di5MVxRXGe5cFFhEQQcGVx4KgLg8XLFCDCigqkvgCSVQqIgRNTP6R/AcmMRpjaWLQEEWRCGrEIKXIGxUMIvJUkKcisryWfB9zVm8w1Hbfnefe76vq6ru70zO9c3/Tc6bnnNM14347z0lSJahGp0AS7JIk2CVJsEuSYJckwS5Jgl2SBLskCXZJEuySJNglwS5Jgl2SBLskVQbsp0+fvgRVT8/Nv66qqtqp0y6Bm1pUdSi1HpsfR9kDdg5nBjs63A/Vb1DGeDZZjzZPotMb9XVXNOhVqKag3G3At6Z9KK+h3fNgpymrkf0ilInowI88/0luvwhFsFe2uqIMR7kD7PTy4OYIqt0EHiUz2Dt43oZaxG2r9V1XvDiyd0Tp7Ll9F5RO1k4PqJIk2CVJsEuCXZIEuyQJdkkS7JIk2CVJsEuSYJckwS5Jgl2SBLsk2CVJsEuSYJckwS5Jgl2SBLskCXZJEuySJNjbiSyJUA8XpZ1gFP7ZkfUnUI66KPHU1zpjFQ47gGG6htoymjYBoBMZwF1npT/KAKsvRunmorQl323uovwpe1E+R9ttqDehbEfZhb6fDDxuR49NT2G/3wr2fILeiOp6g8U33wgh4ii5Bu2XhkDThn72cVHCIGZVG41ymfWZo3oP9KGqlfZMIHSQ0Lso+dT7+N37/IymR1ppy2MywVVvj65+he1XYJ9LBXu+QCfkc1AmoVwY0JS5BJcZKCcT7iNzZI5FmYgyDmUojnlh6H7QpquZOkxFeA32exvqdShv4fObvHCxTfM5RnSC/gjKII9DMSPX+Wj3IfZ3ULDnA/RrUf0SZTq+lPMC225B9TZHxwT7180gu91AH45+dopr/9gXL6JLcJyrUV+H8jI+L8bvPz9r0442og/C38736Pdgu+N0KiobNe0MdJoBD6NMKwP09aj+iPIc2u5PqH8NqO60MhbH6ZzUucC+CeZUHPNK1JejnovfrdMDavsAfZTdkqf7jFRpgm5ZazmKz0D5iY2+qQjHasDxCX5f1E8V2eYW7P8L+v34Mi/IGegcvX+MMhtlcpwmSwDwvdCPGWZzV+Pnt10bk4QK9mxBfwBfYs/AtqtQ/QllbkKg05S6h6YV9n9TlueJJhP6w3zoTTaD87lgLxbojW0AfTmqP6C8iLaHEjJdOKI/iv3fGNM+Obd+quUBM/Quwe0NeF7Yi51/ymjBnjHofNibY6ZLKOjvGujz0PabhLo4AeWhtoCOfrJvfGH0mY3EnPI7ZibIefh7X/f9y6dLcayOHsB3R7tpbI/S3VXQW/SagoI+FNUslPtC56bTAN36dz/KrWW2/xLVGpT3UFaibDHQObKfdN8n8+eDOB8+ebwxaMepxsbWZqLw9wE2wh/B51rBnm/QOaI/aNNreQOdo+VdNGHKmVq0Ps5H+RfKx60smrXfRv4VaPcGQecFhs+c8RnWCvB9ZLMXA/SZodN3KZku1ESUe3GM/oH9I9QLOStE0ENXhsP2vBu8zjecqD9C/QDqSfi9nP2KBnsRQMdxhtjsy3VlmC0voDyN/q1o46zLF9jfX8zGP0BzJfQFm2DPFnS+qp6d8xGdok/O+Nact87q3yEbzR9Hsw1xdML8ejjK00uxGfX0JN/WCvb4QKc58HOWPIOOY9Fb8WYc57LAplzu8Nm4QD8L+mV8iYSPnGO/N+QiFOzpg05PvpkumsLrn1fQTfTLGRvYR3pY/hX9W5lUp+gegONw1oazVjcL9vyC/iDNF3xhg/MMuvmk04QZEtBmB6q/26xL0uIxON04sIw7j2BPCfQ5oV9OBiM6Rc/Ca3xe6lgfGSTCqcKFrQVYxDS6H8MhX8fHRtSPVOoMTY1Ab3N/+cqevuOXBzTj9OCr6OOmtM4rjrUFfV3kIl/6UYI9e9BpDtA77xdlgE5PPnovzk9xRKf4LEHf9N4BbeiXszKDU8xpzXdwrkZU4uhekyPQe6H6GcosfBFDAtsy/Oz3KAvQtinlrvN5oiGgr5z/Xo5+bkv7HDNaCcd/Cx/pgTlcsGcHOt/40RW2oQzQH0N5JYOMANVmvtQFNNuKsiHD081opdWCPVvQ+eB0VVFAN12EcoXzi85v0acouzI85Uy3sRrnjr47vQS7QPcV564H4/jVnn2mv8snLnLgykQ8V+gH7yw7UQR7SqDTB/2+AoNOXRxowhDyzSk/QJ9rdKc51SjYk1VHCwC+02z0UNDXuiiULmvQKQZPhASOfGmgZS1mEtuMc3k8i5jYSoGdxxvoouk6ugCMLmMfvP1uzBp0S61HX52QTAZ7sjRhSsRAEEY/HbK7k2BP6LZ/B0dEwFruiw1OS47kCI99nM7w3HUxmzfEfXYfStYmDO12ekJ+4aIMaII9oZPMKJ7b27iPK/FF0Wf8Y5dg5i5P2Hta7XMnOI7qgIsy7+ZBe/Nw4VXEA2obxZciG5i9FvDvyagP9A/vHuA2e8zMhqacnMPDVgR7nsVESACdD7j0MflbRt3oGGjCEPavrM6DmgR7cYC/iikhUBiUvLYAsNOM+SaNNNgBsH8l2ItlznwA4LcDogMpH7uDC0sydDJHo/p3F59gL87o3hugM20F3wjOy2Bkry0w7MwsRj/3Zt83wII9RvF1Ok58t0DgmRxoqpkzH6U8sncKhL0pR6e75eI76Qqcc72QsANWho5xiZRRgDZ0enKSmTM7UlxMq8qFZcJtNrDyNLIfFezpg97i68J58/FcggXQevttYNu+ls6NI/uCFGEJgbeD81uoK03YT9lFKJs9JdAZG8nAi1fMI4+xmUygPyAwYSmXl7kH7TalFO5G0ENWjyPoXXL03bdcfNWCPXnI+aqf6d4YSrewxdeFadzwp5ddFFwwNWB0r0a7yS5K/bYrhUDm0AfODq68ZSqT/O675Oxu0/5g5wwAR3Ib0Rf9Hx8Xxme+xPWAQiKXsG0/Pqy6aHZmUcL/xonAkb0mZ7CfudP4ZkQQ7G0D/TGc6MXngPa0mTMjmBEscI0k5lm8G+3oN/5pgv8KZ1ZClkjkQyCXeemEfh3PyXdfMemqsxrZOeL+7lyglwDP4GCmbh6BMiVgdKe//BQzZ55gzpSE/g+O6vtxjGOeeRQJFtd74rZ5gP3MxSfYkxvVd9uo/qZnk/fNnLk8JLUGtq0vmZ1ZkiTsVvvAXuolmQeflDOObII9OdHrb6uvf4jNzvAOMNIyWYW8nr/RZme2oF3s0UFM2YF90+PyiPOIVrIH6F45mpHp5sKirAR7GWoOhGorIHnJZmduCmhXyxUozJz5c0KRTQyAoDNVP8/tmQSqR06++94a2fMpprV7kWmhuR5QAPBDSmZnliXQL8aUhjignVl8F2V9Ds5pX9nsORSg/RbQvmbmzOzA1G032uzMZ3zoTcAs2xkIOy/ANxgal9X5xPFpwvTXyJ5f4D+x2RmaM+MC2nG6j3GvH6J+Jua4VY7qjNLnqnNdPbZnUqUGq/dkeDppq9eHLnsv2NPVUpSXuewM/WECgG+wuNWNLkruGdcF+DX2y30ynrOrx/bVtn5rXcawX+oCcskL9mxGd8K1wEb3BwObT7SH1W22slxc2uyihEP1ntufyY7gonVOsxLzyQ8Q7PkHnsBy7r2BvuwB7Xpa3Crn3uOMW+W0Jt2Lx/sEQWCbgdj2epQlSUyJetjrtNXpNNdPsBdDSzi644urD8mLTrdhmjNxxq0yuwH2t8JGd9/lcOjSMNplkx2MCydcV4mLiRU1u8ABM2doDvw0sPkke1iNM251nd0xfGGn3X4L+rAafdiR4qheZ///MFeBKnJ2gTVmztAzcmRAu97mSsC593/EaLdzRYtxPmmgzX+H0NEd4pkUTxuXmJlQSZ6O7QJ2E0P5RtgqcN0DgB9j5sx/4ohbtfcAXOaGSy9O9mzTYLGzDDZZnsKoztH8DrsbOsFevNF9d4ln5J2BzW+12Zm44lZpyizC/hoDpkVvQdmGNnu5wFeCoNNNYRovxEpe+Lc9LCLFh0O6EgxlHsiACyXWuFVzDOOdhi+8pnu26cZET/jIh9ynkkjlh/12N9AfCF0hXLDnb3RvtjjWlkCPrgHNOQV3d4xxq/R5WWCr0V3p2X8uxstVvI+jfg4/fxEj6PSfv9dFCycPcxWudrE8IL7InSVxq5MD2rXErW6II27VIqw4ujeGXHhmvz+Kj91Rz8XPG2MAfYCN6DPLzIMv2HOs91DmmzkzOADQ/iXmzOIYLrwd2B9neRhscldAu6Fo9yt8rEPNZd6XlXPx2SLEfNlG94hplbx8e7uF3ZYsX2TmzMOBy6dwSo6zM5/GFLdKd+I+2F9v7C/EaY3PEQ+5aLnJhTbDQxProAfkdOqiK8INLsqBPz40u5pgLxbwW0o8IycEtOtkcat82fRkW+NWzZyhSzJdFM4LWWXELtIJaDfcZmtW4jNnevi2lS7FjF9lcqNq+/74AErHLq5NdQ3K1dhHndBu57CXjKrzLdCjXwBk9SUvm5bEcOEdMXOmC+pfhy5mbG4Qt9KPxkVBIvSsZMwrM+8yrJF5aBjix+Up6TLcVyN5hcFukP3TRYEeswLnlW+w2ZlY4lbNreEFA56m1dAy9sEc8PXO36vS17ZnIAtXy6sX7MUG/mN8mS+6yAfk2hCwzopbPRlDX/ji61mOxpxiDMlhmZRsec3X7WGYd4RawV5sLTVzhp6RfQJnRVoCPZbFdPExB87T+HiQD6D4eUKGoNOP/gmUVbyLuXxlFhbsZQJ2CF/sqy5yJZgR2HyCje5b4nrJg/0wodJztL05p++iV/e9UoScD7ac3eFd5hV7wD3hKkjteWQnYOtpzligx+iAdqVxq8/GFbdKlwIXvWGlW+8nZjKNCgwgLwf0bag4O8Tnh6VMv2dB13pAbWd6yx5W60NSYPP1uqXh4HqrK2K+CNdh38xKsJojPD4zA8IVcS+tjv3ut77zDvca9r9ZszHli6nf+MbQd5aBMxypLlpl5gNdCQahvs15BEabmOqC89VclW9d3MlI2S8XBY/zYZHpAMfhM6OI+GLo0nLXOcI+aINzJP/InjlouqxNMOdlOaL5xNW+P0N/B3lsz7SJnH49niXsvB3P5cn03J5urKmPLviiV+GkPu6iIOcLAi9mjsCnE+wbB4Dt6N+/XeRiwGeMRvM/b8ntwunHH6SXtozIXCqG5tEh+z74AopBIR/QVDLTKW/mJT1E37UffcIqmXVthc+b5MRgx8EPo9PPO//8hU0JpaHz6es7FisaaiocTWPtUhxjn412y83/vM5gp1suXxr1NDub4FfZKHfYID9gF+VWXjgxZ09I6v/lm2Eu2uwTNXWKATKZ2+wGQiFWSrZb+bEC9JN+7SwMPawywDkX3rkEjmb7X5rsgizczIrddVK781TCA2rRZ5RoQh2xIgl2SRLskiTYJcEuSYJdkgS7JAl2SRLskiTYJUmwS5JglyTBLkmCXRLskiTYJUmwS5JglyTBLkmCXZIEuyQJdkkS7JIk2KUfiEmWmFZuo2cKOi5ewERMxwS7VCjZgmvMA8ncmz4pAbndeqYCF+xSEYHn+lFcA9aHg1Nxpe4W7FKW0FfE6huCXdIDqiQJdkkS7JIk2CVJsEuSYJckwS5Jgl2SBLskCXZJsEuSYJckwS5JxdB/ASH5FI/5dHZAAAAAAElFTkSuQmCC'></div>\n",
       "                    <div>\n",
       "                        <p>Kql Query Language, aka kql, is the query language for advanced analytics on Azure Monitor resources. The current supported data sources are \n",
       "                        Azure Data Explorer (Kusto), Log Analytics and Application Insights. To get more information execute '%kql --help \"kql\"'</p>\n",
       "                        <p>   &bull; kql reference: Click on 'Help' tab > and Select 'kql reference' or execute '%kql --help \"kql\"'<br>\n",
       "                          &bull; Kqlmagic configuration: execute '%config Kqlmagic'<br>\n",
       "                          &bull; Kqlmagic usage: execute '%kql --usage'<br>\n",
       "                    </div>\n",
       "                </div>\n",
       "            </body>\n",
       "            </html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<html>\n",
       "        <head>\n",
       "        \n",
       "        </head>\n",
       "        <body>\n",
       "        <div><p style='padding: 10px; color: #3a87ad; background-color: #d9edf7; border-color: #bce9f1'>Kqlmagic&nbsppackage&nbspis&nbspupdated&nbspfrequently.&nbspRun&nbsp&apos;!pip&nbspinstall&nbspKqlmagic&nbsp--no-cache-dir&nbsp--upgrade&apos;&nbspto&nbspuse&nbspthe&nbsplatest&nbspversion.<br>Kqlmagic&nbspversion:&nbsp0.1.100,&nbspsource:&nbsphttps://github.com/Microsoft/jupyter-Kqlmagic</p></div>\n",
       "        </body>\n",
       "        </html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.reconnect();} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data_environments = QueryProvider.list_data_environments()\n",
    "print(data_environments)\n",
    "qry_prov = QueryProvider(data_environment='LogAnalytics')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
    "source": [
      "<a id='connecting'></a>\n",
      "### Connecting to a Data Environment\n",
      "Once we have instantiated the query provider and loaded the relevent driver we can connect to the Data Environment. This is done by calling the <code>connect()</code> function of the Query Provider we just initialized and passing it a connection string to use.\n",
      "\n",
      "For Log Analytics/Microsoft Sentinel the connection string is in the format of <code>loganalytics://code().tenant(\"TENANT_ID\").workspace(\"WORKSPACE_ID\")</code>. Other Data Environments will have different connection string formats.\n",
      "\n",
      "    connect(self, connection_str: str, **kwargs):\n",
      "       \n",
      "        Connect to data source.\n",
      "\n",
      "        Parameters\n",
      "        ----------\n",
      "        connection_string : str\n",
      "            Connection string for the data source"
    ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Workspace ID xxxxxxxxxxxxxxxxxxxxxxxxxxx\n",
      "Tenant ID xxxxxxxxxxxxxxxxxxxxxxxxxxx\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "                <html><body>\n",
       "\n",
       "                <!-- h1 id=\"user_code_p\"><b>BTYMGY4BP</b><br></h1-->\n",
       "\n",
       "                <input  id=\"kql_MagicCodeAuthInput\" type=\"text\" readonly style=\"font-weight: bold; border: none;\" size = '9' value='BTYMGY4BP'>\n",
       "\n",
       "                <button id='kql_MagicCodeAuth_button', onclick=\"this.style.visibility='hidden';kql_MagicCodeAuthFunction()\">Copy code to clipboard and authenticate</button>\n",
       "\n",
       "                <script>\n",
       "                var kql_MagicUserCodeAuthWindow = null\n",
       "                function kql_MagicCodeAuthFunction() {\n",
       "                    /* Get the text field */\n",
       "                    var copyText = document.getElementById(\"kql_MagicCodeAuthInput\");\n",
       "\n",
       "                    /* Select the text field */\n",
       "                    copyText.select();\n",
       "\n",
       "                    /* Copy the text inside the text field */\n",
       "                    document.execCommand(\"copy\");\n",
       "\n",
       "                    /* Alert the copied text */\n",
       "                    // alert(\"Copied the text: \" + copyText.value);\n",
       "\n",
       "                    var w = screen.width / 2;\n",
       "                    var h = screen.height / 2;\n",
       "                    params = 'width='+w+',height='+h\n",
       "                    kql_MagicUserCodeAuthWindow = window.open('https://microsoft.com/devicelogin', 'kql_MagicUserCodeAuthWindow', params);\n",
       "\n",
       "                    // TODO: save selected cell index, so that the clear will be done on the lince cell\n",
       "                }\n",
       "                </script>\n",
       "\n",
       "                </body></html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "                    <html><body><script>\n",
       "\n",
       "                        // close authentication window\n",
       "                        if (kql_MagicUserCodeAuthWindow && kql_MagicUserCodeAuthWindow.opener != null && !kql_MagicUserCodeAuthWindow.closed) {\n",
       "                            kql_MagicUserCodeAuthWindow.close()\n",
       "                        }\n",
       "                        // TODO: make sure, you clear the right cell. BTW, not sure it is a must to do any clearing\n",
       "\n",
       "                        // clear output cell\n",
       "                        Jupyter.notebook.clear_output(Jupyter.notebook.get_selected_index())\n",
       "\n",
       "                        // TODO: if in run all mode, move to last cell, otherwise move to next cell\n",
       "                        // move to next cell\n",
       "\n",
       "                    </script></body></html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "            <html><body>\n",
       "\n",
       "            <button onclick=\"this.style.visibility='visible';kql_MagicLaunchWindowFunction('Kqlmagic_temp_files/_b1315f05-4a7a-45b4-811f-73e715f7c122_at_loganalytics_schema.html','fullscreen=no,directories=no,location=no,menubar=no,resizable=yes,scrollbars=yes,status=no,titlebar=no,toolbar=no,','_b1315f05_4a7a_45b4_811f_73e715f7c122_at_loganalytics_schema','')\">popup schema b1315f05-4a7a-45b4-811f-73e715f7c122@loganalytics</button>\n",
       "\n",
       "            <script>\n",
       "\n",
       "            function kql_MagicLaunchWindowFunction(file_path, window_params, window_name, notebooks_host) {\n",
       "                var url;\n",
       "                if (file_path.startsWith('http')) {\n",
       "                    url = file_path;\n",
       "                } else {\n",
       "                    var base_url = '';\n",
       "\n",
       "                    // check if azure notebook\n",
       "                    var azure_host = (notebooks_host == null || notebooks_host.length == 0) ? 'https://notebooks.azure.com' : notebooks_host;\n",
       "                    var start = azure_host.search('//');\n",
       "                    var azure_host_suffix = '.' + azure_host.substring(start+2);\n",
       "\n",
       "                    var loc = String(window.location);\n",
       "                    var end = loc.search(azure_host_suffix);\n",
       "                    start = loc.search('//');\n",
       "                    if (start > 0 && end > 0) {\n",
       "                        var parts = loc.substring(start+2, end).split('-');\n",
       "                        if (parts.length == 2) {\n",
       "                            var library = parts[0];\n",
       "                            var user = parts[1];\n",
       "                            base_url = azure_host + '/api/user/' +user+ '/library/' +library+ '/html/';\n",
       "                        }\n",
       "                    }\n",
       "\n",
       "                    // check if local jupyter lab\n",
       "                    if (base_url.length == 0) {\n",
       "                        var configDataScipt  = document.getElementById('jupyter-config-data');\n",
       "                        if (configDataScipt != null) {\n",
       "                            var jupyterConfigData = JSON.parse(configDataScipt.textContent);\n",
       "                            if (jupyterConfigData['appName'] == 'JupyterLab' && jupyterConfigData['serverRoot'] != null &&  jupyterConfigData['treeUrl'] != null) {\n",
       "                                var basePath = 'C:/' + '/';\n",
       "                                if (basePath.startsWith(jupyterConfigData['serverRoot'])) {\n",
       "                                    base_url = '/files/' + basePath.substring(jupyterConfigData['serverRoot'].length+1);\n",
       "                                }\n",
       "                            } \n",
       "                        }\n",
       "                    }\n",
       "\n",
       "                    // assume local jupyter notebook\n",
       "                    if (base_url.length == 0) {\n",
       "\n",
       "                        var parts = loc.split('/');\n",
       "                        parts.pop();\n",
       "                        base_url = parts.join('/') + '/';\n",
       "                    }\n",
       "                    url = base_url + file_path;\n",
       "                }\n",
       "\n",
       "                window.focus();\n",
       "                var w = screen.width / 2;\n",
       "                var h = screen.height / 2;\n",
       "                params = 'width='+w+',height='+h;\n",
       "                kql_Magic__b1315f05_4a7a_45b4_811f_73e715f7c122_at_loganalytics_schema = window.open(url, window_name, window_params + params);\n",
       "            }\n",
       "            </script>\n",
       "\n",
       "            </body></html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ws_id = input('Workspace ID')\n",
    "ten_id = input('Tenant ID')\n",
    "la_connection_string = f'loganalytics://code().tenant(\"{ten_id}\").workspace(\"{ws_id}\")'\n",
    "qry_prov.connect(connection_str=f'{la_connection_string}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='review_queries'></a>\n",
    "### Reviewing available queries\n",
    "Upon connecting to the relevant Data Environment we need to look at what query options we have available to us. In order to do this we can call <code>QUERY_PROVIDER.list_queries().</code> This will return a generator with the names of all the queries in our store.\n",
    "\n",
    "The results returned show the data family the query belongs to and the name of the specific query.\n",
    "\n",
    "    list_queries(self):\n",
    "        \n",
    "        Return list of family.query in the store.\n",
    "\n",
    "        Returns\n",
    "        -------\n",
    "        Iterable[str]\n",
    "            List of queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LinuxSyslog.all_syslog\n",
      "LinuxSyslog.cron_activity\n",
      "LinuxSyslog.squid_activity\n",
      "LinuxSyslog.sudo_activity\n",
      "LinuxSyslog.user_group_activity\n",
      "LinuxSyslog.user_logon\n",
      "SecurityAlert.get_alert\n",
      "SecurityAlert.list_alerts\n",
      "SecurityAlert.list_alerts_counts\n",
      "SecurityAlert.list_alerts_for_ip\n",
      "SecurityAlert.list_related_alerts\n",
      "WindowsSecurity.get_host_logon\n",
      "WindowsSecurity.get_parent_process\n",
      "WindowsSecurity.get_process_tree\n",
      "WindowsSecurity.list_host_logon_failures\n",
      "WindowsSecurity.list_host_logons\n",
      "WindowsSecurity.list_host_processes\n",
      "WindowsSecurity.list_hosts_matching_commandline\n",
      "WindowsSecurity.list_matching_processes\n",
      "WindowsSecurity.list_processes_in_session\n"
     ]
    }
   ],
   "source": [
    "queries = qry_prov.list_queries()\n",
    "for query in queries:\n",
    "    print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get further details on a specific query call <code>QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME('?')</code> or <code>QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME('help')</code>\n",
    "\n",
    "This will display:\n",
    "- Query Name\n",
    "- What Data Environment it is designed for\n",
    "- Short description of what the query does\n",
    "- What parameter the query can be passed\n",
    "- The raw query that will be run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query:  list_alerts\n",
      "Data source:  LogAnalytics\n",
      "Retrieves list of alerts\n",
      "\n",
      "Parameters\n",
      "----------\n",
      "add_query_items: str (optional)\n",
      "    Additional query clauses\n",
      "end: datetime\n",
      "    Query end time\n",
      "path_separator: str (optional)\n",
      "    Path separator\n",
      "    (default value is: \\\\)\n",
      "query_project: str (optional)\n",
      "    Column project statement\n",
      "    (default value is:  | project-rename StartTimeUtc = StartTime, EndTim...)\n",
      "start: datetime\n",
      "    Query start time\n",
      "subscription_filter: str (optional)\n",
      "    Optional subscription/tenant filter expression\n",
      "    (default value is: true)\n",
      "table: str (optional)\n",
      "    Table name\n",
      "    (default value is: SecurityAlert)\n",
      "Query:\n",
      " {table} {query_project} | where {subscription_filter} | where TimeGenerated >= datetime({start}) | where TimeGenerated <= datetime({end}) | extend extendedProps = parse_json(ExtendedProperties) | extend CompromisedEntity = tostring(extendedProps[\"Compromised Host\"]) | project-away extendedProps {add_query_items}\n"
     ]
    }
   ],
   "source": [
    "qry_prov.SecurityAlert.list_alerts('?')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='run_query'></a>\n",
    "### Running an pre-defined query\n",
    "To run a query from our query store we again call <code>QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME(**Kwargs)</code> but this time we simply pass required parameters for that query as key word arguments.\n",
    "\n",
    "This will return a Pandas DataFrame of the results with the columns determined by the query parameters. Should the query fail for some reason an exception will be raised."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TenantId</th>\n",
       "      <th>TimeGenerated</th>\n",
       "      <th>AlertDisplayName</th>\n",
       "      <th>AlertName</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Description</th>\n",
       "      <th>ProviderName</th>\n",
       "      <th>VendorName</th>\n",
       "      <th>VendorOriginalId</th>\n",
       "      <th>SystemAlertId</th>\n",
       "      <th>...</th>\n",
       "      <th>ExtendedProperties</th>\n",
       "      <th>Entities</th>\n",
       "      <th>SourceSystem</th>\n",
       "      <th>WorkspaceSubscriptionId</th>\n",
       "      <th>WorkspaceResourceGroup</th>\n",
       "      <th>ExtendedLinks</th>\n",
       "      <th>ProductName</th>\n",
       "      <th>ProductComponentName</th>\n",
       "      <th>Type</th>\n",
       "      <th>CompromisedEntity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>2518385291989119899_8af9954d-f28d-40ff-a079-d9...</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 07:02:42</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>ba07c315-0af5-4568-9ecd-6c788f9267ae</td>\n",
       "      <td>b7adb73b-0778-4929-b46a-c0ed642bc61f</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-26 06:03:16</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>c3144593-9bae-448e-87dd-b2d3c47de571</td>\n",
       "      <td>d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-23 06:42:01</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>4e4173a6-1a27-451f-8a3c-25d10b306c30</td>\n",
       "      <td>11813ab7-ab7c-4719-b0a1-ccb5d4a32223</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               TenantId       TimeGenerated  \\\n",
       "0  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "1  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "2  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 07:02:42   \n",
       "3  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-26 06:03:16   \n",
       "4  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-23 06:42:01   \n",
       "\n",
       "                                    AlertDisplayName  \\\n",
       "0                 Suspicious authentication activity   \n",
       "1                 Suspicious authentication activity   \n",
       "2  Traffic from unrecommended IP addresses was de...   \n",
       "3  Traffic from unrecommended IP addresses was de...   \n",
       "4  Traffic from unrecommended IP addresses was de...   \n",
       "\n",
       "                                           AlertName Severity  \\\n",
       "0                 Suspicious authentication activity   Medium   \n",
       "1                 Suspicious authentication activity   Medium   \n",
       "2  Traffic from unrecommended IP addresses was de...      Low   \n",
       "3  Traffic from unrecommended IP addresses was de...      Low   \n",
       "4  Traffic from unrecommended IP addresses was de...      Low   \n",
       "\n",
       "                                         Description  \\\n",
       "0  Although none of them succeeded, some of them ...   \n",
       "1  Although none of them succeeded, some of them ...   \n",
       "2  Azure security center has detected incoming tr...   \n",
       "3  Azure security center has detected incoming tr...   \n",
       "4  Azure security center has detected incoming tr...   \n",
       "\n",
       "                ProviderName VendorName                      VendorOriginalId  \\\n",
       "0                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "1                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "2  AdaptiveNetworkHardenings  Microsoft  ba07c315-0af5-4568-9ecd-6c788f9267ae   \n",
       "3  AdaptiveNetworkHardenings  Microsoft  c3144593-9bae-448e-87dd-b2d3c47de571   \n",
       "4  AdaptiveNetworkHardenings  Microsoft  4e4173a6-1a27-451f-8a3c-25d10b306c30   \n",
       "\n",
       "                                       SystemAlertId  ...  \\\n",
       "0  2518385291989119899_8af9954d-f28d-40ff-a079-d9...  ...   \n",
       "1               5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6  ...   \n",
       "2               b7adb73b-0778-4929-b46a-c0ed642bc61f  ...   \n",
       "3               d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760  ...   \n",
       "4               11813ab7-ab7c-4719-b0a1-ccb5d4a32223  ...   \n",
       "\n",
       "                                  ExtendedProperties  \\\n",
       "0  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "1  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "2  {\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...   \n",
       "3  {\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...   \n",
       "4  {\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...   \n",
       "\n",
       "                                            Entities SourceSystem  \\\n",
       "0  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "1  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "2  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "3  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "4  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "\n",
       "                WorkspaceSubscriptionId  WorkspaceResourceGroup  \\\n",
       "0  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "1  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "2                                                                 \n",
       "3                                                                 \n",
       "4                                                                 \n",
       "\n",
       "                                       ExtendedLinks            ProductName  \\\n",
       "0  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "1  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "2  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "3  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "4  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "\n",
       "  ProductComponentName           Type CompromisedEntity  \n",
       "0                       SecurityAlert                    \n",
       "1                       SecurityAlert                    \n",
       "2                       SecurityAlert                    \n",
       "3                       SecurityAlert                    \n",
       "4                       SecurityAlert                    \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alerts = qry_prov.SecurityAlert.list_alerts(start='2019-07-21 23:43:18.274492', end='2019-07-27 23:43:18.274492')\n",
    "alerts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also possible to pass queries objects as arguments before defining keywork arguments. \n",
    "For example if I wanted to define query times as an object rather than defining a start and end via keywork arguments I could simply pass a querytimes object to the pre-defined query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5e8588414e5a4b36823f460350aa735d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HTML(value='<h4>Set query time boundaries</h4>')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d1302e236120441d92d03cffd28a0ca4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(DatePicker(value=datetime.date(2019, 7, 26), description='Origin Date'), Text(value='23:43:18.2…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c0f9a5a7d4a3483f87e86baca8d7d399",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(IntRangeSlider(value=(-5, 1), description='Time Range (day):', layout=Layout(width='80%'), max=…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "query_times = mas.nbwidgets.QueryTime(units='day',  \n",
    "                            max_before=40, max_after=1, before=5)\n",
    "query_times.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TenantId</th>\n",
       "      <th>TimeGenerated</th>\n",
       "      <th>AlertDisplayName</th>\n",
       "      <th>AlertName</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Description</th>\n",
       "      <th>ProviderName</th>\n",
       "      <th>VendorName</th>\n",
       "      <th>VendorOriginalId</th>\n",
       "      <th>SystemAlertId</th>\n",
       "      <th>...</th>\n",
       "      <th>ExtendedProperties</th>\n",
       "      <th>Entities</th>\n",
       "      <th>SourceSystem</th>\n",
       "      <th>WorkspaceSubscriptionId</th>\n",
       "      <th>WorkspaceResourceGroup</th>\n",
       "      <th>ExtendedLinks</th>\n",
       "      <th>ProductName</th>\n",
       "      <th>ProductComponentName</th>\n",
       "      <th>Type</th>\n",
       "      <th>CompromisedEntity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-26 06:03:16</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>c3144593-9bae-448e-87dd-b2d3c47de571</td>\n",
       "      <td>d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-23 06:42:01</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>4e4173a6-1a27-451f-8a3c-25d10b306c30</td>\n",
       "      <td>11813ab7-ab7c-4719-b0a1-ccb5d4a32223</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>2518385291989119899_8af9954d-f28d-40ff-a079-d9...</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 07:02:42</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>ba07c315-0af5-4568-9ecd-6c788f9267ae</td>\n",
       "      <td>b7adb73b-0778-4929-b46a-c0ed642bc61f</td>\n",
       "      <td>...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               TenantId       TimeGenerated  \\\n",
       "0  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-26 06:03:16   \n",
       "1  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-23 06:42:01   \n",
       "2  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "3  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "4  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 07:02:42   \n",
       "\n",
       "                                    AlertDisplayName  \\\n",
       "0  Traffic from unrecommended IP addresses was de...   \n",
       "1  Traffic from unrecommended IP addresses was de...   \n",
       "2                 Suspicious authentication activity   \n",
       "3                 Suspicious authentication activity   \n",
       "4  Traffic from unrecommended IP addresses was de...   \n",
       "\n",
       "                                           AlertName Severity  \\\n",
       "0  Traffic from unrecommended IP addresses was de...      Low   \n",
       "1  Traffic from unrecommended IP addresses was de...      Low   \n",
       "2                 Suspicious authentication activity   Medium   \n",
       "3                 Suspicious authentication activity   Medium   \n",
       "4  Traffic from unrecommended IP addresses was de...      Low   \n",
       "\n",
       "                                         Description  \\\n",
       "0  Azure security center has detected incoming tr...   \n",
       "1  Azure security center has detected incoming tr...   \n",
       "2  Although none of them succeeded, some of them ...   \n",
       "3  Although none of them succeeded, some of them ...   \n",
       "4  Azure security center has detected incoming tr...   \n",
       "\n",
       "                ProviderName VendorName                      VendorOriginalId  \\\n",
       "0  AdaptiveNetworkHardenings  Microsoft  c3144593-9bae-448e-87dd-b2d3c47de571   \n",
       "1  AdaptiveNetworkHardenings  Microsoft  4e4173a6-1a27-451f-8a3c-25d10b306c30   \n",
       "2                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "3                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "4  AdaptiveNetworkHardenings  Microsoft  ba07c315-0af5-4568-9ecd-6c788f9267ae   \n",
       "\n",
       "                                       SystemAlertId  ...  \\\n",
       "0               d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760  ...   \n",
       "1               11813ab7-ab7c-4719-b0a1-ccb5d4a32223  ...   \n",
       "2  2518385291989119899_8af9954d-f28d-40ff-a079-d9...  ...   \n",
       "3               5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6  ...   \n",
       "4               b7adb73b-0778-4929-b46a-c0ed642bc61f  ...   \n",
       "\n",
       "                                  ExtendedProperties  \\\n",
       "0  {\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...   \n",
       "1  {\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...   \n",
       "2  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "3  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "4  {\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...   \n",
       "\n",
       "                                            Entities SourceSystem  \\\n",
       "0  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "1  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "2  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "3  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "4  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "\n",
       "                WorkspaceSubscriptionId  WorkspaceResourceGroup  \\\n",
       "0                                                                 \n",
       "1                                                                 \n",
       "2  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "3  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "4                                                                 \n",
       "\n",
       "                                       ExtendedLinks            ProductName  \\\n",
       "0  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "1  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "2  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "3  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "4  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "\n",
       "  ProductComponentName           Type CompromisedEntity  \n",
       "0                       SecurityAlert                    \n",
       "1                       SecurityAlert                    \n",
       "2                       SecurityAlert                    \n",
       "3                       SecurityAlert                    \n",
       "4                       SecurityAlert                    \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry_prov.SecurityAlert.list_alerts(query_times)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='run_adhoc'></a>\n",
    "###  Running an ad-hoc query\n",
    "It is also possible to run ad-hoc queries via a similar method. Rather than calling a named query from the Query Provider query store, we can pass a query directly to our Query Provider with <code>QUERY_PROVIDER.exec_query(query=QUERY_STRING)</code>. This will execute the query string passed in the parameters with the driver contained in the Query Provider and return data in a Pandas DataFrame. As with predefined queries an exception will be raised should the query fail to execute.\n",
    "\n",
    "    query(self, query: str) -> Union[pd.DataFrame, Any]:\n",
    "        Execute query string and return DataFrame of results.\n",
    "\n",
    "        Parameters\n",
    "        ----------\n",
    "        query : str\n",
    "            The kql query to execute\n",
    "\n",
    "        Returns\n",
    "        -------\n",
    "        Union[pd.DataFrame, results.ResultSet]\n",
    "            A DataFrame (if successful) or\n",
    "            Kql ResultSet if an error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TenantId</th>\n",
       "      <th>TimeGenerated</th>\n",
       "      <th>DisplayName</th>\n",
       "      <th>AlertName</th>\n",
       "      <th>AlertSeverity</th>\n",
       "      <th>Description</th>\n",
       "      <th>ProviderName</th>\n",
       "      <th>VendorName</th>\n",
       "      <th>VendorOriginalId</th>\n",
       "      <th>SystemAlertId</th>\n",
       "      <th>...</th>\n",
       "      <th>RemediationSteps</th>\n",
       "      <th>ExtendedProperties</th>\n",
       "      <th>Entities</th>\n",
       "      <th>SourceSystem</th>\n",
       "      <th>WorkspaceSubscriptionId</th>\n",
       "      <th>WorkspaceResourceGroup</th>\n",
       "      <th>ExtendedLinks</th>\n",
       "      <th>ProductName</th>\n",
       "      <th>ProductComponentName</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>2518385291989119899_8af9954d-f28d-40ff-a079-d9...</td>\n",
       "      <td>...</td>\n",
       "      <td>[\\r\\n  \"1. Enforce the use of strong passwords...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 06:35:13</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Suspicious authentication activity</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Although none of them succeeded, some of them ...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>8af9954d-f28d-40ff-a079-d9d4cc5a5268</td>\n",
       "      <td>5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6</td>\n",
       "      <td>...</td>\n",
       "      <td>[\\r\\n  \"1. Enforce the use of strong passwords...</td>\n",
       "      <td>{\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-22 07:02:42</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>ba07c315-0af5-4568-9ecd-6c788f9267ae</td>\n",
       "      <td>b7adb73b-0778-4929-b46a-c0ed642bc61f</td>\n",
       "      <td>...</td>\n",
       "      <td>[\\r\\n  \"1. Review the IP addresses and determi...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-07-26 06:03:16</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Traffic from unrecommended IP addresses was de...</td>\n",
       "      <td>Low</td>\n",
       "      <td>Azure security center has detected incoming tr...</td>\n",
       "      <td>AdaptiveNetworkHardenings</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>c3144593-9bae-448e-87dd-b2d3c47de571</td>\n",
       "      <td>d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760</td>\n",
       "      <td>...</td>\n",
       "      <td>[\\r\\n  \"1. Review the IP addresses and determi...</td>\n",
       "      <td>{\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...</td>\n",
       "      <td>Detection</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...</td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>2019-06-27 00:31:35</td>\n",
       "      <td>Security incident with shared process detected</td>\n",
       "      <td>Security incident with shared process detected</td>\n",
       "      <td>High</td>\n",
       "      <td>The incident which started on 2019-06-25 21:24...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>be88b671-2572-4373-af4a-323849b1da1d</td>\n",
       "      <td>2518408029550429999_be88b671-2572-4373-af4a-32...</td>\n",
       "      <td>...</td>\n",
       "      <td>[\\r\\n  \"1. Escalate the alert to the informati...</td>\n",
       "      <td>{\\r\\n  \"isincident\": \"true\",\\r\\n  \"Detected Ti...</td>\n",
       "      <td>[\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"DisplayNam...</td>\n",
       "      <td>Detection</td>\n",
       "      <td>3b701f84-d04b-4479-89b1-fa8827eb537e</td>\n",
       "      <td>sentineltest</td>\n",
       "      <td></td>\n",
       "      <td>Azure Security Center</td>\n",
       "      <td></td>\n",
       "      <td>SecurityAlert</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               TenantId       TimeGenerated  \\\n",
       "0  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "1  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 06:35:13   \n",
       "2  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-22 07:02:42   \n",
       "3  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-07-26 06:03:16   \n",
       "4  b1315f05-4a7a-45b4-811f-73e715f7c122 2019-06-27 00:31:35   \n",
       "\n",
       "                                         DisplayName  \\\n",
       "0                 Suspicious authentication activity   \n",
       "1                 Suspicious authentication activity   \n",
       "2  Traffic from unrecommended IP addresses was de...   \n",
       "3  Traffic from unrecommended IP addresses was de...   \n",
       "4     Security incident with shared process detected   \n",
       "\n",
       "                                           AlertName AlertSeverity  \\\n",
       "0                 Suspicious authentication activity        Medium   \n",
       "1                 Suspicious authentication activity        Medium   \n",
       "2  Traffic from unrecommended IP addresses was de...           Low   \n",
       "3  Traffic from unrecommended IP addresses was de...           Low   \n",
       "4     Security incident with shared process detected          High   \n",
       "\n",
       "                                         Description  \\\n",
       "0  Although none of them succeeded, some of them ...   \n",
       "1  Although none of them succeeded, some of them ...   \n",
       "2  Azure security center has detected incoming tr...   \n",
       "3  Azure security center has detected incoming tr...   \n",
       "4  The incident which started on 2019-06-25 21:24...   \n",
       "\n",
       "                ProviderName VendorName                      VendorOriginalId  \\\n",
       "0                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "1                  Detection  Microsoft  8af9954d-f28d-40ff-a079-d9d4cc5a5268   \n",
       "2  AdaptiveNetworkHardenings  Microsoft  ba07c315-0af5-4568-9ecd-6c788f9267ae   \n",
       "3  AdaptiveNetworkHardenings  Microsoft  c3144593-9bae-448e-87dd-b2d3c47de571   \n",
       "4                  Detection  Microsoft  be88b671-2572-4373-af4a-323849b1da1d   \n",
       "\n",
       "                                       SystemAlertId  ...  \\\n",
       "0  2518385291989119899_8af9954d-f28d-40ff-a079-d9...  ...   \n",
       "1               5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6  ...   \n",
       "2               b7adb73b-0778-4929-b46a-c0ed642bc61f  ...   \n",
       "3               d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760  ...   \n",
       "4  2518408029550429999_be88b671-2572-4373-af4a-32...  ...   \n",
       "\n",
       "                                    RemediationSteps  \\\n",
       "0  [\\r\\n  \"1. Enforce the use of strong passwords...   \n",
       "1  [\\r\\n  \"1. Enforce the use of strong passwords...   \n",
       "2  [\\r\\n  \"1. Review the IP addresses and determi...   \n",
       "3  [\\r\\n  \"1. Review the IP addresses and determi...   \n",
       "4  [\\r\\n  \"1. Escalate the alert to the informati...   \n",
       "\n",
       "                                  ExtendedProperties  \\\n",
       "0  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "1  {\\r\\n  \"Activity start time (UTC)\": \"2019/07/2...   \n",
       "2  {\\r\\n  \"Destination Port\": \"3389\",\\r\\n  \"Proto...   \n",
       "3  {\\r\\n  \"Destination Port\": \"22\",\\r\\n  \"Protoco...   \n",
       "4  {\\r\\n  \"isincident\": \"true\",\\r\\n  \"Detected Ti...   \n",
       "\n",
       "                                            Entities SourceSystem  \\\n",
       "0  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "1  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"HostName\":...    Detection   \n",
       "2  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "3  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"ResourceId...    Detection   \n",
       "4  [\\r\\n  {\\r\\n    \"$id\": \"4\",\\r\\n    \"DisplayNam...    Detection   \n",
       "\n",
       "                WorkspaceSubscriptionId  WorkspaceResourceGroup  \\\n",
       "0  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "1  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "2                                                                 \n",
       "3                                                                 \n",
       "4  3b701f84-d04b-4479-89b1-fa8827eb537e            sentineltest   \n",
       "\n",
       "                                       ExtendedLinks            ProductName  \\\n",
       "0  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "1  [\\r\\n  {\\r\\n    \"Href\": \"https://interflowwebp...  Azure Security Center   \n",
       "2  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "3  [\\r\\n  {\\r\\n    \"DetailBladeInputs\": \"protecte...  Azure Security Center   \n",
       "4                                                     Azure Security Center   \n",
       "\n",
       "  ProductComponentName           Type  \n",
       "0                       SecurityAlert  \n",
       "1                       SecurityAlert  \n",
       "2                       SecurityAlert  \n",
       "3                       SecurityAlert  \n",
       "4                       SecurityAlert  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_query = '''\n",
    "    SecurityAlert\n",
    "    | take 5\n",
    "    '''\n",
    "\n",
    "query_test = qry_prov.exec_query(query=test_query)\n",
    "query_test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='new'></a>\n",
    "### Creating a new set of queries\n",
    "msticpy provides a number of pre-defined queries to call with using the data package. You can also add in additional queries to be imported and used by your Query Provider, these are defined in YAML format files and examples of these files can be found at the msticpy GitHub site https://github.com/microsoft/msticpy/tree/master/msticpy/data/queries. \n",
    "\n",
    "The required structure of these query definition files is as follows:\n",
    "- metadata\n",
    "    - version: The version number of the definition file\n",
    "    - description: A description of the purpose of this collection of query definitions\n",
    "    - data_environments[]: A list of the Data Environments that the defined queries can be run against (1 or more)\n",
    "    - data_families[]: A list of Data Families the defined queries related to, these families are defined as part of msticpy.data.query_defns\n",
    "    - tags[]: A list of tags to help manage definition files\n",
    "- defaults: A set of defaults that apply to all queries in the file\n",
    "    - metadata: Metadata regarding a query\n",
    "        - data_source: The data source to be used for the query\n",
    "    - parameters: Parameters to be passed to the query\n",
    "        - name: The parameter name\n",
    "        - description: A description of what the parameter is\n",
    "        - type: The data type of the parameter\n",
    "        - default: The default value for that parameter\n",
    "- sources: a set of queries\n",
    "    - name: The name of the query\n",
    "        -description: A description of the query's function\n",
    "        -metadata: Any metadata associated with the query\n",
    "        -args: The arguments of the query\n",
    "            -query: The query to be executed\n",
    "            -uri: A URI associated with the query\n",
    "        -parameters: Any parameters required by the query not covered by defaults\n",
    "            - name: The parameter name\n",
    "            - description: A description of what the parameter is\n",
    "            - type: The data type of the parameter\n",
    "            - default: The default value for that parameter\n",
    "            \n",
    "\n",
    "There are also a number of tools within the package to assist in validating new query definition files once created.\n",
    "    \n",
    "    data_query_reader.find_yaml_files\n",
    "        \n",
    "        Return iterable of yaml files found in `source_path`.\n",
    "\n",
    "        Parameters\n",
    "        ----------\n",
    "        source_path : str\n",
    "            The source path to search in.\n",
    "        recursive : bool, optional\n",
    "            Whether to recurse through subfolders.\n",
    "            By default False\n",
    "\n",
    "        Returns\n",
    "        -------\n",
    "        Iterable[str]\n",
    "            File paths of yaml files found.\n",
    "            \n",
    "     data_query_reader.validate_query_defs\n",
    "         \n",
    "         Validate content of query definition.\n",
    "\n",
    "        Parameters\n",
    "        ----------\n",
    "        query_def_dict : dict\n",
    "            Dictionary of query definition yaml file contents.\n",
    "\n",
    "        Returns\n",
    "        -------\n",
    "        bool\n",
    "            True if validation succeeds.\n",
    "\n",
    "        Raises\n",
    "        ------\n",
    "        ValueError\n",
    "            The validation failure reason is returned in the\n",
    "            exception message (arg[0])\n",
    "\n",
    "<code>validate_query_defs()</code> does not perform comprehensive checks on the file but does check key elements required in the file are present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " C:\\queries\\example.yaml is a valid query definition\n"
     ]
    }
   ],
   "source": [
    "for file in QueryReader.find_yaml_files(source_path=\"C:\\\\queries\"):\n",
    "    with open(file) as f_handle:\n",
    "        yaml_file = yaml.safe_load(f_handle)\n",
    "        if QueryReader.validate_query_defs(query_def_dict = yaml_file) == True:\n",
    "            print(f' {file} is a valid query definition')\n",
    "        else:\n",
    "            print(f'There is an error with {file}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='add_new'></a>\n",
    "### Adding a new set of queries and running them\n",
    "Once you are happy with a query definition file then you import it with <code>QUERY_PROVIDER.import_query_file(query_file=PATH_TO_QUERY_FILE)</code>\n",
    "This will load the query file into the Query Provider's Query Store from where it can be called."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry_prov.import_query_file(query_file='C:\\queries\\example.yaml')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once imported the queries in the files appear in the Query Provider's Query Store alongside the others and can be called in the same manner as pre-defined queries.\n",
    "\n",
    "If you have created a large number of query definition files and you want to have the automatically imported into a Query Provider's query store at initialization you can specify a directory containing these queries in the msticpyconfig.yaml file under QueryDefinitions: Custom: \n",
    "\n",
    "For example if I have a folder at C:\\queries I will set the config file to:\n",
    "\n",
    " QueryDefinitions:\n",
    "  Default: \"queries\"\n",
    "  Custom: \n",
    "    - \"C:\\\\queries\"\n",
    "    - \"C:\\\\queries2 \n",
    "\n",
    " \n",
    "Having the Custom field populated will mean the Query Provider will automatically enumerate all the YAML files in the directory provided and automatically import he relevant queries into the query store at initialization alongside the default queries. Custom queries with the same name as default queries will overwrite default queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LinuxSyslog.all_syslog\n",
      "LinuxSyslog.cron_activity\n",
      "LinuxSyslog.squid_activity\n",
      "LinuxSyslog.sudo_activity\n",
      "LinuxSyslog.syslog_example\n",
      "LinuxSyslog.user_group_activity\n",
      "LinuxSyslog.user_logon\n",
      "SecurityAlert.get_alert\n",
      "SecurityAlert.list_alerts\n",
      "SecurityAlert.list_alerts_counts\n",
      "SecurityAlert.list_alerts_for_ip\n",
      "SecurityAlert.list_related_alerts\n",
      "WindowsSecurity.get_host_logon\n",
      "WindowsSecurity.get_parent_process\n",
      "WindowsSecurity.get_process_tree\n",
      "WindowsSecurity.list_host_logon_failures\n",
      "WindowsSecurity.list_host_logons\n",
      "WindowsSecurity.list_host_processes\n",
      "WindowsSecurity.list_hosts_matching_commandline\n",
      "WindowsSecurity.list_matching_processes\n",
      "WindowsSecurity.list_processes_in_session\n"
     ]
    }
   ],
   "source": [
    "queries = qry_prov.list_queries()\n",
    "for query in queries:\n",
    "    print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query:  syslog_example\n",
      "Data source:  LogAnalytics\n",
      "Example query\n",
      "\n",
      "Parameters\n",
      "----------\n",
      "add_query_items: str (optional)\n",
      "    Additional query clauses\n",
      "end: datetime\n",
      "    Query end time\n",
      "host_name: str\n",
      "    Hostname to query for\n",
      "query_project: str (optional)\n",
      "    Column project statement\n",
      "    (default value is:  | project TenantId, Computer, Facility, TimeGener...)\n",
      "start: datetime\n",
      "    Query start time\n",
      "subscription_filter: str (optional)\n",
      "    Optional subscription/tenant filter expression\n",
      "    (default value is: true)\n",
      "table: str (optional)\n",
      "    Table name\n",
      "    (default value is: Syslog)\n",
      "Query:\n",
      " {table} | where {subscription_filter} | where TimeGenerated >= datetime({start}) | where TimeGenerated <= datetime({end}) | where Computer == \"{host_name}\" | take 5\n"
     ]
    }
   ],
   "source": [
    "qry_prov.LinuxSyslog.syslog_example('?')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TenantId</th>\n",
       "      <th>SourceSystem</th>\n",
       "      <th>TimeGenerated</th>\n",
       "      <th>Computer</th>\n",
       "      <th>EventTime</th>\n",
       "      <th>Facility</th>\n",
       "      <th>HostName</th>\n",
       "      <th>SeverityLevel</th>\n",
       "      <th>SyslogMessage</th>\n",
       "      <th>ProcessID</th>\n",
       "      <th>HostIP</th>\n",
       "      <th>ProcessName</th>\n",
       "      <th>MG</th>\n",
       "      <th>Type</th>\n",
       "      <th>_ResourceId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>Linux</td>\n",
       "      <td>2019-07-25 15:15:37.213</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>2019-07-25 15:15:37</td>\n",
       "      <td>authpriv</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>notice</td>\n",
       "      <td>omsagent : TTY=unknown   PWD=/opt/microsoft/om...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0.1.4</td>\n",
       "      <td>sudo</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>Syslog</td>\n",
       "      <td>/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>Linux</td>\n",
       "      <td>2019-07-25 15:15:37.313</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>2019-07-25 15:15:37</td>\n",
       "      <td>authpriv</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>info</td>\n",
       "      <td>pam_unix(sudo:session): session opened for use...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0.1.4</td>\n",
       "      <td>sudo</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>Syslog</td>\n",
       "      <td>/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>Linux</td>\n",
       "      <td>2019-07-25 15:15:37.917</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>2019-07-25 15:15:37</td>\n",
       "      <td>authpriv</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>info</td>\n",
       "      <td>pam_unix(sudo:session): session closed for use...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0.1.4</td>\n",
       "      <td>sudo</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>Syslog</td>\n",
       "      <td>/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>Linux</td>\n",
       "      <td>2019-07-25 15:15:50.793</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>2019-07-25 15:15:50</td>\n",
       "      <td>authpriv</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>info</td>\n",
       "      <td>pam_unix(cron:session): session closed for use...</td>\n",
       "      <td>29486.0</td>\n",
       "      <td>10.0.1.4</td>\n",
       "      <td>CRON</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>Syslog</td>\n",
       "      <td>/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b1315f05-4a7a-45b4-811f-73e715f7c122</td>\n",
       "      <td>Linux</td>\n",
       "      <td>2019-07-25 15:16:01.800</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>2019-07-25 15:16:01</td>\n",
       "      <td>authpriv</td>\n",
       "      <td>UbuntuDevEnv</td>\n",
       "      <td>info</td>\n",
       "      <td>pam_unix(cron:session): session opened for use...</td>\n",
       "      <td>29844.0</td>\n",
       "      <td>10.0.1.4</td>\n",
       "      <td>CRON</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>Syslog</td>\n",
       "      <td>/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               TenantId SourceSystem           TimeGenerated  \\\n",
       "0  b1315f05-4a7a-45b4-811f-73e715f7c122        Linux 2019-07-25 15:15:37.213   \n",
       "1  b1315f05-4a7a-45b4-811f-73e715f7c122        Linux 2019-07-25 15:15:37.313   \n",
       "2  b1315f05-4a7a-45b4-811f-73e715f7c122        Linux 2019-07-25 15:15:37.917   \n",
       "3  b1315f05-4a7a-45b4-811f-73e715f7c122        Linux 2019-07-25 15:15:50.793   \n",
       "4  b1315f05-4a7a-45b4-811f-73e715f7c122        Linux 2019-07-25 15:16:01.800   \n",
       "\n",
       "       Computer           EventTime  Facility      HostName SeverityLevel  \\\n",
       "0  UbuntuDevEnv 2019-07-25 15:15:37  authpriv  UbuntuDevEnv        notice   \n",
       "1  UbuntuDevEnv 2019-07-25 15:15:37  authpriv  UbuntuDevEnv          info   \n",
       "2  UbuntuDevEnv 2019-07-25 15:15:37  authpriv  UbuntuDevEnv          info   \n",
       "3  UbuntuDevEnv 2019-07-25 15:15:50  authpriv  UbuntuDevEnv          info   \n",
       "4  UbuntuDevEnv 2019-07-25 15:16:01  authpriv  UbuntuDevEnv          info   \n",
       "\n",
       "                                       SyslogMessage  ProcessID    HostIP  \\\n",
       "0  omsagent : TTY=unknown   PWD=/opt/microsoft/om...        NaN  10.0.1.4   \n",
       "1  pam_unix(sudo:session): session opened for use...        NaN  10.0.1.4   \n",
       "2  pam_unix(sudo:session): session closed for use...        NaN  10.0.1.4   \n",
       "3  pam_unix(cron:session): session closed for use...    29486.0  10.0.1.4   \n",
       "4  pam_unix(cron:session): session opened for use...    29844.0  10.0.1.4   \n",
       "\n",
       "  ProcessName                                    MG    Type  \\\n",
       "0        sudo  00000000-0000-0000-0000-000000000002  Syslog   \n",
       "1        sudo  00000000-0000-0000-0000-000000000002  Syslog   \n",
       "2        sudo  00000000-0000-0000-0000-000000000002  Syslog   \n",
       "3        CRON  00000000-0000-0000-0000-000000000002  Syslog   \n",
       "4        CRON  00000000-0000-0000-0000-000000000002  Syslog   \n",
       "\n",
       "                                         _ResourceId  \n",
       "0  /subscriptions/3b701f84-d04b-4479-89b1-fa8827e...  \n",
       "1  /subscriptions/3b701f84-d04b-4479-89b1-fa8827e...  \n",
       "2  /subscriptions/3b701f84-d04b-4479-89b1-fa8827e...  \n",
       "3  /subscriptions/3b701f84-d04b-4479-89b1-fa8827e...  \n",
       "4  /subscriptions/3b701f84-d04b-4479-89b1-fa8827e...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry_prov.LinuxSyslog.syslog_example(start='2019-07-21 23:43:18.274492', end='2019-07-27 23:43:18.274492', host_name='UbuntuDevEnv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you are having difficulties with a defined query and it is not producing the expected results it can be useful to see the raw query exactly as it is passed to the Data Environment. If you call a query with 'print' and the parameters required by that query it will construct and print out the query string to be run. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "' Syslog | where true | where TimeGenerated >= datetime(2019-07-21 23:43:18.274492) | where TimeGenerated <= datetime(2019-07-27 23:43:18.274492) | where Computer == \"UbuntuDevEnv\" | take 5'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry_prov.LinuxSyslog.syslog_example('print', start='2019-07-21 23:43:18.274492', end='2019-07-27 23:43:18.274492', host_name='UbuntuDevEnv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}