Get Started

Notebook Version: 1.0
Python Version: Python 3.6 (including Python 3.6 - AzureML)
Required Packages: Kqlmagic 0.1.90
Platforms Supported:

-  Azure Notebooks Free Compute
-  Azure Notebooks DSVM

Data Source Required:

-  Log Analytics - SecurityEvent

Description

The notebook provides you basic knowledge to use Azure Sentinel Notebooks.

When you switch between Azure Notebooks Free Compute and Data Science Virtual Machine (DSVM), you may need to select Python version: please select Python 3.6 for Free Compute, and Python 3.6 - AzureML for DSVM.

Prerequisite check

In [ ]:
# only run once, current version 0.1.2
!pip install --upgrade Sentinel-Utilities
In [ ]:
import SentinelUtils
# checking Python version
check = SentinelUtils.version_management.ModuleVersionCheck()
py_check = check.validate_python('3.6.0')
if py_check.requirement_met == False:
    print('Please select Python 3.6 or Python 3.6 - AzureML at the upper right corner')
else:
    print('Please continue')
In [ ]:
# checking required packages
mods_check = check.validate_installed_modules(['Kqlmagic>=0.1.90'])
for mod_info in mods_check:
    if mod_info.requirement_met == False:
        print('Please install {} {} at the following cell.'.format(mod_info.name, mod_info.required_version))
In [ ]:
# Please install required packages based on the check at last cellp
#!pip install Kqlmagic --upgrade

Table of Contents

  1. Retrieve Log Analytics information
  2. Log into Log Analytics
  3. Get a list of available data tables from Log Analytics
  4. Run a simple Kusto query

1. Retrieve Log Analytics Information

In [ ]:
path = %env PATH
dsvm = False
if '/dsvm/' in path:
    dsvm = True
    
if dsvm == False:
    # Run this if you are using Free Compute
    tenant_id = SentinelUtils.config_reader.ConfigReader.read_config_values("config.json")[0]
    subscription_id = SentinelUtils.config_reader.ConfigReader.read_config_values("config.json")[1]
    resource_group = SentinelUtils.config_reader.ConfigReader.read_config_values("config.json")[2]
    workspace_id = SentinelUtils.config_reader.ConfigReader.read_config_values("config.json")[3]
    workspace_name = SentinelUtils.config_reader.ConfigReader.read_config_values("config.json")[4]
    print('Your Log Analytic Workspace: {}'.format(workspace_name))
else:
    # Run this if you are using DSVM.  You need to copy the values from config.json, if the file has no value, then you need to go to Log Analytics Portal to get the information.
    tenant_id = input('tenant_id:')
    subscription_id = input('subscription_id:')
    resource_group = input('resource_group:')
    workspace_id = input('workspace_id:')
    workspace_name = input('workspace_name:')

2. Log into Log Analytics

In [ ]:
# You must run this cell to log into Log Analytics to continue
# Make sure you have 0.1.90 or above, if not, run Kqlmagic installation again
%reload_ext Kqlmagic
%kql loganalytics://code;workspace=workspace_id;tenant=tenant_id;alias="SentinelDB"

3. Get a list of available data tables from Log Analytics

  1. Tables with records
In [ ]:
%kql search * | summarize count() by Type
  1. All Tables in drop down
In [ ]:
import ipywidgets as widgets
from IPython.display import display
dbSchema = %kql --schema "[email protected]"
tables = list(dbSchema.keys())
selected_table = widgets.Dropdown(options=tables, value=tables[1],description='Data Table:')
display(selected_table)
  1. Choose columns from the selected table
In [ ]:
columns = list(dbSchema[selected_table.value])
columns.sort()
selected_columns = widgets.SelectMultiple(options=columns, value=[],description='Data Columns:')
display(selected_columns)
  1. List unique values for selected columns
In [ ]:
%kql {selected_table.value} | where TimeGenerated >= ago(30d) | take 1000
if len(_kql_raw_result_) > 0:
    df = _kql_raw_result_.to_dataframe()
else:
    df = None
    print('no data')
In [ ]:
for index, column in enumerate(selected_columns.value):
    unique_values = df[column].drop_duplicates().sort_values()
    values_dropdown = widgets.Dropdown(options=unique_values, description=column)
    display(values_dropdown)
  1. Search a term
In [ ]:
# scope to a table and a column
%kql search in ({selected_table.value}) {selected_columns.value[0]}: '*local service' | take 5

4. Run a simple Kusto query

In [ ]:
# Checking required Data Sources
required_data_sources = ['SecurityEvent']
dbSchema = %kql --schema "[email protected]"
tables = list(dbSchema.keys())
for source in required_data_sources:
    if source not in tables:
        print('You do not have required data source: {}'.format(source))
    else:
        print('Please continue')
In [ ]:
%kql SecurityEvent | take 5 | project Account, Computer
df = _kql_raw_result_.to_dataframe()
df