Reference

• 1: T-SQL
• 2: JSONPath syntax
• 3: KQL docs navigation guide
• 4: Regex syntax
• 5: Splunk to Kusto map
• 6: SQL to Kusto query translation
• 7: Timezone

1 - T-SQL

The query editor supports the use of T-SQL in addition to its primary query language, Kusto query language (KQL). While KQL is the recommended query language, T-SQL can be useful for tools that are unable to use KQL.

Query with T-SQL

To run a T-SQL query, begin the query with an empty T-SQL comment line: --. The -- syntax tells the query editor to interpret the following query as T-SQL and not KQL.

Example

--
SELECT * FROM StormEvents

T-SQL to Kusto Query Language

The query editor supports the ability to translate T-SQL queries into KQL. This translation feature can be helpful for users who are familiar with SQL and want to learn more about KQL.

To get the equivalent KQL for a T-SQL SELECT statement, add the keyword explain before the query. The output will be the KQL version of the query, which can be useful for understanding the corresponding KQL syntax and concepts.

Remember to preface T-SQL queries with a T-SQL comment line, --, to tell the query editor to interpret the following query as T-SQL and not KQL.

Example

--
explain
SELECT top(10) *
FROM StormEvents
ORDER BY DamageProperty DESC

Output

StormEvents
| project
    StartTime,
    EndTime,
    EpisodeId,
    EventId,
    State,
    EventType,
    InjuriesDirect,
    InjuriesIndirect,
    DeathsDirect,
    DeathsIndirect,
    DamageProperty,
    DamageCrops,
    Source,
    BeginLocation,
    EndLocation,
    BeginLat,
    BeginLon,
    EndLat,
    EndLon,
    EpisodeNarrative,
    EventNarrative,
    StormSummary
| sort by DamageProperty desc nulls first
| take int(10)

Run stored functions

When using T-SQL, we recommend that you create optimized KQL queries and encapsulate them in stored functions, as doing so minimizes T-SQL code and may increase performance. For example, if you have a stored function as described in the following table, you can execute it as shown in the code example.

SELECT * FROM kusto.MyFunction(10)

Set request properties

Request properties control how a query executes and returns results. To set request properties with T-SQL, preface your query with one or more statements with the following syntax:

Syntax

DECLARE @__kql_set_requestPropertyName type = value;

Parameters

Examples

The following table shows examples for how to set request properties with T-SQL.

To set request properties with KQL, see set statement.

Coverage

The query environment offers limited support for T-SQL. The following table outlines the T-SQL statements and features that aren’t supported or are partially supported.

Related content

• Learn about SQL Server emulation in Azure Data Explorer
• Use the SQL to Kusto Query Language cheat sheet

2 - JSONPath syntax

JSONPath notation describes the path to one or more elements in a JSON document.

The JSONPath notation is used in the following scenarios:

• To specify data mappings for ingestion
• To specify data mappings for external tables
• In Kusto Query Language (KQL) functions that process dynamic objects, like bag_remove_keys() and extract_json()

The following subset of the JSONPath notation is supported:

Example

Given the following JSON document:

{
  "Source": "Server-01",
  "Timestamp": "2023-07-25T09:15:32.123Z",
  "Log Level": "INFO",
  "Message": "Application started successfully.",
  "Details": {
    "Service": "AuthService",
    "Endpoint": "/api/login",
    "Response Code": 200,
    "Response Time": 54.21,
    "User": {
      "User ID": "user123",
      "Username": "kiana_anderson",
      "IP Address": "192.168.1.100"
    },
    "Tags": [
      "startup",
      "auth",
      "performance"
    ]
  }
}

You can represent each of the fields with JSONPath notation as follows:

"$.Source"                     // Source field
"$.Timestamp"                  // Timestamp field
"$['Log Level']"               // Log Level field
"$.Message"                    // Message field
"$.Details.Service"            // Service field
"$.Details.Endpoint"           // Endpoint field
"$.Details['Response Code']"   // Response Code field
"$.Details['Response Time']"   // Response Time field
"$.Details.User['User ID']"    // User ID field
"$.Details.User.Username"      // Username field
"$.Details.User['IP Address']" // IP Address field
"$.Tags[0]"                    // First value in the array of the Tags

Related content

• Get the path to a dynamic field
• Add filter from dynamic field

3 - KQL docs navigation guide

KQL behavior can vary across services. On Microsoft Learn, the selected service name appears above the table of contents (TOC) under the Version dropdown. To view behavior for another service, use the Version dropdown to switch services.

Change service selection

HTTPS view= parameter

Applies to services

Most KQL articles include Applies to under the title. The line lists services and shows which ones the article applies to. For example, a function might apply to Microsoft Fabric and Azure Data Explorer, but not to Azure Monitor. If you don’t see your service, the article likely doesn’t apply.

Versions

This table describes KQL versions and their associated services.

4 - Regex syntax

This article provides an overview of regular expression syntax supported by Kusto Query Language (KQL).

There are a number of KQL operators and functions that perform string matching, selection, and extraction with regular expressions, such as matches regex, parse, and replace_regex().

In KQL, regular expressions must be encoded as string literals and follow the string quoting rules. For example, the regular expression \A is represented in KQL as "\\A". The extra backslash indicates that the other backslash is part of the regular expression \A.

Syntax

The following sections document the regular expression syntax supported by Kusto.

Match one character

Character classes

Precedence in character classes is from most binding to least binding:

1. Ranges: [a-cd] == [[a-c]d]
2. Union: [ab&&bc] == [[ab]&&[bc]]
3. Intersection, difference, symmetric difference: All have equivalent precedence, and are evaluated from left-to-right. For example, [\pL--\p{Greek}&&\p{Uppercase}] == [[\pL--\p{Greek}]&&\p{Uppercase}].
4. Negation: [^a-z&&b] == [^[a-z&&b]].

Composites

Repetitions

Empty matches

Grouping and flags

Capture group names can contain only alpha-numeric Unicode codepoints, dots ., underscores _, and square brackets[ and ]. Names must start with either an _ or an alphabetic codepoint. Alphabetic codepoints correspond to the Alphabetic Unicode property, while numeric codepoints correspond to the union of the Decimal_Number, Letter_Number and Other_Number general categories.

Flags are single characters. For example, (?x) sets the flag x and (?-x) clears the flag x. Multiple flags can be set or cleared at the same time: (?xy) sets both the x and y flags and (?x-y) sets the x flag and clears the y flag. By default all flags are disabled unless stated otherwise. They are:

In verbose mode, whitespace is ignored everywhere, including within character classes. To insert whitespace, use its escaped form or a hex literal. For example, \ or \x20 for an ASCII space.

Escape sequences

Perl character classes (Unicode friendly)

These classes are based on the definitions provided in UTS#18:

ASCII character classes

These classes are based on the definitions provided in UTS#18:

Performance

This section provides some guidance on speed and resource usage of regex expressions.

Unicode can affect memory usage and search speed

KQL regex provides first class support for Unicode. In many cases, the extra memory required to support Unicode is negligible and doesn’t typically affect search speed.

The following are some examples of Unicode character classes that can affect memory usage and search speed:

• Memory usage: The effect of Unicode primarily arises from the use of Unicode character classes. Unicode character classes tend to be larger in size. For example, the \w character class matches around 140,000 distinct codepoints by default. This requires more memory and can slow down regex compilation. If ASCII satisfies your requirements, use ASCII classes instead of Unicode classes. The ASCII-only version of \w can be expressed in multiple ways, all of which are equivalent.

  [0-9A-Za-z_]
  (?-u:\w)
  [[:word:]]
  [\w&&\p{ascii}]
  

• Search speed: Unicode tends to be handled well, even when using large Unicode character classes. However, some of the faster internal regex engines can’t handle a Unicode aware word boundary assertion. So if you don’t need Unicode-aware word boundary assertions, you might consider using (?-u:\b) instead of \b. The (?-u:\b) uses an ASCII-only definition of a word character, which can improve search speed.

Literals can accelerate searches

KQL regex has a strong ability to recognize literals within a regex pattern, which can significantly speed up searches. If possible, including literals in your pattern can greatly improve search performance. For example, in the regex \w+@\w+, first occurrences of @ are matched and then a reverse match is performed for \w+ to find the starting position.

5 - Splunk to Kusto map

This article is intended to assist users who are familiar with Splunk learn the Kusto Query Language to write log queries with Kusto. Direct comparisons are made between the two to highlight key differences and similarities, so you can build on your existing knowledge.

Structure and concepts

The following table compares concepts and data structures between Splunk and Kusto logs:

Functions

The following table specifies functions in Kusto that are equivalent to Splunk functions.

(1) In Splunk, the function is invoked by using the eval operator. In Kusto, it’s used as part of extend or project.
(2) In Splunk, the function is invoked by using the eval operator. In Kusto, it can be used with the where operator.

Operators

The following sections give examples of how to use different operators in Splunk and Kusto.

Search

In Splunk, you can omit the search keyword and specify an unquoted string. In Kusto, you must start each query with find, an unquoted string is a column name, and the lookup value must be a quoted string.

Filter

Kusto log queries start from a tabular result set in which filter is applied. In Splunk, filtering is the default operation on the current index. You also can use the where operator in Splunk, but we don’t recommend it.

Get n events or rows for inspection

Kusto log queries also support take as an alias to limit. In Splunk, if the results are ordered, head returns the first n results. In Kusto, limit isn’t ordered, but it returns the first n rows that are found.

Get the first n events or rows ordered by a field or column

For the bottom results, in Splunk, you use tail. In Kusto, you can specify ordering direction by using asc.

Extend the result set with new fields or columns

Splunk has an eval function, but it’s not comparable to the eval operator in Kusto. Both the eval operator in Splunk and the extend operator in Kusto support only scalar functions and arithmetic operators.

Rename

Kusto uses the project-rename operator to rename a field. In the project-rename operator, a query can take advantage of any indexes that are prebuilt for a field. Splunk has a rename operator that does the same.

Format results and projection

Splunk uses the table command to select which columns to include in the results. Kusto has a project operator that does the same and more.

Splunk uses the fields - command to select which columns to exclude from the results. Kusto has a project-away operator that does the same.

Aggregation

See the list of summarize aggregations functions that are available.

Join

join in Splunk has substantial limitations. The subquery has a limit of 10,000 results (set in the deployment configuration file), and a limited number of join flavors are available.

Sort

The default sort order is ascending. To specify descending order, add a minus sign (-) before the field name. Kusto also supports defining where to put nulls, either at the beginning or at the end.

Multivalue expand

The multivalue expand operator is similar in both Splunk and Kusto.

Result facets, interesting fields

In Log Analytics in the Azure portal, only the first column is exposed. All columns are available through the API.

Deduplicate

In Kusto, you can use summarize arg_min() to reverse the order of which record is chosen.

Timechart

Kusto and Splunk both use the timechart operator to visualize data over time. In Splunk, it aggregates data over specified time intervals and can be used with various statistical functions. In Kusto, the equivalent is achieved using the summarize and bin functions, followed by the render timechart operator.

Related content

• Walk through a tutorial on the Kusto Query Language.

6 - SQL to Kusto query translation

If you’re familiar with SQL and want to learn KQL, translate SQL queries into KQL by prefacing the SQL query with a comment line, --, and the keyword explain. The output shows the KQL version of the query, which can help you understand the KQL syntax and concepts.

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

Output

SQL to Kusto cheat sheet

The following table shows sample queries in SQL and their KQL equivalents.

| Category | SQL Query | Kusto Query | Learn more | |–|–|–| | Select data from table | SELECT * FROM dependencies | dependencies | Tabular expression statements | |–| SELECT name, resultCode FROM dependencies | dependencies | project name, resultCode | project | |–| SELECT TOP 100 * FROM dependencies | dependencies | take 100 | take | | Null evaluation | SELECT * FROM dependencies
WHERE resultCode IS NOT NULL | dependencies
| where isnotnull(resultCode) | isnotnull() | | Comparison operators (date) | SELECT * FROM dependencies
WHERE timestamp > getdate()-1 | dependencies
| where timestamp > ago(1d) | ago() | |–| SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ... | dependencies
| where timestamp between (datetime(2016-10-01) .. datetime(2016-11-01)) | between | | Comparison operators (string) | SELECT * FROM dependencies
WHERE type = "Azure blob" | dependencies
| where type == "Azure blob" | Logical operators | |–| -- substring
SELECT * FROM dependencies
WHERE type like "%blob%" | // substring
dependencies
| where type has "blob" | has | |–| -- wildcard
SELECT * FROM dependencies
WHERE type like "Azure%" | // wildcard
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*" | startswith
matches regex | | Comparison (boolean) | SELECT * FROM dependencies
WHERE !(success) | dependencies
| where success == False | Logical operators | | Grouping, Aggregation | SELECT name, AVG(duration) FROM dependencies
GROUP BY name | dependencies
| summarize avg(duration) by name | summarize
avg() | | Distinct | SELECT DISTINCT name, type FROM dependencies | dependencies
| distinct name, type | summarize
distinct | |–| SELECT name, COUNT(DISTINCT type)
FROM dependencies
GROUP BY name | dependencies
| summarize by name, type | summarize count() by name
// or approximate for large sets
dependencies
| summarize dcount(type) by name | count()
dcount() | | Column aliases, Extending | SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name | dependencies
| summarize AvgD = avg(duration) by Name=operationName | Alias statement | |–| SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions | ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session | strcat()
project | | Ordering | SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC | dependencies
| project name, timestamp
| sort by timestamp asc nulls last | sort | | Top n by measure | SELECT TOP 100 name, COUNT(*) as Count FROM dependencies
GROUP BY name
ORDER BY Count DESC | dependencies
| summarize Count = count() by name
| top 100 by Count desc | top | | Union | SELECT * FROM dependencies
UNION
SELECT * FROM exceptions | union dependencies, exceptions | union | |–| SELECT * FROM dependencies
WHERE timestamp > ...
UNION
SELECT * FROM exceptions
WHERE timestamp > ... | dependencies
| where timestamp > ago(1d)
| union
(exceptions
| where timestamp > ago(1d)) | | | Join | SELECT * FROM dependencies
LEFT OUTER JOIN exceptions
ON dependencies.operation_Id = exceptions.operation_Id | dependencies
| join kind = leftouter
(exceptions)
on $left.operation_Id == $right.operation_Id | join | | Nested queries | SELECT * FROM dependencies
WHERE resultCode ==
(SELECT TOP 1 resultCode FROM dependencies
WHERE resultId = 7
ORDER BY timestamp DESC) | dependencies
| where resultCode == toscalar(
dependencies
| where resultId == 7
| top 1 by timestamp desc
| project resultCode) | toscalar | | Having | SELECT COUNT(\*) FROM dependencies
GROUP BY name
HAVING COUNT(\*) > 3 | dependencies
| summarize Count = count() by name
| where Count > 3 | summarize
where |

Related content

• Use T-SQL to query data

7 - Timezone

The following is a list of timezones supported by the Internet Assigned Numbers Authority (IANA) Time Zone Database.

Related functions:

• datetime_local_to_utc()
• datetime_utc_to_local()
• datetime-list-timezones()