Notebook Version: 1.0
Python Version: Python 3.6 - AzureML
Required Packages: No
Platforms Supported: Azure Machine Learning Notebooks
Data Source Required: Log Analytics tables
This notebook will provide step-by-step instructions and sample code to guide you through Azure authentication, Sentinel log data discovery by using Azure SDK for Python and Kusto Query Language (KQL).
*** No need to download and install any other Python modules. *
* Please run the cells sequentially to avoid errors. ***
Need to know more about KQL? Getting started with Kusto Query Language.
# If you need to know what Python modules are available, you may run this:
# help("modules")
# Load Python libraries that will be used in this notebook
from azure.common.client_factory import get_client_from_cli_profile
from azure.common.credentials import get_azure_cli_credentials
from azure.loganalytics.models import QueryBody
from azure.mgmt.loganalytics import LogAnalyticsManagementClient
from azure.loganalytics import LogAnalyticsDataClient
from pandas.io.json import json_normalize
import json
import ipywidgets
import matplotlib.pyplot as plt
# Functions will be used in this notebook
def read_config_values(file_path):
"This loads pre-generated parameters for Sentinel Workspace"
with open(file_path) as json_file:
if json_file:
json_config = json.load(json_file)
return (json_config["tenant_id"],
json_config["subscription_id"],
json_config["resource_group"],
json_config["workspace_id"],
json_config["workspace_name"])
return None
def process_result(result):
"This function processes data returned from Azure LogAnalyticsDataClient, it returns pandas DataFrame."
json_result = result.as_dict()
cols = json_normalize(json_result['tables'][0], 'columns')
final_result = json_normalize(json_result['tables'][0], 'rows')
if final_result.shape[0] != 0:
final_result.columns = cols.name
return final_result
# Calling the above function to populate Sentinel workspace parameters
# The file, config.json, was generated by the system, however, you may modify the values, or manually set the variables
tenant_id, subscription_id, resource_group, workspace_id, workspace_name = read_config_values('config.json');
# Azure CLI is used to get device code to login into Azure, you need to copy the code and open the DeviceLogin site.
# You may add [--tenant $tenant_id] to the command
!az login --tenant $tenant_id --use-device-code
# Initialzie Azure LogAnalyticsDataClient, which is used to access Sentinel log data in Azure Log Analytics.
# You may need to change resource_uri for various cloud environments.
resource_uri = "https://api.loganalytics.io"
la_client = get_client_from_cli_profile(LogAnalyticsManagementClient, subscription_id = subscription_id)
creds, _ = get_azure_cli_credentials(resource=resource_uri)
la_data_client = LogAnalyticsDataClient(creds)
# Get all tables available using Kusto query language. If you need to know more about KQL, please check out the link provided at the introductory section.
tables_result = None
table_list = None
all_tables_query = "union withsource = SentinelTableName * | distinct SentinelTableName | sort by SentinelTableName asc"
if la_data_client != None:
tables_result = la_data_client.query(workspace_id, QueryBody(query=all_tables_query))
# Process the data using above function, then convert DataFrame to list
if tables_result != None:
table_list = process_result(tables_result)
tables = sorted(table_list.SentinelTableName.tolist())
table_dropdown = ipywidgets.Dropdown(options=tables, description='Tables:')
display(table_dropdown)
# You may query the table based on your needs, here I use TimeGenerated column as an example, going back to 7 days, counting events per day
# Then process the data and display the result
# To look at the query, you may run: print(sample_query)
date_column_name = "TimeGenerated"
count_column_name = "Count"
if table_list.empty == False:
table_name = table_dropdown.value
sample_query = "{0} | where {1} >= ago(7d) | summarize {2}=count() by format_datetime({1}, 'yyyy-M-dd') | order by {1} asc".format(table_name, date_column_name, count_column_name)
print("Query:" + sample_query)
print("===================")
result_sample = la_data_client.query(workspace_id, QueryBody(query=sample_query))
sample_result = process_result(result_sample)
print(sample_result)
# Then plot a bar chart
if sample_result.empty == False:
plt.bar(sample_result[date_column_name], sample_result[count_column_name])
plt.rcParams['figure.figsize'] = [14,2.5]
# Here we are going to use the Sentinel Watchlist name that you got in previous Hands-on notebook to get all Watchlist items
# First, please set the watchlist_name
watchlist_name = ipywidgets.Text(value='[[YOUR WATCHLIST NAME]]',description='watchlist_name: ')
display(watchlist_name)
# Using Aazure SDK for Python: LogAnalyticsDataClient to get items
watchlist_query = "_GetWatchlist('{0}')".format(watchlist_name.value)
result_watchlist = la_data_client.query(workspace_id, QueryBody(query=watchlist_query))
my_watchlist_items = process_result(result_watchlist)
print(my_watchlist_items)