tutorials-and-examples/example-notebooks/Example - Using Sentinel Search Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example Notebook - Using Microsoft Sentinel Search Jobs\n",
    "\n",
    "[Search in Microsoft Sentinel](https://docs.microsoft.com/azure/sentinel/investigate-large-datasets) is built on top of Search jobs. Search jobs are asynchronous queries that fetch records.<br>\n",
    "The results are returned to a search table that's created in your Log Analytics workspace after you start the Search job. <br>\n",
    "The search job uses parallel processing to run the search across long time spans, in extremely large datasets. \n",
    "\n",
    "Using [MSTICPy](https://github.com/microsoft/msticpy) you can create Search jobs from a notebook, check when the requested logs are ready and then query the returned data.\n",
    "In this notebook we take you through an example of doing just this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "The first thing we need to do is install and configure MSTICPy in order to ensure the features are accessible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<h3>Starting Notebook setup...</h3>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "%pip install --upgrade msticpy\n",
    "display(HTML(\"<h3>Starting Notebook setup...</h3>\"))\n",
    "\n",
    "import msticpy\n",
    "\n",
    "msticpy.init_notebook(\n",
    "    namespace=globals(),\n",
    "    verbosity=0,\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next step is to connect to Microsoft Sentinel. If you have configured a [MSTICPy configuration file](https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html) you can use your workspace details configured there. Otherwise you can pass in your details when initalizing it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from msticpy.data.azure import MicrosoftSentinel\n",
    "microsoft_sentinel = MicrosoftSentinel()\n",
    "microsoft_sentinel.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once connected we can now start a search with `create_search`. To this function we need to pass in a KQL query to run for the search.<br>\n",
    "Log queries in a search job are intended to scan very large sets of data. <br>\n",
    "To support distribution and segmentation, the queries can only search on data source at a time and can only use a subset of KQL, including the operators:\n",
    " - where\n",
    " - extend\n",
    " - project\n",
    " - project-away\n",
    " - project-keep\n",
    " - project-rename\n",
    " - project-reorder\n",
    " - parse\n",
    " - parse-where\n",
    "\n",
    "More details on the limitations can be found in the [documenation](https://docs.microsoft.com/azure/sentinel/investigate-large-datasets#limitations-of-a-search-job).\n",
    "\n",
    "In addition to the query we can also provide the following optional parameters:\n",
    " - start and end times for the query - if this isn't provided it defaults to the last 90 days\n",
    " - a name for the serach - if not provided a random GUID is generated\n",
    " - a limit on the number of results to return - by defualt this is 1000\n",
    "\n",
    "<b>Note:</b> It can take some time to create a search job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Search job created with for examplesearch_SRCH.\n"
     ]
    }
   ],
   "source": [
    "microsoft_sentinel.create_search(query=\"SecurityEvent | where * has 'infected.exe'\", search_name=\"examplesearch\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once a search job is created it is not immediately ready for querying, it can take some time to run the search, and return the data.<br>\n",
    "We can check the status of our search job with `check_search_status` and by passing it our search name.<br>\n",
    "This will print out the current search jobs's status. Once the status is 'Succeeded' the data is ready for querying when this happens the function will return True."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "examplesearch_SRCH status is 'Updating'\n",
      "examplesearch_SRCH status is 'Updating'\n",
      "examplesearch_SRCH status is 'InProgress'\n",
      "examplesearch_SRCH status is 'InProgress'\n",
      "examplesearch_SRCH status is 'Succeeded'\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "while not microsoft_sentinel.check_search_status(\"examplesearch\"):\n",
    "    time.sleep(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once the search job is ready we can use MSTICPy's [QueryProvider](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProv-MSSentinel.html) feature to run a query against the search's dataset and see the results of the search.<br>\n",
    "The name of the table to query is the name of the search job with _SRCH appended - this is output when you run `create_search` or `check_search_status`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting... "
     ]
    },
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "            <html><body>\n",
       "            <div style=''>\n",
       "            <a href='http://127.0.0.1:59590/webbrowser?url=http%3A//127.0.0.1%3A59590/files/.kqlmagic/temp_files/12804qbN9WpXP0yFv/popup__52b1ab41-869e-4138-9e40-2a4457f09bf0_at_loganalytics_schema.html%3Fkernelid%3D12804qbN9WpXP0yFv&kernelid=12804qbN9WpXP0yFv' style='padding: 2px 6px 2px 6px; color: #333333; background-color: #EEEEEE; border-top: 1px solid #CCCCCC; border-right: 1px solid #333333; border-bottom: 1px solid #333333; border-left: 1px solid #CCCCCC' target='popup__52b1ab41-869e-4138-9e40-2a4457f09bf0_at_loganalytics_schema'>popup schema 52b1ab41-869e-4138-9e40-2a4457f09bf0@loganalytics</a>\n",
       "            </div>\n",
       "            </body></html>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connected\n"
     ]
    },
    {
     "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>TimeGenerated</th>\n",
       "      <th>SourceSystem</th>\n",
       "      <th>Account</th>\n",
       "      <th>AccountType</th>\n",
       "      <th>Computer</th>\n",
       "      <th>EventSourceName</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Task</th>\n",
       "      <th>Level</th>\n",
       "      <th>EventData</th>\n",
       "      <th>EventID</th>\n",
       "      <th>Activity</th>\n",
       "      <th>PartitionKey</th>\n",
       "      <th>RowKey</th>\n",
       "      <th>StorageAccount</th>\n",
       "      <th>AzureDeploymentID</th>\n",
       "      <th>AzureTableName</th>\n",
       "      <th>AccessList</th>\n",
       "      <th>AccessMask</th>\n",
       "      <th>AccessReason</th>\n",
       "      <th>AccountDomain</th>\n",
       "      <th>AccountExpires</th>\n",
       "      <th>AccountName</th>\n",
       "      <th>AccountSessionIdentifier</th>\n",
       "      <th>AdditionalInfo</th>\n",
       "      <th>...</th>\n",
       "      <th>TemplateOID</th>\n",
       "      <th>TemplateSchemaVersion</th>\n",
       "      <th>TemplateVersion</th>\n",
       "      <th>TokenElevationType</th>\n",
       "      <th>TransmittedServices</th>\n",
       "      <th>UserAccountControl</th>\n",
       "      <th>UserParameters</th>\n",
       "      <th>UserPrincipalName</th>\n",
       "      <th>UserWorkstations</th>\n",
       "      <th>VirtualAccount</th>\n",
       "      <th>VendorIds</th>\n",
       "      <th>Workstation</th>\n",
       "      <th>WorkstationName</th>\n",
       "      <th>SourceComputerId</th>\n",
       "      <th>EventOriginId</th>\n",
       "      <th>MG</th>\n",
       "      <th>TimeCollected</th>\n",
       "      <th>ManagementGroupName</th>\n",
       "      <th>Id</th>\n",
       "      <th>_OriginalType</th>\n",
       "      <th>_OriginalItemId</th>\n",
       "      <th>_OriginalTimeGenerated</th>\n",
       "      <th>TenantId</th>\n",
       "      <th>Type</th>\n",
       "      <th>_ResourceId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows Ã— 229 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [TimeGenerated, SourceSystem, Account, AccountType, Computer, EventSourceName, Channel, Task, Level, EventData, EventID, Activity, PartitionKey, RowKey, StorageAccount, AzureDeploymentID, AzureTableName, AccessList, AccessMask, AccessReason, AccountDomain, AccountExpires, AccountName, AccountSessionIdentifier, AdditionalInfo, AdditionalInfo2, AllowedToDelegateTo, Attributes, AuditPolicyChanges, AuditsDiscarded, AuthenticationLevel, AuthenticationPackageName, AuthenticationProvider, AuthenticationServer, AuthenticationService, AuthenticationType, CACertificateHash, CalledStationID, CallerProcessId, CallerProcessName, CallingStationID, CAPublicKeyHash, CategoryId, CertificateDatabaseHash, ClassId, ClassName, ClientAddress, ClientIPAddress, ClientName, CommandLine, CompatibleIds, DCDNSName, DeviceDescription, DeviceId, DisplayName, Disposition, DomainBehaviorVersion, DomainName, DomainPolicyChanged, DomainSid, EAPType, ElevatedToken, ErrorCode, ExtendedQuarantineState, FailureReason, FileHash, FilePath, FilePathNoUser, Filter, ForceLogoff, Fqbn, FullyQualifiedSubjectMachineName, FullyQualifiedSubjectUserName, GroupMembership, HandleId, HardwareIds, HomeDirectory, HomePath, ImpersonationLevel, InterfaceUuid, IpAddress, IpPort, KeyLength, LmPackageName, LocationInformation, LockoutDuration, LockoutObservationWindow, LockoutThreshold, LoggingResult, LogonGuid, LogonHours, LogonID, LogonProcessName, LogonType, LogonTypeName, MachineAccountQuota, MachineInventory, MachineLogon, MandatoryLabel, MaxPasswordAge, ...]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 229 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry_prov = QueryProvider(\"MSSentinel\")\n",
    "qry_prov.connect(WorkspaceConfig())\n",
    "search_results = qry_prov.exec_query(\"examplesearch_SRCH | take 10\")\n",
    "search_results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once a search job is complete and the data no longer needed we can delete the job and its associated data. <br>\n",
    "This can be done with `delete_search` and again passing it the search name. <br>\n",
    "As with search job creation, the deletion can take some time but not further action is required once the deletion is started. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "examplesearch_SRCH set for deletion.\n"
     ]
    }
   ],
   "source": [
    "microsoft_sentinel.delete_search(\"examplesearch\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More details about these features can be found at:\n",
    " - [Microsoft Sentinel Search Jobs](https://docs.microsoft.com/azure/sentinel/investigate-large-datasets)\n",
    " - [MSTICPY's Sentinel features](https://msticpy.readthedocs.io/en/latest/data_acquisition/Sentinel.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "8d09662bfd52ca8b6b410a86d2cd870e6fac48c0484ffa10f489a1bb5cd6642a"
  },
  "kernelspec": {
   "display_name": "Python 3.8 - AzureML",
   "language": "python",
   "name": "python38-azureml"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}