Cheatsheet — KQL

Photo by Luke Chesser on Unsplash

Kusto Query Language (KQL) is used in Azure Application Insights for filtering analytics data. This article is a cheatsheet for KQL filters.

General Filters

Show Only 10 records

| take 10

Create variables — Approach 1 — Using extend

| extend employeeName = customDimensions["empName"]
| where employeeName == "Yuvraj"

Create variables — Approach 2 — Using let

let employeeName = customDimensions["empName"]
...
...
...
| where employeeName == "Yuvraj"

Order By Date Descending

| order by timestamp desc

Order By Date Ascending

| order by timestamp asc

Sort By Count

| sort by count_desc

Round Down a value

bin(4.5, 1) --> Returns 4

bin(timestamp, 15m) --> Returns data with 15 minutes average value

Attribute and Object Filters

Event Name

| where * has "UV:EMPLOYEE_SIGN_UP_COMPLETED"

Custom Direct Attribute

| where employeeGroup == "HR"

Custom Nested object’s attribute

| extend employeeStr = tostring(customDimensions["employeeData"])
| extend employeeObj = parse_json(employeeStr)
| where employeeObj.group == "HR"

Array of primitives

| where employeeDepartment in ("HR", "Accounts", "Engineering")

Row with Max Value

| summarize arg_max(userId, *)

Row with Min Value

| summarize arg_min(userId, *)

Date Filters

Before 5 days

| where timestamp > ago(5d)

Between last 5 days and 10 days

| where timestamp > ago(5d) and timestamp < ago(10d)

String Manipulations

Sub-string | Get string between 4th & 10th characters

let addressStr = tostring(userAddress)
| extend refinedAddress = substring(addressStr, 3, 9)

Visualizations

Render all columns in table format

| summarize by (userId)
| render table

Render only few columns in table format

| project userId, employeeName, employeeAddress

Render Bar Chart

| summarize count() by employeeLeaves
| render barchart

Compare two event counts overall time based

customEvents
| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL"),
| summarize count() by bin(timestamp, 30m), name

Compare two event counts overall (Count column name is _count )

customEvents
| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL"),
| summarize count() by name
| sort by count desc

Update the column name in result (Count column name iseventCount )

customEvents
| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL"),
| summarize eventCount = count() by name
| sort by eventCount desc

Show total of custom attribute’s value

customEvents
| where name in (TRANSACTION_SUCCESS"),
| project name, amountTransferred=toint(customMeasurements["amount"])
| summarize Total_Transaction_Events = count() by name, Total_Amount_Transffered = sum(amountTransferred)

Add new column to table/result

customEvents
| project userId, startTime, endTime
| extend duration = endTime - startTime