{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"![alt text](images/Kusto.jpg \"Kusto Magic\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Kqlmagic Quick Start\n",
"1. Press F11 to switch browser to full screen mode \n",
"2. If in running jupyter lab, in View tab, select 'Single-Document Mode' and 'Presentation Mode' or (Ctrl + Shift + D) \n",
"3. In Kernel tab, select 'Restart Kernel and Clear All Outputs...', to start from fresh\n",
"4. Repeat: press Shift+Enter to run cells sequentially"
]
},
{
"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 is 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": [
"## If you want to pre-set defaults, set environment varaible, before activation notebook, or within notebook\n",
"- **KQLMAGIC_NOTEBOOK_APP** - set the notebook application (default **jupyternotebook**)\n",
"- **KQLMAGIC_LOAD_MODE** - set the kqlmagic load mode (default **verbose**). If silent, it will load without welcome output\n",
"- **KQLMAGIC_CONFIGURATION** - modify the default configuration, key=value pairs must be separated by a **semicolon**\n",
"\n",
"- **KQLMAGIC_CONNECTION_STR** - set the default connection string (default **None**). If set it will connect to it on Kqlmagic load"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# you can try it within the notebook, by setting the environment variable using %env magic\n",
"\n",
"#%env KQLMAGIC_NOTEBOOK_APP=jupyterlab\n",
"#%env KQLMAGIC_LOAD_MODE=silent\n",
"#%env KQLMAGIC_CONFIGURATION=\"show_query_time=False;plot_package='plotly';display_limit=100\"\n",
"#%env KQLMAGIC_CONFIGURATION=\"show_init_banner=True;check_magic_version=False;show_what_new=False\"\n",
"\n",
"#%env KQLMAGIC_CONNECTION_STR=AzureDataExplorer://username='michabin@microsoft.com';cluster='help';database='Samples'\n",
"#%env KQLMAGIC_CONNECTION_STR=AzureDataExplorer://code;cluster='help';database='Samples'\n",
"\n",
"#%env KQLMAGIC_LOG_LEVEL=DEBUG\n",
"#%env KQLMAGIC_LOG_FILE_MODE=Append\n",
"#%env KQLMAGIC_LOG_FILE=michael.log\n",
"#%env KQLMAGIC_LOG_FILE_PREFIX=myLog\n",
"#%env KQLMAGIC_DEVICE_CODE_NOTIFICATION_EMAIL=SMTPEndPoint='endpoint';SMTPPort='port';sendFrom='from';sendFromPassword='password';sendTo='to';context='text'\n",
"\n",
"# For more information about configuration options, you can run the following command:\n",
"#%kql --config"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Add Kqlmagic to notebook magics"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"%reload_ext Kqlmagic"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: information is included in the kqlmagic banner, and in the note below it, with version and location of the open source code*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: after kql magic was loaded, kql reference was added to Help menu (Help tab)*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get help"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql --help \"help\" -popup_window"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get Azure Data Explorer reference"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql --help \"AzureDataExplorer\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Connect to database@cluster\n",
"there are few options to authenticate with Azure Data Explorer (Kusto):<br>\n",
"```python\n",
"%kql azure_data-Explorer://code;cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"```python\n",
"%kql azure_data-Explorer://tenant='<tenant-id>';clientid='<aad-appid>';clientsecret='<aad-appkey>';cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"```python\n",
"%kql azure_data-Explorer://tenant='<tenant-id>';certificate='<certificate>';certificate_thumbprint='<thumbprint>';cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"```python\n",
"%kql azure_data-Explorer://tenant='<tenant-id>';certificate_pem_file='<pem_filename>';certificate_thumbprint='<thumbprint>';cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"```python\n",
"%kql azure_data-Explorer://username='<username>';password='<password>';cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"```python\n",
"%kql azureDataExplorer://anonymous;cluster='<cluster-name>';database='<database-name>';alias='<database-friendly-name>'\n",
"```\n",
"\n",
"Notes:<br>\n",
"(1) username/password works only on corporate network.<br>\n",
"(2) alias is optional.<br>\n",
"(3) if credentials are missing, and a previous connection was established the credentials will be inherited.<br>\n",
"(4) if secret (password / clientsecret / thumbprint) is missing, user will be prompted to provide it.<br>\n",
"(5) if cluster is missing, and a previous connection was established the cluster will be inherited.<br>\n",
"(6) if tenant is missing, and a previous connection was established the tenant will be inherited.<br>\n",
"(7) if only the database change, a new connection can be set as follow: \n",
"```<new-database-name>@<cluster-name>```<br>\n",
"(8) **a not quoted value, is a python expression, that is evaluated and its result is used as the value. This is how you can parametrize the connection string** \n",
"<br>(9) anonymous authentication, is NO authentication, for the case that your cluster is local."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"%kql azureDataExplorer://code;cluster='help';database='Samples' // -try_azcli_login"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note - popup window that opened with the database@cluster schema*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note - Help menu contains the database@cluster schema*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get help on connection string, how to authenticate and get access to you data resource"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql --help \"conn\" -popup_window"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Once connected you can query the current database@cluster"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql StormEvents | summarize count() by State | sort by count_ | limit 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Python code can access the result by the \\_ variable"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Converts result data to a dataframe"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = _.to_dataframe()\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.State"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.min()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.to_json()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.columns"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Excution of another cell overrides \\_ , However, \\_kql\\_raw\\_result\\_ python variable holds last kql query result "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show a chart, render the query to the proper chart"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql -sql -qld='Kusto.Explorer' StormEvents | summarize count() by State | sort by count_ | limit 10 | render piechart title='my apple pie' "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: the chart is interactive, hoover to the right upper corner to get the menu, click on the legend to toggle facets, hoover on the chart to get value and percentage*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Assign kql query result to a python variable"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql my_bar_chart << StormEvents | summarize count() by State | sort by count_ | limit 10 | render barchart title='my bar chart'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: nothing is displayed when the result is assigtned to a variable*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Display kql result object"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Access table data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart.show_table()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart[1]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart[1][1:]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart[1]['State']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"my_bar_chart[1:5]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events = %kql StormEvents | project StartTime, State, StormSummary | limit 3\n",
"storm_events"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1:3]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1]['StartTime']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1]['State']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1]['StormSummary']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1]['StormSummary']['Details']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"storm_events[1]['StormSummary']['Details']['Description']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: that you can access directly dynamic object data.*</span>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = storm_events.to_dataframe()\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df['StormSummary'][0]['Details']['Description']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: that access to data in dataframe is different, you first specify the column and then the row.*</span>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df['StartTime'][1]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: that datetime in dataframe is Timestamp.*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Cell mixed with single line kql queries "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql bar_chart << StormEvents | summarize count() by State | sort by count_ | limit 10 | render barchart title='my bar chart'\n",
"print(bar_chart)\n",
"%kql pie_chart << StormEvents | summarize count() by State | sort by count_ | limit 10 | render piechart title='my pie chart'\n",
"display(bar_chart)\n",
"display(pie_chart)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: single line magics (starts with only one \\%) can share the same cell with python code*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Multiline query "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit 10 \n",
" | render columnchart title='my column chart' "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: that this is a multiline magic, it starts with \\%\\%. It can include only kql code*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show last chart result as a table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_.show_table()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show last result in a popup window"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_.popup()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Resize poup window, and to adjust the chart inside the window press F5"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: when you F5, you loose the latest chart display state*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show last chart result as a table in a popup window"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.popup_table()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## More complex chart"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | extend count2 = count_*count_ \n",
" | limit 10 \n",
" | render piechart title='my pie chart #2' "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Convert result data to dictionary of columns"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"dict_of_columns = _kql_raw_result_.to_dict()\n",
"print(dict_of_columns)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.to_csv('csv_file.csv')\n",
"#%cat csv_file.csv"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Get last kql query attributes "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query string"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query render title"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.title"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query render visualization"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.visualization"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Number of records in query response "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.records_count"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## List of columns in query table response"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.columns_name"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Kqlmagic time metric (in seconds)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.start_time"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.end_time"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.elapsed_timespan"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query connection database@cluster"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.connection"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query Options (settings) "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.options"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Completion query information (query metadata)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.completion_query_info"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Completion query resource consumption (query metadata)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.completion_query_resource_consumption"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.completion_query_resource_consumption['resource_usage']['memory']['peak_per_node']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Completion query data set info (query metadata)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
" _kql_raw_result_.dataSetCompletion"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Query raw json response"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"_kql_raw_result_.raw_json"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.raw_json[0]['Version']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.raw_json[1]['Columns']"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.raw_json[1]['Columns'][1]['ColumnType']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Advanced part"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Kql query with option -c2lv (-columns_to_local_vars)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql -c2lv \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit 10 \n",
" | render columnchart title='my column chart' "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: option are specified before the query*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: c2lv is abbreviation of columns_to_local_vars options*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: columns_to_local_vars option returns data result in local variables from column names*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### result data is assigned to local variables with columns names"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"State"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"count_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note - current database@cluster is always shown*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Kql query with negated option -f (-feedback)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql -!feedback \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | limit 10 \n",
" | render barchart title='my bar chart'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: the feedback message 'Done..... records' was not displayed because of the negated feedback option*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: option is negated by adding \\! before the option*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: full option name instead of abbreviation can be used too*</span>\n",
" - ### <span style=\"color:#82CAFA\">*Note: feedback option is on by default, so to turn it off, it must be negated*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get default value of option feedback (f)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%config Kqlmagic.feedback"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: options have defaults, and can be accessed to read or modify using %config magic*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Set default value of option feedback (f) to False"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%config Kqlmagic.feedback=False\n",
"%config Kqlmagic.feedback"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | extend count2 = count_*count_ \n",
" | limit 5"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: feedback message was not displayed, because the default feedback option was set to False*</span>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%config Kqlmagic.feedback=True\n",
"%config Kqlmagic.feedback"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show all option defaults with help"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%config Kqlmagic"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Show query results in a popup window"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql -pw \n",
" let randn = () {rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand() + rand()};\n",
"range c from 1 to 1000 step 1 \n",
" | limit 1000\n",
" | extend e1 = randn()\n",
" | extend e2 = randn()\n",
" | extend e3 = randn()\n",
" | extend e4 = randn()\n",
" | extend e5 = randn()\n",
" | project c, e1,e2,e3,e4,e5\n",
" | render scatterchart title=\"Michael's chart\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - ### <span style=\"color:#82CAFA\">*Note: popup_window option (abbr: pw) cause the result to be displayed in a popup window*</span>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Empty table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%kql StormEvents | summarize count() by State | sort by count_ | extend count2 = count_*count_ | limit 0"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | extend count2 = count_ * count_ \n",
" | limit 0 \n",
" | render piechart title='my chart'"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"len(_kql_raw_result_)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.popup()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.show_table()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = _kql_raw_result_.to_dataframe()\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.to_json()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"_kql_raw_result_.raw_json"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Kql request with fork - return multiple tables"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%kql \n",
" StormEvents\n",
" | summarize count() by State\n",
" | sort by count_ \n",
" | extend count2 = count_ * count_ \n",
" | fork (limit 10) (project State | limit 5)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.fork_result(1)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Submit same query"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
" _kql_raw_result_.submit()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
" _kql_raw_result_.fork_result(1)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Refresh query"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.fork_result(1).refresh()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_.fork_result(0)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"_kql_raw_result_"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernel_info": {
"name": "python3"
},
"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.11.9"
},
"nteract": {
"version": "0.15.0"
}
},
"nbformat": 4,
"nbformat_minor": 2
}