{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
" # Kqlmagic - __parametrization__ features\n",
"***\n",
"Explains how to emebed python values in kql queries\n",
"***\n",
"***\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Make sure that you have the lastest version of Kqlmagic\n",
"Download Kqlmagic from PyPI and install/update\n",
"(if latest version ims already installed you can skip this step)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"#!pip install Kqlmagic --no-cache-dir --upgrade"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Add Kqlmagic to notebook magics"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%reload_ext Kqlmagic"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Authenticate to get access to data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql azure-data-explorer://code;cluster='help';database='Samples'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Use python user namespace as source of parameters\n",
"- prefix query with **kql let statements** to parametrize the query\n",
"- beware to the mapping: \n",
" - int -> long \n",
" - float -> real\n",
" - str -> string\n",
" - bool -> bool\n",
" - datetime -> datetime\n",
" - timedelta -> timespan\n",
" - dict, list, set, tuple -> dynamic (only if can be serialized to json)\n",
" - **pandas dataframe -> view table**\n",
" - None -> null\n",
" - unknown, str(value) == 'nan' -> real(null)\n",
" - unknown, str(value) == 'NaT' -> datetime(null)\n",
" - unknown str(value) == 'nat' -> time(null)\n",
" - other -> string"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from datetime import datetime, timedelta\n",
"my_limit = 10\n",
"my_not_state = 'TEXAS'\n",
"my_start_datetime = datetime(2007, 8, 29)\n",
"my_timespan = timedelta(days=100)\n",
"my_dict = {\"a\":1}\n",
"my_list = [\"x\", \"y\", \"z\"]\n",
"my_tuple = (\"t\", 44, my_limit)\n",
"my_set = {6,7,8}"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" let _dict_ = my_dict;\n",
" let _list_ = my_list;\n",
" let _tuple_ = my_tuple;\n",
" let _set_ = my_set;\n",
" let _start_time_ = my_start_datetime;\n",
" let _timespan_ = my_timespan;\n",
" let _limit_ = my_limit;\n",
" let _not_val_ = my_not_state;\n",
" StormEvents\n",
" | where StartTime >= _start_time_\n",
" | where EndTime <= _start_time_ + _timespan_\n",
" | where State != _not_val_\n",
" | summarize count() by State\n",
" | extend d = _dict_\n",
" | extend l = _list_\n",
" | extend t = _tuple_\n",
" | extend s = _set_\n",
" | sort by count_ \n",
" | limit _limit_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Dataframe prameter as a kql table\n",
"- prefix query with **kql let statement** that assigns a kql var to the dataframe\n",
"- beware to the mapping of the dataframe to kql table columns types : \n",
" - int8,int16,int32,int64,uint8,uint16,uint32,uint64 -> long \n",
" - float16,float32,float64 -> real\n",
" - character -> string\n",
" - bytes -> string\n",
" - void -> string\n",
" - category -> string\n",
" - datetime,datetime64,datetime64[ns],datetime64[ns,tz] -> datetime\n",
" - timedelta,timedelta64,timedelta64[ns] -> timespan\n",
" - bool -> bool\n",
" - record -> dynamic\n",
" - complex64,complex128 -> dynamic([real, imag])\n",
" - object -> if all objects of type:\n",
" - dict,list,tuple,set -> dynamic (only if can be serialized to json)\n",
" - bool or nan -> bool\n",
" - float or nan -> float\n",
" - int or nan -> long\n",
" - datetime or 'NaT' -> datetime\n",
" - timedeltae or 'NaT' -> timespan\n",
" - other -> string"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_df =_kql_raw_result_.to_dataframe()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
"let _my_table_ = my_df;\n",
"_my_table_ | project State, s, t | limit 3"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.parametrized_query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Parametrize the whole query string"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"sort_col = 'count_'\n",
"my_query = \"\"\"StormEvents \n",
" | where State != 'OHIO'\n",
" | summarize count() by State\n",
" | sort by {0} \n",
" | limit 5\"\"\".format(sort_col)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql -query my_query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Use python dictionary as source of parameters\n",
"- set option -params_dict with the name of a python variable that refer to the dictionary\n",
"- prefix query with kql let statements to parametrize the query"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"p_dict = {'p_limit':20, 'p_not_state':'IOWA'}"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" -params_dict p_dict\n",
" let _limit_ = p_limit;\n",
" let _not_val_ = p_not_state;\n",
" StormEvents \n",
" | where State != _not_val_\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit _limit_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Use python dictionary expression as source of parameters\n",
"- set option -params_dict with a dictionary string (python format)\n",
"- prefix query with kql let statements to parametrize the query\n",
"- **make sure that the dictionary expression is without spaces**"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" -params_dict {'p_limit':5,'p_not_state':'OHIO'}\n",
" let _limit_ = p_limit;\n",
" let _not_val_ = p_not_state;\n",
" StormEvents \n",
" | where State != _not_val_\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit _limit_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## get query string\n",
"- shows the original query, as in the input cell"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## get parametrized query string\n",
"- shows the parametrized query, that was submited to kusto"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.parametrized_query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### *Note - additional let statements were added to the original query, one let statement for each parameter*"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"p_dict = {'p_limit':5,'p_not_state':'OHIO'}"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" -params_dict p_dict\n",
" let _limit_ = p_limit;\n",
" let _not_val_ = p_not_state;\n",
" StormEvents \n",
" | where State != _not_val_\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit _limit_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## parameters dictionary is modified"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"p_dict = {'p_limit': 5, 'p_not_state': 'IOWA'}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## refresh use original parameters\n",
"- the same parameter values are used"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.refresh()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### *Note - the refresh method use the original parameter values, as they were set*"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## submit use the current python values as parameters\n",
"- a new query is created and parametrized with the current python values"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.submit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### *Note - the submit method cretes a new query and parametrize with the current parameter values*"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Parametrize option\n",
"all options can be parametrized.\n",
"instead of providing a quoted parameter value, specify the python variable or python expression\n",
"\n",
"- beware, that python expression must not have spaces !!! \n",
" - valid expression examples: ```my_var```, ```str(type(x))```, ```[a,1,2]```\n",
" - invalid expressions: ```str( type ( x ) )```, ```[a, 1, 2]```"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"table_package = 'pandas'\n",
"my_popup_state = True"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql -tp=table_package -pw=my_popup_state -f=table_package!='pandas'\n",
" StormEvents \n",
" | where State != 'OHIO'\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit 5"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Parametrize commands\n",
"all commands can be parametrized.\n",
"instead of providing a quoted parameter value, specify the python variable or python expression.\n",
"\n",
"- **note**, if instead of the python expression, you specify a variable that starts with $, it will be retreived from the environment variables.
\n",
"- **beware**, that python expression must not have spaces !!!"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_topic = \"kql\""
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql --help my_topic"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Parametrize connection string\n",
"all values in connection string can be parametrized.\n",
"instead of providing a quoted parameter value, specify the python variable or python expression\n",
"\n",
"- **note**, if you don't specify the credential's secret you will be prompted.\n",
"- **note**, if instead of the python expression, you specify a variable that starts with $, it will be retreived from the environment variables.
\n",
"\n",
"- beware, that python expression must not have spaces !!!"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_appid = \"DEMO_APP\"\n",
"my_appkey = \"DEMO_KEY\""
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql appinsights://appid=my_appid;appkey=my_appkey"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Parametrize the whold connection string"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_connection_str = \"\"\"\n",
"loganalytics://workspace='DEMO_WORKSPACE';appkey='DEMO_KEY';alias='myworkspace'\n",
"\"\"\""
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql -conn=my_connection_str"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"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.1"
}
},
"nbformat": 4,
"nbformat_minor": 2
}