---

Kusto Query Language Cheat Sheet for Azure Monitor

Kusto Query Language (KQL) is a powerful tool for querying and analyzing data within Azure Monitor, Log Analytics, App Insight, and Container Insight. This Kusto Query Language Cheat Sheet for Azure Monitor will help you get up to speed with the essential commands and techniques used in KQL. Whether a beginner or an experienced user, this guide is designed to make your work with KQL more efficient and effective.

Table of Contents

1. Introduction to KQL
2. General Filters
   • Show Only 10 Records
   • Create Variables — Approach 1: Using Extend
   • Create Variables — Approach 2: Using Let
   • Order By Date
   • Sort By Count
   • Round Down a Value
3. Attribute and Object Filters
   • Event Name
   • Custom Direct Attribute
   • Custom Nested Object’s Attribute
   • Array of Primitives
   • Row with Max Value
   • Row with Min Value
4. Date Filters
   • Before 5 Days
   • Between Last 5 Days and 10 Days
5. String Manipulations
   • Sub-string
6. Visualizations
   • Render All Columns in Table Format
   • Render Only Few Columns in Table Format
   • Render Bar Chart
   • Compare Two Event Counts Over Time
   • Compare Two Event Counts Overall
   • Update the Column Name in Result
   • Show Total of Custom Attribute’s Value
   • Add New Column to Table/Result
7. Conclusion
8. FAQs

Introduction to KQL

Kusto Query Language (KQL) is the cornerstone for anyone working with Azure Monitor, Log Analytics, App Insight, and Container Insight. It filters, manipulates, and visualises data, providing valuable insights into your applications and infrastructure. Understanding how to use KQL effectively can significantly improve your ability to troubleshoot and analyze data.

General Filters

1. Show Only 10 Records

To limit your query to show only the top 10 records, use:

| take 10

2. Create Variables — Approach 1: Using Extend

Creating variables with the extend command allows adding new columns to your result set.

| extend employeeName = customDimensions["empName"]

| where employeeName == "Shivansh"

3. Create Variables — Approach 2: Using Let

Using let to create variables is useful for reusing expressions in your queries.

let employeeName = customDimensions["empName"];

...

| where employeeName == "Shivansh"

4. Order By Date

You can order your results by date in descending or ascending order.

• Descending Order:

| order by timestamp desc

• Ascending Order:

| order by timestamp asc

5. Sort By Count

To sort your results based on count in descending order:

| sort by count_desc

6. Round Down a Value

Rounding down a value can be done using the bin function.

bin(4.5, 1) --> Returns 4

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

Attribute and Object Filters

1. Event Name

Filter events by name:

| where * has "UV:EMPLOYEE_SIGN_UP_COMPLETED"

2. Custom Direct Attribute

Filter by a direct custom attribute:

| where employeeGroup == "HR"

3. Custom Nested Object’s Attribute

For nested object attributes, use tostring and parse_json to extract and filter.

| extend employeeStr = tostring(customDimensions["employeeData"])

| extend employeeObj = parse_json(employeeStr)

| where employeeObj.group == "HR"

4. Array of Primitives

Filter rows where a column value is within a specific set:

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

5. Row with Max Value

Find the row with the maximum value in a column:

| summarize arg_max(userId, *)

6. Row with Min Value

Find the row with the minimum value in a column:

| summarize arg_min(userId, *)

Date Filters

1. Before 5 Days

Filter records older than 5 days:

| where timestamp > ago(5d)

2. Between Last 5 Days and 10 Days

Filter records between 5 and 10 days ago:

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

String Manipulations

1. Sub-string

Extract a substring from a string value:

let addressStr = tostring(userAddress);

| extend refinedAddress = substring(addressStr, 3, 9)

Visualizations

1. Render All Columns in Table Format

Display all columns in a table format:

| summarize by (userId)

| render table

2. Render Only Few Columns in Table Format

Select specific columns to render in a table:

| project userId, employeeName, employeeAddress

| render table                                                                                                                                                                                                      

3. Render Bar Chart

Create a bar chart visualization:

| summarize count() by employeeLeaves

| render barchart

4. Compare Two Event Counts Over Time

Compare the counts of two different events over time:

customEvents

| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL")

| summarize count() by bin(timestamp, 30m), name

| render barchart

5. Compare Two Event Counts Overall

Compare the total counts of two different events:

customEvents

| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL")

| summarize count() by name

| sort by count desc

6. Update the Column Name in Result

Rename a column in the query result:

customEvents

| where name in ("UV_SIGN_UP_SUCCESS", "UV_SIGN_UP_FAIL")

| summarize eventCount = count() by name

| sort by eventCount desc

7. Show Total of Custom Attribute’s Value

Sum a 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)

8. Add New Column to Table/Result

Add a new column to your result set:

customEvents

| project userId, startTime, endTime

| extend duration = endTime - startTime

Conclusion

Kusto Query Language (KQL) is a versatile and powerful tool for data analysis in Azure Monitor, Log Analytics, App Insight, and Container Insight. This Kusto Query Language Cheat Sheet for Azure Monitor provides a comprehensive overview of various KQL commands and filters, making it easier for you to query, analyze, and visualize your data effectively. Whether you're just getting started or looking to refine your skills, this guide has something for everyone.

FAQs

1. What is KQL used for in Azure Monitor? KQL is used for querying, filtering, and analyzing data in Azure Monitor and Application Insights.
2. How do I create a variable in KQL? You can create variables using extend or let commands.
3. How can I visualize data in KQL? KQL supports various visualizations like tables, bar charts, and more using the render command.
4. What function do I use to round down a value in KQL? Use the bin function to round down values.
5. How do I filter data by date in KQL? You can filter data by date using the timestamp and ago() functions.

With this Kusto Query Language Cheat Sheet for Azure Monitor, you should be well-equipped to handle most common KQL tasks in Azure Monitor, Log Analytics, App Insight, and Container Insight.

Happy querying!