{
"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",
"
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
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": [
"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": [
"\n", " | TenantId | \n", "TimeGenerated | \n", "AlertDisplayName | \n", "AlertName | \n", "Severity | \n", "Description | \n", "ProviderName | \n", "VendorName | \n", "VendorOriginalId | \n", "SystemAlertId | \n", "... | \n", "ExtendedProperties | \n", "Entities | \n", "SourceSystem | \n", "WorkspaceSubscriptionId | \n", "WorkspaceResourceGroup | \n", "ExtendedLinks | \n", "ProductName | \n", "ProductComponentName | \n", "Type | \n", "CompromisedEntity | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "2518385291989119899_8af9954d-f28d-40ff-a079-d9... | \n", "... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
1 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6 | \n", "... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
2 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 07:02:42 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "ba07c315-0af5-4568-9ecd-6c788f9267ae | \n", "b7adb73b-0778-4929-b46a-c0ed642bc61f | \n", "... | \n", "{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
3 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-26 06:03:16 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "c3144593-9bae-448e-87dd-b2d3c47de571 | \n", "d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760 | \n", "... | \n", "{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
4 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-23 06:42:01 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "4e4173a6-1a27-451f-8a3c-25d10b306c30 | \n", "11813ab7-ab7c-4719-b0a1-ccb5d4a32223 | \n", "... | \n", "{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
5 rows × 30 columns
\n", "\n", " | TenantId | \n", "TimeGenerated | \n", "AlertDisplayName | \n", "AlertName | \n", "Severity | \n", "Description | \n", "ProviderName | \n", "VendorName | \n", "VendorOriginalId | \n", "SystemAlertId | \n", "... | \n", "ExtendedProperties | \n", "Entities | \n", "SourceSystem | \n", "WorkspaceSubscriptionId | \n", "WorkspaceResourceGroup | \n", "ExtendedLinks | \n", "ProductName | \n", "ProductComponentName | \n", "Type | \n", "CompromisedEntity | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-26 06:03:16 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "c3144593-9bae-448e-87dd-b2d3c47de571 | \n", "d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760 | \n", "... | \n", "{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
1 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-23 06:42:01 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "4e4173a6-1a27-451f-8a3c-25d10b306c30 | \n", "11813ab7-ab7c-4719-b0a1-ccb5d4a32223 | \n", "... | \n", "{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
2 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "2518385291989119899_8af9954d-f28d-40ff-a079-d9... | \n", "... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
3 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6 | \n", "... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
4 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 07:02:42 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "ba07c315-0af5-4568-9ecd-6c788f9267ae | \n", "b7adb73b-0778-4929-b46a-c0ed642bc61f | \n", "... | \n", "{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "\n", " |
5 rows × 30 columns
\n", "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": [
"\n", " | TenantId | \n", "TimeGenerated | \n", "DisplayName | \n", "AlertName | \n", "AlertSeverity | \n", "Description | \n", "ProviderName | \n", "VendorName | \n", "VendorOriginalId | \n", "SystemAlertId | \n", "... | \n", "RemediationSteps | \n", "ExtendedProperties | \n", "Entities | \n", "SourceSystem | \n", "WorkspaceSubscriptionId | \n", "WorkspaceResourceGroup | \n", "ExtendedLinks | \n", "ProductName | \n", "ProductComponentName | \n", "Type | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "2518385291989119899_8af9954d-f28d-40ff-a079-d9... | \n", "... | \n", "[\\r\\n \"1. Enforce the use of strong passwords... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "
1 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 06:35:13 | \n", "Suspicious authentication activity | \n", "Suspicious authentication activity | \n", "Medium | \n", "Although none of them succeeded, some of them ... | \n", "Detection | \n", "Microsoft | \n", "8af9954d-f28d-40ff-a079-d9d4cc5a5268 | \n", "5d60fff6-7dd2-4474-a4d0-4c8e3fa6fad6 | \n", "... | \n", "[\\r\\n \"1. Enforce the use of strong passwords... | \n", "{\\r\\n \"Activity start time (UTC)\": \"2019/07/2... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"HostName\":... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "[\\r\\n {\\r\\n \"Href\": \"https://interflowwebp... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "
2 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-22 07:02:42 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "ba07c315-0af5-4568-9ecd-6c788f9267ae | \n", "b7adb73b-0778-4929-b46a-c0ed642bc61f | \n", "... | \n", "[\\r\\n \"1. Review the IP addresses and determi... | \n", "{\\r\\n \"Destination Port\": \"3389\",\\r\\n \"Proto... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "
3 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-07-26 06:03:16 | \n", "Traffic from unrecommended IP addresses was de... | \n", "Traffic from unrecommended IP addresses was de... | \n", "Low | \n", "Azure security center has detected incoming tr... | \n", "AdaptiveNetworkHardenings | \n", "Microsoft | \n", "c3144593-9bae-448e-87dd-b2d3c47de571 | \n", "d89ad3b2-f7a7-4cff-b8a4-3f6fa58b4760 | \n", "... | \n", "[\\r\\n \"1. Review the IP addresses and determi... | \n", "{\\r\\n \"Destination Port\": \"22\",\\r\\n \"Protoco... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"ResourceId... | \n", "Detection | \n", "\n", " | \n", " | [\\r\\n {\\r\\n \"DetailBladeInputs\": \"protecte... | \n", "Azure Security Center | \n", "\n", " | SecurityAlert | \n", "
4 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "2019-06-27 00:31:35 | \n", "Security incident with shared process detected | \n", "Security incident with shared process detected | \n", "High | \n", "The incident which started on 2019-06-25 21:24... | \n", "Detection | \n", "Microsoft | \n", "be88b671-2572-4373-af4a-323849b1da1d | \n", "2518408029550429999_be88b671-2572-4373-af4a-32... | \n", "... | \n", "[\\r\\n \"1. Escalate the alert to the informati... | \n", "{\\r\\n \"isincident\": \"true\",\\r\\n \"Detected Ti... | \n", "[\\r\\n {\\r\\n \"$id\": \"4\",\\r\\n \"DisplayNam... | \n", "Detection | \n", "3b701f84-d04b-4479-89b1-fa8827eb537e | \n", "sentineltest | \n", "\n", " | Azure Security Center | \n", "\n", " | SecurityAlert | \n", "
5 rows × 29 columns
\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": [
"\n", " | TenantId | \n", "SourceSystem | \n", "TimeGenerated | \n", "Computer | \n", "EventTime | \n", "Facility | \n", "HostName | \n", "SeverityLevel | \n", "SyslogMessage | \n", "ProcessID | \n", "HostIP | \n", "ProcessName | \n", "MG | \n", "Type | \n", "_ResourceId | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "Linux | \n", "2019-07-25 15:15:37.213 | \n", "UbuntuDevEnv | \n", "2019-07-25 15:15:37 | \n", "authpriv | \n", "UbuntuDevEnv | \n", "notice | \n", "omsagent : TTY=unknown PWD=/opt/microsoft/om... | \n", "NaN | \n", "10.0.1.4 | \n", "sudo | \n", "00000000-0000-0000-0000-000000000002 | \n", "Syslog | \n", "/subscriptions/3b701f84-d04b-4479-89b1-fa8827e... | \n", "
1 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "Linux | \n", "2019-07-25 15:15:37.313 | \n", "UbuntuDevEnv | \n", "2019-07-25 15:15:37 | \n", "authpriv | \n", "UbuntuDevEnv | \n", "info | \n", "pam_unix(sudo:session): session opened for use... | \n", "NaN | \n", "10.0.1.4 | \n", "sudo | \n", "00000000-0000-0000-0000-000000000002 | \n", "Syslog | \n", "/subscriptions/3b701f84-d04b-4479-89b1-fa8827e... | \n", "
2 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "Linux | \n", "2019-07-25 15:15:37.917 | \n", "UbuntuDevEnv | \n", "2019-07-25 15:15:37 | \n", "authpriv | \n", "UbuntuDevEnv | \n", "info | \n", "pam_unix(sudo:session): session closed for use... | \n", "NaN | \n", "10.0.1.4 | \n", "sudo | \n", "00000000-0000-0000-0000-000000000002 | \n", "Syslog | \n", "/subscriptions/3b701f84-d04b-4479-89b1-fa8827e... | \n", "
3 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "Linux | \n", "2019-07-25 15:15:50.793 | \n", "UbuntuDevEnv | \n", "2019-07-25 15:15:50 | \n", "authpriv | \n", "UbuntuDevEnv | \n", "info | \n", "pam_unix(cron:session): session closed for use... | \n", "29486.0 | \n", "10.0.1.4 | \n", "CRON | \n", "00000000-0000-0000-0000-000000000002 | \n", "Syslog | \n", "/subscriptions/3b701f84-d04b-4479-89b1-fa8827e... | \n", "
4 | \n", "b1315f05-4a7a-45b4-811f-73e715f7c122 | \n", "Linux | \n", "2019-07-25 15:16:01.800 | \n", "UbuntuDevEnv | \n", "2019-07-25 15:16:01 | \n", "authpriv | \n", "UbuntuDevEnv | \n", "info | \n", "pam_unix(cron:session): session opened for use... | \n", "29844.0 | \n", "10.0.1.4 | \n", "CRON | \n", "00000000-0000-0000-0000-000000000002 | \n", "Syslog | \n", "/subscriptions/3b701f84-d04b-4479-89b1-fa8827e... | \n", "