Kusto Query Language

https://learn.microsoft.com/en-us/kusto
Admin
Kusto Query Language (KQL) is a powerful and versatile tool for exploring, analyzing, and querying...
Tokens:879,216
Snippets:9,093
Trust Score:10
Update:1 month ago
Show doc for...
Tokens:
Raw
### Complete query execution example

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-basic-query

Full implementation including client connection setup and query execution.

```C#
using Kusto.Data;
using Kusto.Data.Net.Client;

namespace BasicQuery {
  class BasicQuery {
    static void Main(string[] args) {
      string clusterUri = "https://help.kusto.windows.net/";
      var kcsb = new KustoConnectionStringBuilder(clusterUri)
          .WithAadUserPromptAuthentication();

      using (var kustoClient = KustoClientFactory.CreateCslQueryProvider(kcsb)) {
        string database = "Samples";
        string query = @"StormEvents
                         | where EventType == 'Tornado'
                         | extend TotalDamage = DamageProperty + DamageCrops
                         | summarize DailyDamage=sum(TotalDamage) by State, bin(StartTime, 1d)
                         | where DailyDamage > 100000000
                         | order by DailyDamage desc";

        using (var response = kustoClient.ExecuteQuery(database, query, null)) {
          int columnNoStartTime = response.GetOrdinal("StartTime");
          int columnNoState = response.GetOrdinal("State");
          int columnNoDailyDamage = response.GetOrdinal("DailyDamage");

          Console.WriteLine("Daily tornado damages over 100,000,000$:");

          while (response.Read()) {
            Console.WriteLine("{0} - {1}, {2}",
              response.GetDateTime(columnNoStartTime),
              response.GetString(columnNoState),
              response.GetInt64(columnNoDailyDamage));
          }
        }
      }
    }
  }
}
```

```Python
from azure.kusto.data import KustoClient, KustoConnectionStringBuilder

def main():
  cluster_uri = "https://help.kusto.windows.net"
  kcsb = KustoConnectionStringBuilder.with_interactive_login(cluster_uri)

  with KustoClient(kcsb) as kusto_client:

    database = "Samples"
    query = "StormEvents" \
            "| where EventType == 'Tornado'" \
            "| extend TotalDamage = DamageProperty + DamageCrops" \
            "| summarize DailyDamage=sum(TotalDamage) by State, bin(StartTime, 1d)" \
            "| where DailyDamage > 100000000" \
            "| order by DailyDamage desc"

    response = kusto_client.execute(database, query)

    print("Daily tornado damages over 100,000,000$:")
    for row in response.primary_results[0]:
      print(row["StartTime"], "-", row["State"], ",", row["DailyDamage"], "$")

if __name__ == "__main__":
  main()
```

--------------------------------

### Launching Kusto.Explorer with command-line arguments

Source: https://learn.microsoft.com/en-us/kusto/tools/kusto-explorer-using

Examples of starting Kusto.Explorer with a local script file and cluster connection parameters.

```Kusto
Kusto.Explorer.exe c:\temp\script.kql "uri=https://help.kusto.windows.net/Samples;Fed=true&name=Samples"
```

```Kusto
Kusto.Explorer.exe c:\temp\script.kql "uri=https://<baseaddress>/groups/<GroupID>/databases/<DatabaseID>"
```

--------------------------------

### Example Request and Response

Source: https://learn.microsoft.com/en-us/kusto/management/data-ingestion/queued-ingest-configuration-http

Provides a sample GET request and the corresponding JSON response containing container and ingestion settings.

```HTTP
GET /v1/rest/ingestion/configuration
Authorization: Bearer <access_token>
```

```JSON
{
  "containerSettings": {
    "containers": [
      {
        "path": "https://mystorageaccount.blob.core.windows.net/ingestdata?sv=2021-06-08&ss=b&srt=sco&sp=rwdlacx&se=2025-12-31T23:59:59Z&st=2025-01-01T00:00:00Z&spr=https&sig=..."
      }
    ],
    "lakeFolders": [
      {
        "path": "https://onelake.dfs.fabric.microsoft.com/workspace/artifact/Files"
      }
    ],
    "refreshInterval": "01:00:00",
    "preferredUploadMethod": "Storage"
  },
  "ingestionSettings": {
    "maxBlobsPerBatch": 20,
    "maxDataSize": 6442450944,
    "preferredIngestionMethod": "REST"
  }
}
```

--------------------------------

### Install dependencies and build the project

Source: https://learn.microsoft.com/en-us/kusto/api/monaco/monaco-kusto

Run this command from the root of the cloned repository to install required dependencies.

```bash
npm install
```

--------------------------------

### Kusto !startswith operator comparison table examples

Source: https://learn.microsoft.com/en-us/kusto/query/not-startswith-operator

Examples demonstrating the behavior of various startswith-related operators.

```Kusto
"Fabrikam" startswith "fab"
```

```Kusto
"Fabrikam" !startswith "kam"
```

```Kusto
"Fabrikam" startswith_cs "Fab"
```

```Kusto
"Fabrikam" !startswith_cs "fab"
```

--------------------------------

### Alter table partitioning policy examples

Source: https://learn.microsoft.com/en-us/kusto/management/alter-table-partitioning-policy-command

Examples demonstrating how to set hash, uniform range, and combined partition keys for a table.

```Kusto
.alter table [table_name] policy partitioning 
{
  "PartitionKeys": [
    {
      "ColumnName": "my_string_column",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "PartitionAssignmentMode": "Uniform"
      }
    }
  ]
}
```

```Kusto
.alter table [table_name] policy partitioning 
{
  "PartitionKeys": [
    {
      "ColumnName": "my_datetime_column",
      "Kind": "UniformRange",
      "Properties": {
        "Reference": "1970-01-01T00:00:00",
        "RangeSize": "1.00:00:00",
        "OverrideCreationTime": false
      }
    }
  ]
}
```

```Kusto
.alter table [table_name] policy partitioning 
{
  "PartitionKeys": [
    {
      "ColumnName": "my_string_column",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "PartitionAssignmentMode": "Uniform"
      }
    },
    {
      "ColumnName": "my_datetime_column",
      "Kind": "UniformRange",
      "Properties": {
        "Reference": "1970-01-01T00:00:00",
        "RangeSize": "1.00:00:00",
        "OverrideCreationTime": false
      }
    }
  ]
}
```

--------------------------------

### Install Monaco Editor and Kusto package

Source: https://learn.microsoft.com/en-us/kusto/api/monaco/monaco-kusto

Commands to install the required npm packages.

```bash
npm i monaco-editor
```

```bash
npm i @kusto/monaco-kusto
```

--------------------------------

### Graph Query Examples

Source: https://learn.microsoft.com/en-us/kusto/management/graph/graph-model-overview

Examples demonstrating node-edge-node patterns and complex relationship filtering.

```Kusto
// Find people who commented on posts by employees in the last week
graph("SocialNetwork") 
| graph-match (person)-[comments]->(post)<-[authored]-(employee)
    where person.age > 30 
      and comments.createTime > ago(7d)
    project person.name, post.title, employee.userName
```

```Kusto
// Find people who both work with and are friends with each other
graph("ProfessionalNetwork") 
| graph-match (p1)-[worksWith]->(p2)-[friendsWith]->(p1)
    where labels(worksWith) has "WORKS_WITH" and labels(friendsWith) has "FRIENDS_WITH" and
      labels(p1) has "Person" and labels(p2) has "Person"
    project p1.name, p2.name, p1.department
```

--------------------------------

### Two-step purge example

Source: https://learn.microsoft.com/en-us/kusto/concepts/data-purge

Example of initiating a two-step purge and completing it with a verification token.

```Kusto
// Connect to the Data Management service
 #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"

.purge table MyTable in database MyDatabase allrecords
```

```Kusto
.purge table MyTable in database MyDatabase allrecords
with (verificationtoken=h'eyJT.....')
```

--------------------------------

### Example Query Output

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-basic-query

Sample output format for the basic query execution.

```bash
Daily damages tornado with damages over 100,000,000$:
2007-02-02 00:00:00+00:00 - FLORIDA , 270004000 $
2007-03-01 00:00:00+00:00 - ALABAMA , 266853000 $
2007-05-04 00:00:00+00:00 - KANSAS , 251625000 $
2007-03-01 00:00:00+00:00 - GEORGIA , 143688000 $
```

--------------------------------

### Alter table folder examples

Source: https://learn.microsoft.com/en-us/kusto/management/alter-table-folder-command

Examples demonstrating how to update a table's folder path using simple strings or escaped strings for nested folder structures.

```Kusto
.alter table MyTable folder "Updated folder"
```

```Kusto
.alter table MyTable folder @"First Level\Second Level"
```

--------------------------------

### Run Vite-React Application

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-management-commands

Starts the development server for a Vite-React application.

```bash
npm run dev
```

--------------------------------

### countof() function examples

Source: https://learn.microsoft.com/en-us/kusto/query/countof-function

Examples demonstrating string and regex matching behavior, including overlapping matches for plain strings.

```kusto
countof("aaa", "a")
```

```kusto
countof("aaaa", "aa")
```

```kusto
countof("ababa", "ab", "normal")
```

```kusto
countof("ababa", "aba")
```

```kusto
countof("ababa", "aba", "regex")
```

```kusto
countof("abcabc", "a.c", "regex")
```

--------------------------------

### Show purges command examples

Source: https://learn.microsoft.com/en-us/kusto/concepts/data-purge

Practical examples of the .show purges command used to retrieve operation status with various filters.

```Kusto
.show purges
.show purges c9651d74-3b80-4183-90bb-bbe9e42eadc4
.show purges from '2018-01-30 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00' in database MyDatabase
```

--------------------------------

### Calculate start of day with offsets

Source: https://learn.microsoft.com/en-us/kusto/query/startofday-function

Demonstrates using the startofday function with a range of offsets to return the start of the day for dates before, during, and after the input datetime.

```Kusto
range offset from -1 to 1 step 1
| project dayStart = startofday(datetime(2017-01-01 10:10:17), offset) 
```

--------------------------------

### Calculate start of month with offsets

Source: https://learn.microsoft.com/en-us/kusto/query/startofmonth-function

Demonstrates using the startofmonth function with a range of offsets to return the start of the previous, current, and next months.

```Kusto
range offset from -1 to 1 step 1
| project monthStart = startofmonth(datetime(2017-01-01 10:10:17), offset) 
```

--------------------------------

### Install and Use External Artifacts in Python Plugin

Source: https://learn.microsoft.com/en-us/kusto/query/python-plugin

Demonstrates installing a zipped Python package from an external artifact URL and using it within a Kusto python() evaluation.

```Kusto
range ID from 1 to 3 step 1 
| extend Name=''
| evaluate python(typeof(*), ```if 1:
    from sandbox_utils import Zipackage
    Zipackage.install("Faker.zip")
    from faker import Faker
    fake = Faker()
    result = df
    for i in range(df.shape[0]):
        result.loc[i, "Name"] = fake.name()
    ```,
    external_artifacts=bag_pack('faker.zip', 'https://artifacts.blob.core.windows.net/Faker.zip;impersonate'))
```

--------------------------------

### Install and use external artifacts in Kusto

Source: https://learn.microsoft.com/en-us/kusto/query/python-plugin

Example of using the python plugin to install a zipped package from OneLake and generate data using the Faker library.

```kusto
range ID from 1 to 3 step 1 
| extend Name=''
| evaluate python(typeof(*), ```if 1:
    from sandbox_utils import Zipackage
    Zipackage.install("Faker.zip")
    from faker import Faker
    fake = Faker()
    result = df
    for i in range(df.shape[0]):
        result.loc[i, "Name"] = fake.name()
    ```,
    external_artifacts=bag_pack('faker.zip', 'https://msit-onelake.dfs.fabric.microsoft.com/MSIT_DEMO_WS/MSIT_DEMO_LH.Lakehouse/Files/Faker.zip;impersonate'))
```

--------------------------------

### Start the development server

Source: https://learn.microsoft.com/en-us/kusto/api/monaco/monaco-kusto

Run this command to verify the project is working and launch the development server.

```bash
npm run watch
```

--------------------------------

### Get extended sign-in activities

Source: https://learn.microsoft.com/en-us/kusto/query/join-innerunique

Uses join to correlate start and stop events for activities, calculating the duration between them.

```Kusto
let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, StartTime=timestamp
| join (Events
    | where Name == "Stop"
        | project StopTime=timestamp, ActivityId)
    on ActivityId
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime
```

```Kusto
let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, ActivityIdLeft = ActivityId, StartTime=timestamp
| join (Events
        | where Name == "Stop"
        | project StopTime=timestamp, ActivityIdRight = ActivityId)
    on $left.ActivityIdLeft == $right.ActivityIdRight
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime
```

--------------------------------

### Get runtime types of expressions

Source: https://learn.microsoft.com/en-us/kusto/query/gettype-function

Examples demonstrating the runtime type returned for various scalar and dynamic expressions.

```KQL
gettype("a")
```

```KQL
gettype(111)
```

```KQL
gettype(1==1)
```

```KQL
gettype(now())
```

```KQL
gettype(1s)
```

```KQL
gettype(parse_json('1'))
```

```KQL
gettype(parse_json(' "abc" '))
```

```KQL
gettype(parse_json(' {"abc":1} '))
```

```KQL
gettype(parse_json(' [1, 2, 3] '))
```

```KQL
gettype(123.45)
```

```KQL
gettype(guid(12e8b78d-55b4-46ae-b068-26d7a0080254))
```

```KQL
gettype(parse_json(''))
```

--------------------------------

### Define main application structure

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-hello-kusto

Set up the entry point for your application.

```C#
namespace HelloKusto {
  class HelloKusto {
    static void Main(string[] args) {
    }
  }
}
```

```Python
def main():

if __name__ == "__main__":
  main()
```

```TypeScript
async function main()
{
}

main();
```

```Java
public class HelloKusto 
{
  public static void main(String[] args) throws Exception {
    try {
    }
  }
}
```

--------------------------------

### Specify a null guid literal

Source: https://learn.microsoft.com/en-us/kusto/query/scalar-data-types/guid

Use the guid() function with null to represent a null value for the guid type.

```KQL
guid(null)
```

--------------------------------

### Complete Hello Kusto Implementation

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-hello-kusto

Full example code for a complete application flow.

```C#
using Kusto.Data;
using Kusto.Data.Net.Client;

namespace HelloKusto {
  class HelloKusto {
    static void Main(string[] args) {
      string clusterUri = "https://help.kusto.windows.net/";
      var kcsb = new KustoConnectionStringBuilder(clusterUri).WithAadUserPromptAuthentication();
    
      using (var kustoClient = KustoClientFactory.CreateCslQueryProvider(kcsb)) {
        string database = "Samples";
        string query = "print Welcome='Hello Kusto!'";

        using (var response = kustoClient.ExecuteQuery(database, query, null)) {
          response.Read();
          int columnNo = response.GetOrdinal("Welcome");
          Console.WriteLine(response.GetString(columnNo));
        }
      }
    }
  }
}
```

```Python
from azure.kusto.data import KustoClient, KustoConnectionStringBuilder

def main():
  cluster_uri = "https://help.kusto.windows.net"
  kcsb = KustoConnectionStringBuilder.with_interactive_login(cluster_uri)

  with KustoClient(kcsb) as kusto_client:

    database = "Samples"
    query = "print Welcome='Hello Kusto!'"
    response = kusto_client.execute(database, query)

    print(response.primary_results[0][0]["Welcome"])

if __name__ == "__main__":
  main()
```

```TypeScript
import { Client as KustoClient, KustoConnectionStringBuilder } from "azure-kusto-data/";
import { InteractiveBrowserCredentialInBrowserOptions } from "@azure/identity";

async function main()
{
  const clusterUri = "https://help.kusto.windows.net";
  const authOptions = {
    clientId: "00001111-aaaa-2222-bbbb-3333cccc4444",
    redirectUri: "http://localhost:5173",
  } as InteractiveBrowserCredentialInBrowserOptions;
  const kcsb = KustoConnectionStringBuilder.withUserPrompt(clusterUri, authOptions);
  const kustoClient = new KustoClient(kcsb);

  const database = "Samples";
  const query = "print Welcome='Hello Kusto!'";
  const response = await kustoClient.execute(database, query);

  console.log(response.primaryResults[0][0]["Welcome"].toString());
}

main();
```

--------------------------------

### Generate fixed and dynamic GUIDs

Source: https://learn.microsoft.com/en-us/kusto/query/toscalar-function

Demonstrates the difference between a fixed GUID calculated once using toscalar() and dynamic GUIDs generated per row.

```Kusto
let g1 = toscalar(new_guid());
let g2 = new_guid();
range x from 1 to 2 step 1
| extend x=g1, y=g2
```

--------------------------------

### base64_encode_fromguid(guid)

Source: https://learn.microsoft.com/en-us/kusto/query/base64-encode-fromguid-function

Encodes a GUID to a base64 string.

```APIDOC
## base64_encode_fromguid(guid)

### Description
Encodes a GUID to a base64 string.

### Syntax
`base64_encode_fromguid(guid)`

### Parameters
- **guid** (guid) - Required - The value to encode to a base64 string.

### Returns
Returns a base64 string encoded from a GUID.

### Example
```kusto
print Quine = base64_encode_fromguid(toguid("ae3133f2-6e22-49ae-b06a-16e6a9b212eb"))
```
```

--------------------------------

### Create initial table

Source: https://learn.microsoft.com/en-us/kusto/management/update-table-command

Sets up the Employees table with 100 records for use in subsequent examples.

```Kusto
.set-or-replace Employees <|
  range i from 1 to 100 step 1
  | project Id=i
  | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
  | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])
```

--------------------------------

### Summarize with Shuffle and Partition Hints

Source: https://learn.microsoft.com/en-us/kusto/query/shuffle-query

Shows how to control the number of partitions using hint.num_partitions to further optimize shuffle performance.

```Kusto
lineitem 
| summarize hint.strategy = shuffle dcount(l_comment), dcount(l_shipdate) by l_partkey 
| consume
```

```Kusto
lineitem 
| summarize hint.strategy = shuffle hint.num_partitions = 10 dcount(l_comment), dcount(l_shipdate) by l_partkey 
| consume
```

--------------------------------

### Expression example

Source: https://learn.microsoft.com/en-us/kusto/query/pattern-statement

Example of a tabular expression used within a pattern definition.

```kusto
Logs | where Timestamp > ago(1h)
```

--------------------------------

### Generate Java Maven Project

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-set-up

Use the Maven archetype quickstart template to initialize a new Java application.

```bash
 mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId==my-app -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.4 -DinteractiveMode=false
```

--------------------------------

### Single-step purge example

Source: https://learn.microsoft.com/en-us/kusto/concepts/data-purge

Example of triggering a purge in a single-step activation scenario.

```Kusto
// Connect to the Data Management service
#connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"

.purge table MyTable in database MyDatabase allrecords with (noregrets='true')
```

--------------------------------

### Batch ingestion and query setup in Java

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-managed-streaming-ingest

Initializes Kusto clients and demonstrates a basic row count query using a helper method for result printing.

```Java

package com.example;

import java.io.FileWriter;

import com.azure.identity.DefaultAzureCredential;
import com.azure.identity.DefaultAzureCredentialBuilder;
import com.microsoft.azure.kusto.data.Client;
import com.microsoft.azure.kusto.data.ClientFactory;
import com.microsoft.azure.kusto.data.KustoOperationResult;
import com.microsoft.azure.kusto.data.KustoResultSetTable;
import com.microsoft.azure.kusto.data.KustoResultColumn;
import com.microsoft.azure.kusto.data.auth.ConnectionStringBuilder;
import com.microsoft.azure.kusto.ingest.IngestClientFactory;
import com.microsoft.azure.kusto.ingest.IngestionProperties;
import com.microsoft.azure.kusto.ingest.ManagedStreamingIngestClient;
import com.microsoft.azure.kusto.ingest.IngestionProperties.DataFormat;
import com.microsoft.azure.kusto.ingest.source.FileSourceInfo;

public class BatchIngestion {
    public static void main(String[] args) throws Exception {

        String clusterUri = "<KustoClusterQueryURI>";
        String ingestionUri = "<KustoClusterQueryIngestURI>";
        String databaseName = "<databaseName>";
        String table = "MyStormEvents";

        ConnectionStringBuilder clusterKcsb = ConnectionStringBuilder.createWithUserPrompt(clusterUri);
        ConnectionStringBuilder ingestionKcsb = ConnectionStringBuilder.createWithUserPrompt(ingestionUri);

        try (
                Client kustoClient = ClientFactory.createClient(clusterKcsb)) {

            String query = table + " | count";
            KustoOperationResult results = kustoClient.execute(databaseName, query);
            KustoResultSetTable primaryResults = results.getPrimaryResults();
            System.out.println("\nNumber of rows in " + table + " BEFORE ingestion:");
            printResultsAsValueList(primaryResults);
        }
    }

    public static void printResultsAsValueList(KustoResultSetTable results) {
        while (results.next()) {
            KustoResultColumn[] columns = results.getColumns();
            for (int i = 0; i < columns.length; i++) {
                System.out.println("\t" + columns[i].getColumnName() + " - "
                        + (results.getObject(i) == null ? "None" : results.getString(i)));
            }
        }
    }
}
```

--------------------------------

### XML Input Example

Source: https://learn.microsoft.com/en-us/kusto/query/parse-xml-function

An example of an XML-formatted string used as input for the parse_xml function.

```xml
<?xml version="1.0" encoding="UTF-8"?>
<duration>
    <value>118.0</value>
    <count>5.0</count>
    <min>100.0</min>
    <max>150.0</max>
    <stdDev>0.0</stdDev>
    <sampledValue>118.0</sampledValue>
    <sum>118.0</sum>
</duration>
```

--------------------------------

### Alter-merge table examples

Source: https://learn.microsoft.com/en-us/kusto/management/alter-merge-table-command

Demonstrates how to add columns to a table and how to include optional properties like docstring and folder.

```Kusto
.alter-merge table MyTable (ColumnX:string, ColumnY:int) 
.alter-merge table MyTable (ColumnX:string, ColumnY:int) with (docstring = "Some documentation", folder = "Folder1")
```

--------------------------------

### Set up sample dataset and model

Source: https://learn.microsoft.com/en-us/kusto/functions-library/predict-fl

Use these commands to ingest the sample occupancy detection data and the serialized model into your Kusto database.

```Kusto
//dataset
.set OccupancyDetection <| cluster('help').database('Samples').OccupancyDetection

//model
.set ML_Models <| datatable(name:string, timestamp:datetime, model:string) [
'Occupancy', datetime(now), '800363736b6c6561726e2e6c696e6561725f6d6f64656c2e6c6f6769737469630a4c6f67697374696352656772657373696f6e0a7100298171017d710228580700000070656e616c7479710358020000006c32710458040000006475616c7105895803000000746f6c7106473f1a36e2eb1c432d5801000000437107473ff0000000000000580d0000006669745f696e746572636570747108885811000000696e746572636570745f7363616c696e6771094b01580c000000636c6173735f776569676874710a4e580c00000072616e646f6d5f7374617465710b4e5806000000736f6c766572710c58090000006c69626c696e656172710d58080000006d61785f69746572710e4b64580b0000006d756c74695f636c617373710f58030000006f767271105807000000766572626f736571114b00580a0000007761726d5f737461727471128958060000006e5f6a6f627371134b015808000000636c61737365735f7114636e756d70792e636f72652e6d756c746961727261790a5f7265636f6e7374727563740a7115636e756d70790a6e6461727261790a71164b00857117430162711887711952711a284b014b0285711b636e756d70790a64747970650a711c58020000006231711d4b004b0187711e52711f284b0358010000007c71204e4e4e4affffffff4affffffff4b007471216289430200017122747123625805000000636f65665f7124681568164b008571256818877126527127284b014b014b05867128681c5802000000663871294b004b0187712a52712b284b0358010000003c712c4e4e4e4affffffff4affffffff4b0074712d628943286a02e0d50687e0bfc6d7c974fa93a63fb3d3b8080e6e943ffceb15defdad713f14c3a76bd73202bf712e74712f62580a000000696e746572636570745f7130681568164b008571316818877132527133284b014b01857134682b894308f1e89f57711290bf71357471366258070000006e5f697465725f7137681568164b00857138681887713952713a284b014b0185713b681c58020000006934713c4b004b0187713d52713e284b03682c4e4e4e4affffffff4affffffff4b0074713f628943040c00000071407471416258100000005f736b6c6561726e5f76657273696f6e71425806000000302e31392e32714375622e'
]
```

--------------------------------

### Join with Shuffle Strategy and Key Hint

Source: https://learn.microsoft.com/en-us/kusto/query/shuffle-query

Demonstrates how to use hint.shufflekey when hint.strategy=shuffle is ignored due to preceding summarize operations.

```Kusto
StormEvents
| where StartTime > datetime(2007-01-01 00:00:00.0000000)
| summarize count() by EpisodeId, EventId
| join kind = inner hint.strategy=shuffle (StormEvents | where DamageCrops > 62000000) on EpisodeId, EventId
```

```Kusto
StormEvents
| where StartTime > datetime(2007-01-01 00:00:00.0000000)
| summarize count() by EpisodeId, EventId
| join kind = inner hint.shufflekey=EpisodeId (StormEvents | where DamageCrops > 62000000) on EpisodeId, EventId
```

--------------------------------

### Install R package

Source: https://learn.microsoft.com/en-us/kusto/query/r-plugin

Installs a specified R package into the current library path.

```R
> install.packages("brglm2")
```

--------------------------------

### Initialize Kusto client in C#

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-managed-streaming-ingest

Sets up the Kusto connection and provides a helper method to print query results.

```C#
using System.Data;

using Kusto.Data;
using Kusto.Data.Common;
using Kusto.Data.Net.Client;
using Kusto.Ingest.V2;
using Kusto.Ingest.Common;

using Azure.Identity;

namespace BatchIngest;

class BatchIngest
{
    static async Task Main()
    {
       var tokenCredential = new InteractiveBrowserCredential();
       var clusterUri = "<your cluster>"; // e.g., "https://<your_cluster_name>.<region>.kusto.windows.net"
       var clusterKcsb = new KustoConnectionStringBuilder(clusterUri).WithAadAzureTokenCredentialsAuthentication(tokenCredential);
        var database = "<your database>";
        var table = "MyStormEvents";
        var query = table + " | count";
        using var kustoClient = KustoClientFactory.CreateCslQueryProvider(clusterKcsb);
        using (var response = await kustoClient.ExecuteQueryAsync(database, query, null))
        {
            Console.WriteLine("\nNumber of rows in " + table + " BEFORE ingestion:");
            PrintResultsAsValueList(response);
        }
    }

    static void PrintResultsAsValueList(IDataReader response)
    {
        while (response.Read())
        {
            for (var i = 0; i < response.FieldCount; i++)
            {
                object val = response.GetValue(i);
                string value = val.ToString() ?? "None";
                Console.WriteLine("\t{0} - {1}", response.GetName(i), value);
            }
        }
    }
}
```

--------------------------------

### Specify a guid literal

Source: https://learn.microsoft.com/en-us/kusto/query/scalar-data-types/guid

Use the guid() function to define a specific 128-bit unique identifier.

```KQL
guid(74be27de-1e4e-49d9-b579-fe0b331d3642)
```

--------------------------------

### Compare hint.strategy=shuffle and hint.shufflekey=key

Source: https://learn.microsoft.com/en-us/kusto/query/shuffle-query

Demonstrates the equivalence between using a general shuffle strategy and explicitly defining shuffle keys.

```Kusto
StormEvents
| where StartTime > datetime(2007-01-01 00:00:00.0000000)
| join kind = inner hint.strategy=shuffle (StormEvents | where DamageCrops > 62000000) on EpisodeId, EventId
| count
```

```Kusto
StormEvents
| where StartTime > datetime(2007-01-01 00:00:00.0000000)
| join kind = inner hint.shufflekey = EpisodeId hint.shufflekey = EventId (StormEvents | where DamageCrops > 62000000) on EpisodeId, EventId
```

--------------------------------

### Calculate week of year examples

Source: https://learn.microsoft.com/en-us/kusto/query/week-of-year-function

Examples demonstrating the week_of_year() function with various datetime inputs.

```Kusto
week_of_year(datetime(2020-12-31))
```

```Kusto
week_of_year(datetime(2020-06-15))
```

```Kusto
week_of_year(datetime(1970-01-01))
```

```Kusto
week_of_year(datetime(2000-01-01))
```

--------------------------------

### Request with Query Parameters

Source: https://learn.microsoft.com/en-us/kusto/api/rest/request

Example body demonstrating how to pass query parameters and client request properties.

```json
{
    "db": "Samples",
    "csl": "declare query_parameters (n:long, d:dynamic); StormEvents | where State in (d) | top n by StartTime asc",
    "properties": {
        "Options": {
            "maxmemoryconsumptionperiterator": 68719476736,
            "max_memory_consumption_per_query_per_node": 68719476736,
            "servertimeout": "50m"
        },
        "Parameters": {
            "n": 10, "d": "dynamic([\"ATLANTIC SOUTH\"])"
        }
    }
}
```

--------------------------------

### Convert strings to guid

Source: https://learn.microsoft.com/en-us/kusto/query/toguid-function

Demonstrates converting various string formats into guid scalars using a datatable.

```Kusto
datatable(str: string)
[
    "0123456789abcdef0123456789abcdef",
    "0123456789ab-cdef-0123-456789abcdef",
    "a string that is not a guid"
]
| extend guid = toguid(str)
```

--------------------------------

### Create a staging table

Source: https://learn.microsoft.com/en-us/kusto/management/update-table-command

Initializes a staging table with sample data for subsequent update operations.

```Kusto
.set-or-replace MyStagingTable <|
    range i from 70 to 130 step 5
    | project Id=i
    | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
    | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])
```

--------------------------------

### Decode base64 to GUID

Source: https://learn.microsoft.com/en-us/kusto/query/base64-decode-toguid-function

Demonstrates the basic usage of base64_decode_toguid() to convert a valid base64 string into a GUID.

```Kusto
print Quine = base64_decode_toguid("JpbpECu8dUy7Pv5gbeJXAA==")
```

--------------------------------

### BytesAsBase64 transformation AVRO example

Source: https://learn.microsoft.com/en-us/kusto/management/mappings

Example AVRO input and mapping configuration demonstrating the BytesAsBase64 transformation.

```JSON
{
    "Time": "2012-01-15T10:45",
    "Props": {
        "id": [227,131,34,92,28,91,65,72,134,138,9,133,51,45,104,52]
    }
}
```

```JSON
[
    { "Column": "ID", "Properties": { "Path": "$.props.id" } },
    { "Column": "Base64EncodedId", "Properties": { "Path": "$.props.id", "Transform":"BytesAsBase64" } },
]
```

--------------------------------

### KQL pipeline query example

Source: https://learn.microsoft.com/en-us/kusto/query/syntax-conventions

Demonstrates a typical KQL query structure using data retrieval, filtering, summarization, sorting, and selection steps.

```Kusto
SigninLogs                              // Get data
| evaluate bag_unpack(LocationDetails)  // Ignore this line for now; we'll come back to it at the end.
| where RiskLevelDuringSignIn == 'none' // Filter
   and TimeGenerated >= ago(7d)         // Filter
| summarize Count = count() by city     // Summarize
| sort by Count desc                    // Sort
| take 5                                // Select
```

--------------------------------

### Create an external table partitioned by name and date

Source: https://learn.microsoft.com/en-us/kusto/management/external-tables-azure-storage

Demonstrates multi-level partitioning using customer name and date.

```Kusto
.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)
```

--------------------------------

### DropMappedFields transformation JSON example

Source: https://learn.microsoft.com/en-us/kusto/management/mappings

Example JSON input and mapping configuration demonstrating the DropMappedFields transformation.

```JSON
{
    "Time": "2012-01-15T10:45",
    "Props": {
        "EventName": "CustomEvent",
        "Revenue": 0.456
    }
}
```

```JSON
[
    { "Column": "Time", "Properties": { "Path": "$.Time" } },
    { "Column": "EventName", "Properties": { "Path": "$.Props.EventName" } },
    { "Column": "Props", "Properties": { "Path": "$.Props", "Transform":"DropMappedFields" } },
]
```

--------------------------------

### Example command output

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-management-commands

Displays the expected output format when running the retention policy management command.

```Bash
--------------------

Command: .show database YourDatabase policy retention | project-away ChildEntities, EntityType
Result:
   PolicyName - RetentionPolicy
   EntityName - [YourDatabase]
   Policy - {
  "SoftDeletePeriod": "365.00:00:00",
  "Recoverability": "Enabled"
}
```

--------------------------------

### Execute Parameterized Queries

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-basic-query

Examples demonstrating how to declare and set query parameters across different programming languages.

```C#
string query = @"declare query_parameters(event_type:string, daily_damage:int);
                  StormEvents
                  | where EventType == event_type
                  | extend TotalDamage = DamageProperty + DamageCrops
                  | summarize DailyDamage=sum(TotalDamage) by State, bin(StartTime, 1d)
                  | where DailyDamage > daily_damage
                  | order by DailyDamage desc";

var crp = new ClientRequestProperties();
crp.SetParameter("event_type", "Flash Flood");
crp.SetParameter("daily_damage", 200000000.ToString());

using (var response = kustoClient.ExecuteQuery(database, query, crp)) {
  int columnNoStartTime = response.GetOrdinal("StartTime");
  int columnNoState = response.GetOrdinal("State");
  int columnNoDailyDamage = response.GetOrdinal("DailyDamage");
  Console.WriteLine("Daily flash flood damages over 200,000,000$:");

  while (response.Read()) {
    Console.WriteLine("{0} - {1}, {2}",
      response.GetDateTime(columnNoStartTime),
      response.GetString(columnNoState),
      response.GetInt64(columnNoDailyDamage));
  }
}
```

```Python
query = "declare query_parameters(event_type:string, daily_damage:int);"\
        "StormEvents" \
        "| where EventType == event_type" \
        "| extend TotalDamages = DamageProperty + DamageCrops" \
        "| summarize DailyDamage=sum(TotalDamages) by State, bin(StartTime, 1d)" \
        "| where DailyDamage > daily_damage" \
        "| order by DailyDamage desc"

crp = ClientRequestProperties()
crp.set_parameter("event_type", "Flash Flood")
crp.set_parameter("daily_damage", str(200000000))

response = kusto_client.execute_query(=database, query, crp)

print("Daily flash flood damages over 200,000,000$:")
for row in response.primary_results[0]:
  print(row["StartTime"], "-", row["State"], ",", row["DailyDamage"], "$")
```

```TypeScript
const query = `declare query_parameters(event_type:string, daily_damage:int);
               StormEvents
               | where EventType == event_type
               | extend TotalDamage = DamageProperty + DamageCrops
               | summarize DailyDamage=sum(TotalDamage) by State, bin(StartTime, 1d)
               | where DailyDamage > daily_damage
               | order by DailyDamage desc`;

const crp = new ClientRequestProperties();
crp.setParameter("event_type", "Flash Flood");
crp.setParameter("daily_damage", 200000000);

const response = await kustoClient.execute(database, query, crp);

console.log("Daily flash flood damages over 200,000,000$:");
for (row of response.primaryResults[0].rows()) {
  console.log(row.getValueAt(columnNoStartTime).toString(), "-", row.getValueAt(columnNoState).toString(), ",", row.getValueAt(columnNoDailyDamage).toString(), "$");
}
```

```Java
String query = "declare query_parameters(event_type:string, daily_damage:int);\n" +
               "StormEvents\n" +
               "| where EventType == event_type\n" +
               "| extend TotalDamage = DamageProperty + DamageCrops\n" +
               "| summarize DailyDamage=sum(TotalDamage) by State, bin(StartTime, 1d)\n" +
               "| where DailyDamage > daily_damage\n" +
               "| order by DailyDamage desc";

ClientRequestProperties crp = new ClientRequestProperties();
crp.setParameter("event_type", "Flash Flood");
crp.setParameter("daily_damage", 200000000);

KustoOperationResult response = kusto_client.execute(database, query, crp);
KustoResultSetTable primary_results = response.getPrimaryResults();

System.out.println("Daily flash flood damages over 200,000,000$:");
while (primary_results.next()) {
  System.out.println("DEBUG: " + primary_results.getString(columnNoStartTime) + " - " + primary_results.getString(columnNoState) + " , " + primary_results.getString(columnNoDailyDamage));
}
```

--------------------------------

### Install Python Kusto Libraries

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-set-up

Use pip to install the Kusto Data and Ingest packages for Python.

```bash
python -m pip install azure-kusto-data
python -m pip install azure-kusto-ingest
```

--------------------------------

### toguid(value)

Source: https://learn.microsoft.com/en-us/kusto/query/toguid-function

Converts a string to a guid scalar. If the conversion is successful, it returns a guid; otherwise, it returns null.

```APIDOC
## toguid(value)

### Description
Converts a string to a `guid` scalar. The function takes the first 32 characters of the input, ignoring hyphens, and validates that characters are between 0-9 or a-f.

### Syntax
`toguid(value)`

### Parameters
- **value** (scalar) - Required - The string value to convert to a guid.

### Returns
- **guid** (scalar) - The converted guid value or `null` if the conversion fails.
```

--------------------------------

### Configure Ingestion Properties in V2

Source: https://learn.microsoft.com/en-us/kusto/api/netfx/kusto-ingest-migrate

Demonstrates setting up ingestion properties and performing an asynchronous ingestion using FileSource.

```C#
var source = new FileSource(filePath, DataSourceFormat.csv);
var properties = new IngestProperties
{
    EnableTracking = true,
    MappingReference = "MyMapping"
};
await client.IngestAsync(source, database, table, properties);
```

--------------------------------

### Encode a GUID to base64

Source: https://learn.microsoft.com/en-us/kusto/query/base64-encode-fromguid-function

Demonstrates the standard usage of the function by converting a GUID string into a base64 encoded value.

```Kusto
print Quine = base64_encode_fromguid(toguid("ae3133f2-6e22-49ae-b06a-16e6a9b212eb"))
```

--------------------------------

### Graph snapshot configuration example

Source: https://learn.microsoft.com/en-us/kusto/management/graph/graph-snapshot-overview

Example JSON structure representing the metadata and model information of a graph snapshot.

```JSON
{
  "Metadata": {
    "Name": "UserInteractionsSnapshot",
    "SnapshotTime": "2025-04-28T10:15:30Z"
  },
  "ModelInformation": {
    "ModelName": "SocialNetworkGraph",
    "ModelVersion": "v1.2",
    "ModelCreationTime": "2025-04-15T08:20:10Z"
  }
}
```

--------------------------------

### Execute factorial_fl() examples

Source: https://learn.microsoft.com/en-us/kusto/functions-library/factorial-fl

Demonstrates usage of the function via a query-defined approach and a stored function approach.

```Kusto
let factorial_fl=(n:int)
{
    gamma(n+1)
};
range x from 1 to 10 step 3
| extend fx = factorial_fl(x)
```

```Kusto
range x from 1 to 10 step 3
| extend fx = factorial_fl(x)
```

--------------------------------

### Install TypeScript Kusto Libraries

Source: https://learn.microsoft.com/en-us/kusto/api/get-started/app-set-up

Use npm to install the Kusto Data and Ingest packages for Node.js projects.

```bash
npm install azure-kusto-data
npm install azure-kusto-ingest
```

--------------------------------

### Calculate start of week from Monday

Source: https://learn.microsoft.com/en-us/kusto/query/startofweek-function

Uses a custom function to shift the default Sunday-based start of week to Monday.

```Kusto
let startofweekFromMonday = (dateArg: datetime) {
    datetime_add('day', 1, startofweek(datetime_add('day', -1, dateArg)))
};
let data=datatable(Date: datetime, day: string)
[
datetime(2025, 6, 14), "Saturday",
datetime(2025, 6, 15), "Sunday",
datetime(2025, 6, 16), "Monday",
datetime(2025, 6, 17), "Tuesday"
];
data 
| extend MondayWeek=startofweekFromMonday(Date)
```

--------------------------------

### Translate SQL to KQL using explain

Source: https://learn.microsoft.com/en-us/kusto/query/sql-cheat-sheet

Use the explain keyword preceded by a comment to generate the KQL equivalent of a SQL query.

```Kusto
--
explain
SELECT COUNT_BIG(*) as C FROM StormEvents 
```

--------------------------------

### array_slice(array, start, end)

Source: https://learn.microsoft.com/en-us/kusto/query/array-slice-function

Extracts a slice of a dynamic array from the start index to the end index (inclusive).

```APIDOC
## array_slice(array, start, end)

### Description
Extracts a slice of a dynamic array. Out of bounds indices are ignored.

### Parameters
- **array** (dynamic) - Required - The array from which to extract the slice.
- **start** (int) - Required - The start index of the slice (inclusive). Negative values are converted to array_length + start.
- **end** (int) - Required - The last index of the slice (inclusive). Negative values are converted to array_length + end.

### Returns
Returns a dynamic array of the values in the range [start..end] from the input array.

### Example
```kusto
print arr=dynamic([1,2,3]) 
| extend sliced=array_slice(arr, 1, 2)
```
```
Tokens:
Raw