{
 "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
}