Kqlmagic - parametrization features¶

Explains how to emebed python values in kql queries

Make sure that you have the lastest version of Kqlmagic¶

Download Kqlmagic from PyPI and install/update (if latest version ims already installed you can skip this step)

In [ ]:

#!pip install Kqlmagic --no-cache-dir --upgrade

Add Kqlmagic to notebook magics¶

In [ ]:

%reload_ext Kqlmagic

Authenticate to get access to data¶

In [ ]:

%kql azure-data-explorer://code;cluster='help';database='Samples'

Use python user namespace as source of parameters¶

prefix query with kql let statements to parametrize the query
beware to the mapping:
int -> long
float -> real
str -> string
bool -> bool
datetime -> datetime
timedelta -> timespan
dict, list, set, tuple -> dynamic (only if can be serialized to json)
pandas dataframe -> view table
None -> null
unknown, str(value) == 'nan' -> real(null)
unknown, str(value) == 'NaT' -> datetime(null)
unknown str(value) == 'nat' -> time(null)
other -> string

In [ ]:

from datetime import datetime, timedelta
my_limit = 10
my_not_state = 'TEXAS'
my_start_datetime = datetime(2007, 8, 29)
my_timespan = timedelta(days=100)
my_dict = {"a":1}
my_list = ["x", "y", "z"]
my_tuple = ("t", 44, my_limit)
my_set = {6,7,8}

In [ ]:

%%kql 
    let _dict_ = my_dict;
    let _list_ = my_list;
    let _tuple_ = my_tuple;
    let _set_ = my_set;
    let _start_time_ = my_start_datetime;
    let _timespan_ = my_timespan;
    let _limit_ = my_limit;
    let _not_val_ = my_not_state;
    StormEvents
    | where StartTime >= _start_time_
    | where EndTime <= _start_time_ + _timespan_
    | where State != _not_val_
    | summarize count() by State
    | extend d = _dict_
    | extend l = _list_
    | extend t = _tuple_
    | extend s = _set_
    | sort by count_ 
    | limit _limit_

Dataframe prameter as a kql table¶

prefix query with kql let statement that assigns a kql var to the dataframe
beware to the mapping of the dataframe to kql table columns types :
int8,int16,int32,int64,uint8,uint16,uint32,uint64 -> long
float16,float32,float64 -> real
character -> string
bytes -> string
void -> string
category -> string
datetime,datetime64,datetime64[ns],datetime64[ns,tz] -> datetime
timedelta,timedelta64,timedelta64[ns] -> timespan
bool -> bool
record -> dynamic
complex64,complex128 -> dynamic([real, imag])
object -> if all objects of type:
- dict,list,tuple,set -> dynamic (only if can be serialized to json)
- bool or nan -> bool
- float or nan -> float
- int or nan -> long
- datetime or 'NaT' -> datetime
- timedeltae or 'NaT' -> timespan
- other -> string

In [ ]:

my_df =_kql_raw_result_.to_dataframe()

In [ ]:

my_df

In [ ]:

%%kql 
let _my_table_ = my_df;
_my_table_ | project State, s, t | limit 3

In [ ]:

_kql_raw_result_.parametrized_query

Parametrize the whole query string¶

In [ ]:

sort_col = 'count_'
my_query = """StormEvents 
    | where State != 'OHIO'
    | summarize count() by State
    | sort by {0} 
    | limit 5""".format(sort_col)

In [ ]:

%kql -query my_query

Use python dictionary as source of parameters¶

set option -params_dict with the name of a python variable that refer to the dictionary
prefix query with kql let statements to parametrize the query

In [ ]:

p_dict = {'p_limit':20, 'p_not_state':'IOWA'}

In [ ]:

%%kql 
    -params_dict p_dict
    let _limit_ = p_limit;
    let _not_val_ = p_not_state;
    StormEvents 
    | where State != _not_val_
    | summarize count() by State
    | sort by count_ 
    | limit _limit_

Use python dictionary expression as source of parameters¶

set option -params_dict with a dictionary string (python format)
prefix query with kql let statements to parametrize the query
make sure that the dictionary expression is without spaces

In [ ]:

%%kql 
    -params_dict {'p_limit':5,'p_not_state':'OHIO'}
    let _limit_ = p_limit;
    let _not_val_ = p_not_state;
    StormEvents 
    | where State != _not_val_
    | summarize count() by State
    | sort by count_ 
    | limit _limit_

get query string¶

shows the original query, as in the input cell

In [ ]:

_kql_raw_result_.query

get parametrized query string¶

shows the parametrized query, that was submited to kusto

In [ ]:

_kql_raw_result_.parametrized_query

Note - additional let statements were added to the original query, one let statement for each parameter¶

In [ ]:

p_dict = {'p_limit':5,'p_not_state':'OHIO'}

In [ ]:

%%kql 
    -params_dict p_dict
    let _limit_ = p_limit;
    let _not_val_ = p_not_state;
    StormEvents 
    | where State != _not_val_
    | summarize count() by State
    | sort by count_ 
    | limit _limit_

parameters dictionary is modified¶

In [ ]:

p_dict = {'p_limit': 5, 'p_not_state': 'IOWA'}

refresh use original parameters¶

the same parameter values are used

In [ ]:

_kql_raw_result_.refresh()

Note - the refresh method use the original parameter values, as they were set¶

submit use the current python values as parameters¶

a new query is created and parametrized with the current python values

In [ ]:

_kql_raw_result_.submit()

Note - the submit method cretes a new query and parametrize with the current parameter values¶

Parametrize option¶

all options can be parametrized. instead of providing a quoted parameter value, specify the python variable or python expression

beware, that python expression must not have spaces !!!
- valid expression examples: my_var, str(type(x)), [a,1,2]
- invalid expressions: str( type ( x ) ), [a, 1, 2]

In [ ]:

table_package = 'pandas'
my_popup_state = True

In [ ]:

%%kql -tp=table_package -pw=my_popup_state  -f=table_package!='pandas'
    StormEvents 
    | where State != 'OHIO'
    | summarize count() by State
    | sort by count_ 
    | limit 5

Parametrize commands¶

all commands can be parametrized. instead of providing a quoted parameter value, specify the python variable or python expression.

note, if instead of the python expression, you specify a variable that starts with $, it will be retreived from the environment variables.
beware, that python expression must not have spaces !!!

In [ ]:

my_topic = "kql"

In [ ]:

%kql --help my_topic

Parametrize connection string¶

all values in connection string can be parametrized. instead of providing a quoted parameter value, specify the python variable or python expression

note, if you don't specify the credential's secret you will be prompted.
note, if instead of the python expression, you specify a variable that starts with $, it will be retreived from the environment variables.
beware, that python expression must not have spaces !!!

In [ ]:

my_appid = "DEMO_APP"
my_appkey = "DEMO_KEY"

In [ ]:

%kql appinsights://appid=my_appid;appkey=my_appkey

Parametrize the whold connection string¶

In [ ]:

my_connection_str = """
loganalytics://workspace='DEMO_WORKSPACE';appkey='DEMO_KEY';alias='myworkspace'
"""

In [ ]:

%kql -conn=my_connection_str

In [ ]:

Kqlmagic - parametrization features¶

Make sure that you have the lastest version of Kqlmagic¶

Add Kqlmagic to notebook magics¶

Authenticate to get access to data¶

Use python user namespace as source of parameters¶

Dataframe prameter as a kql table¶

Parametrize the whole query string¶

Use python dictionary as source of parameters¶

Use python dictionary expression as source of parameters¶

get query string¶

get parametrized query string¶

Note - additional let statements were added to the original query, one let statement for each parameter¶

parameters dictionary is modified¶

refresh use original parameters¶

Note - the refresh method use the original parameter values, as they were set¶

submit use the current python values as parameters¶

Note - the submit method cretes a new query and parametrize with the current parameter values¶

Parametrize option¶

Parametrize commands¶

Parametrize connection string¶

Parametrize the whold connection string¶