{ "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/Azure 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": [ "\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", "\n", "import msticpy\n", "msticpy.init_notebook(globals(), verbosity=0)\n", "\n", "#data library imports\n", "from msticpy.data.data_providers import QueryProvider\n", "import msticpy.data.core.data_query_reader as QueryReader\n", "from msticpy.data.core.param_extractor import extract_query_params\n", "\n", "print('Imports Complete')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\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 QueryProvider.list_data_environments() 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 QueryProvider(DATA_ENVIRONMENT). 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 QueryProvider(data_environment=DATA_ENVIRONMENT, driver=QUERY_DRIVER)\n", "* We can choose to import queries from a custom query directory (see - [Creating a new set of queries](#new) for more details) with QueryProvider(data_environment=DATA_ENVIRONMENT, driver=QUERY_DRIVER, query_path=QUERY_DIRECTORY_PATH). \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": [ "\n", " \n", " \n", " \n", " \n", "
\n", "
\n", "
\n", "

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\"'

\n", "

• kql reference: Click on 'Help' tab > and Select 'kql reference' or execute '%kql --help \"kql\"'
\n", " • Kqlmagic configuration: execute '%config Kqlmagic'
\n", " • Kqlmagic usage: execute '%kql --usage'
\n", "

\n", "
\n", " \n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "

Kqlmagic package is updated frequently. Run '!pip install Kqlmagic --no-cache-dir --upgrade' to use the latest version.
Kqlmagic version: 0.1.100, source: https://github.com/Microsoft/jupyter-Kqlmagic

\n", " \n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.reconnect();} catch(err) {;}", "text/plain": [ "" ] }, "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": [ "\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 connect() function of the Query Provider we just initialized and passing it a connection string to use.\n", "\n", "For Log Analytics/Azure Sentinel the connection string is in the format of loganalytics://code().tenant(\"TENANT_ID\").workspace(\"WORKSPACE_ID\"). 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": "stdout", "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": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", "\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", "\n", " \n", "\n", " \n", "\n", " " ], "text/plain": [ "" ] }, "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": [ "\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 QUERY_PROVIDER.list_queries(). 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 QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME('?') or QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME('help')\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": [ "\n", "### Running an pre-defined query\n", "To run a query from our query store we again call QUERY_PROVIDER.DATA_FAMILY.QUERY_NAME(**Kwargs) 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": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TenantIdTimeGeneratedAlertDisplayNameAlertNameSeverityDescriptionProviderNameVendorNameVendorOriginalIdSystemAlertId...ExtendedPropertiesEntitiesSourceSystemWorkspaceSubscriptionIdWorkspaceResourceGroupExtendedLinksProductNameProductComponentNameTypeCompromisedEntity
0b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52682518385291989119899_8af9954d-f28d-40ff-a079-d9......{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
1b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52685d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6...{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
2b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 07:02:42Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftba07c315-0af5-4568-9ecd-6c788f9267aeb7adb73b-0778-4929-b46a-c0ed642bc61f...{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
3b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-26 06:03:16Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftc3144593-9bae-448e-87dd-b2d3c47de571d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760...{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
4b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-23 06:42:01Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoft4e4173a6-1a27-451f-8a3c-25d10b306c3011813ab7-ab7c-4719-b0a1-ccb5d4a32223...{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
\n", "

5 rows × 30 columns

\n", "
" ], "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='

Set query time boundaries

')" ] }, "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": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TenantIdTimeGeneratedAlertDisplayNameAlertNameSeverityDescriptionProviderNameVendorNameVendorOriginalIdSystemAlertId...ExtendedPropertiesEntitiesSourceSystemWorkspaceSubscriptionIdWorkspaceResourceGroupExtendedLinksProductNameProductComponentNameTypeCompromisedEntity
0b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-26 06:03:16Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftc3144593-9bae-448e-87dd-b2d3c47de571d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760...{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
1b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-23 06:42:01Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoft4e4173a6-1a27-451f-8a3c-25d10b306c3011813ab7-ab7c-4719-b0a1-ccb5d4a32223...{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
2b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52682518385291989119899_8af9954d-f28d-40ff-a079-d9......{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
3b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52685d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6...{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
4b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 07:02:42Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftba07c315-0af5-4568-9ecd-6c788f9267aeb7adb73b-0778-4929-b46a-c0ed642bc61f...{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
\n", "

5 rows × 30 columns

\n", "
" ], "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": [ "\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 QUERY_PROVIDER.exec_query(query=QUERY_STRING). 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": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TenantIdTimeGeneratedDisplayNameAlertNameAlertSeverityDescriptionProviderNameVendorNameVendorOriginalIdSystemAlertId...RemediationStepsExtendedPropertiesEntitiesSourceSystemWorkspaceSubscriptionIdWorkspaceResourceGroupExtendedLinksProductNameProductComponentNameType
0b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52682518385291989119899_8af9954d-f28d-40ff-a079-d9......[\\r\\n \"1. Enforce the use of strong passwords...{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
1b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 06:35:13Suspicious authentication activitySuspicious authentication activityMediumAlthough none of them succeeded, some of them ...DetectionMicrosoft8af9954d-f28d-40ff-a079-d9d4cc5a52685d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6...[\\r\\n \"1. Enforce the use of strong passwords...{\\r\\n \"Activity start time (UTC)\": \"2019/07/2...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltest[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp...Azure Security CenterSecurityAlert
2b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-22 07:02:42Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftba07c315-0af5-4568-9ecd-6c788f9267aeb7adb73b-0778-4929-b46a-c0ed642bc61f...[\\r\\n \"1. Review the IP addresses and determi...{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
3b1315f05-4a7a-45b4-811f-73e715f7c1222019-07-26 06:03:16Traffic from unrecommended IP addresses was de...Traffic from unrecommended IP addresses was de...LowAzure security center has detected incoming tr...AdaptiveNetworkHardeningsMicrosoftc3144593-9bae-448e-87dd-b2d3c47de571d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760...[\\r\\n \"1. Review the IP addresses and determi...{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId...Detection[\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte...Azure Security CenterSecurityAlert
4b1315f05-4a7a-45b4-811f-73e715f7c1222019-06-27 00:31:35Security incident with shared process detectedSecurity incident with shared process detectedHighThe incident which started on 2019-06-25 21:24...DetectionMicrosoftbe88b671-2572-4373-af4a-323849b1da1d2518408029550429999_be88b671-2572-4373-af4a-32......[\\r\\n \"1. Escalate the alert to the informati...{\\r\\n \"isincident\": \"true\",\\r\\n \"Detected Ti...[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"DisplayNam...Detection3b701f84-d04b-4479-89b1-fa8827eb537esentineltestAzure Security CenterSecurityAlert
\n", "

5 rows × 29 columns

\n", "
" ], "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": [ "\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", "validate_query_defs() 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": [ "\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 QUERY_PROVIDER.import_query_file(query_file=PATH_TO_QUERY_FILE)\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": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TenantIdSourceSystemTimeGeneratedComputerEventTimeFacilityHostNameSeverityLevelSyslogMessageProcessIDHostIPProcessNameMGType_ResourceId
0b1315f05-4a7a-45b4-811f-73e715f7c122Linux2019-07-25 15:15:37.213UbuntuDevEnv2019-07-25 15:15:37authprivUbuntuDevEnvnoticeomsagent : TTY=unknown PWD=/opt/microsoft/om...NaN10.0.1.4sudo00000000-0000-0000-0000-000000000002Syslog/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...
1b1315f05-4a7a-45b4-811f-73e715f7c122Linux2019-07-25 15:15:37.313UbuntuDevEnv2019-07-25 15:15:37authprivUbuntuDevEnvinfopam_unix(sudo:session): session opened for use...NaN10.0.1.4sudo00000000-0000-0000-0000-000000000002Syslog/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...
2b1315f05-4a7a-45b4-811f-73e715f7c122Linux2019-07-25 15:15:37.917UbuntuDevEnv2019-07-25 15:15:37authprivUbuntuDevEnvinfopam_unix(sudo:session): session closed for use...NaN10.0.1.4sudo00000000-0000-0000-0000-000000000002Syslog/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...
3b1315f05-4a7a-45b4-811f-73e715f7c122Linux2019-07-25 15:15:50.793UbuntuDevEnv2019-07-25 15:15:50authprivUbuntuDevEnvinfopam_unix(cron:session): session closed for use...29486.010.0.1.4CRON00000000-0000-0000-0000-000000000002Syslog/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...
4b1315f05-4a7a-45b4-811f-73e715f7c122Linux2019-07-25 15:16:01.800UbuntuDevEnv2019-07-25 15:16:01authprivUbuntuDevEnvinfopam_unix(cron:session): session opened for use...29844.010.0.1.4CRON00000000-0000-0000-0000-000000000002Syslog/subscriptions/3b701f84-d04b-4479-89b1-fa8827e...
\n", "
" ], "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 }