Kusto Query Language 101

Recently I’ve started spending more time using Azure Sentinel and I wanted to get up to speed on the Kusto Query Language. This is a collection of my ‘Kusto Query Language 101’ learnings.

What is Kusto Query Language(KQL)?

KQL is a read-only language similar to SQL that’s used to query large datasets in Azure.  Unlike SQL, KQL can only be used to query data, not update or delete. KQL is commonly used in the following Azure services:

• Azure Application Insights
• Azure Log Analytics
• Azure Monitor Logs
• Azure Data Explorer

Fun fact, Kusto is named after Jacques Cousteau, as a reference to “exploring the ocean of data”.

How about an example?

To get started, let’s look at a sample KQL query.

SecurityEvent
| where AccountType == 'User'
| count

• The query starts with a reference to the SecurityEvent table.
• The data is then ‘piped’ through a where clause which filters the rows by the AccountType column. The pipe is used to bind together data transformation operators. Both the where clause and pipe (|) delimiter are key to writing KQL queries.
• The query returns a count of the surviving rows.

You can chain additional statements together by piping the data to further statements:

SecurityEvent
| where AccountType == 'User'
| where TimeGenerated >= datetime(2021-01-01) and TimeGenerated < datetime(2021-01-31)
| count

Operators and Functions

Here are some KQL operators and functions I’ve been using recently.

String comparison

• Equal: ==
  SecurityEvent
| where AccountType == 'User'
  
• Not equal: !=
  SecurityEvent
| where AccountType != 'User‘

String Matching

• Case Sensitive Match: ==
  SecurityEvent
| where AccountType == 'User'
  
• Case Insensitive Match: Either =~ or contains/has
  SecurityEvent
| where AccountType =~ 'User'

SecurityEvent
| where AccountType contains 'User'

SecurityEvent
| where AccountType has ‘User’
  
• Case Insensitive and not Equal To Match: !~
  SecurityEvent
| where AccountType !~ 'User'
  
• Match on values starting or ending with a specific string.
  SecurityEvent
| where Account startswith 'NT'

Performance Tips
If there are two operators that do the same task, always use the case-sensitive one.

• instead of =~, use ==
• instead of in~, use in
• instead of contains, use contains_cs

Microsoft has outlined several best practices to improve your KQL query performance. You can find them here.

String Concatenation

The strcat() function allows you to concatenate between 1 and 64 arguments. If one of the arguments is not a string, it will forcibly be converted to a string.

print str = strcat("hello", " ", "world")

Numeric Operators

• Equal: ==
  SecurityEvent
| summarize count() by TargetUserName
| where count_ == 1000
  
• Greater Than: >
  SecurityEvent
| summarize count() by TargetUserName
| where count_ > 1000
  
• Less Than: <
  SecurityEvent
| summarize count() by TargetUserName
| where count_ < 1000
  
• Less or Equal: <=
  SecurityEvent
| summarize count() by TargetUserName
| where count_ <= 1000
  
• Greater or Equal: >=
  SecurityEvent
| summarize count() by TargetUserName
| where count_ >= 1000
  

DateTime and Timespan

KQL offers powerful functionality around datetime and timespan values. Here are a few examples:

• Refer to d, h, m, and s for days, hours, minutes and seconds.
  SecurityEvent
  | where TimeGenerated > now(-7d)
  
• Perform arithmetic operations on values of types datetime and timespan:
  datetime(2021-01-31) + 1d
  Returns: 2/1/2021, 12:00:00.000 AM
  
  Divide two timespan values to get the quotient
  1h / 1s
  Returns: 3,600
  
• Multiply numeric values (such as double and long) by a timespan value to get a timespan value.
  1.5 * 1hr
  Returns: 1:30:00

Sorting

• Sort by:
  Sort the rows of the input table
  SecurityEvent
| sort by TimeGenerated
  
• Take:
  Returns up to the specified number of rows.
  SecurityEvent
| take 5
  
• Top:
  Returns the first N records sorted by the specified columns.
  SecurityEvent
| top 5 by TimeGenerated

Summarize

Similar to the GROUP BY SQL command, summarize groups together rows using some comparison, and then performs an aggregation.

SecurityEvent
| summarize count() by TargetUserName

The above query returns the number of security events by each target user.

Render

The render operator is used to create visualizations. These visualizations include:

• Area Chart
• Bar Chart
• Column Chart
• Pie Chart
• Scatter Chart
• Table
• Time Chart

Let’s add the render operator to the above query:
SecurityEvent
| summarize count() by TargetUserName
| render piechart

This generates a pie chart based on the query provided.

Resources

Here are some resources I’ve been using on my KQL journey. You may find them valuable too!

• Microsoft Documentation
• Azure Sentinel Ninja Training
• Log Analytics Demo
  All the above queries can be run in the Log Analytics Demo Workspace
• Visual Studio Code and the Kusto Extension Pack

Summary

In this blog post I covered some of the essential functions and operators of KQL. In future blog posts I will continue sharing my learnings around KQL and how to effectively use it with Azure Sentinel.

Thank you for reading!