How I started learning KQL for Sentinel

I recently made it through the Microsoft Cloud Skill Challenge: Microsoft Ignite: Protect Everything Challenge and it is a lot of information in this challenge. Oh my lord, how Microsoft Security portfolio have grown!

But this chapter on Kusto Query Language (KQL) statements for Sentinel stood out for me, and gave me a much better entrance to learning to build KQL statements. I just wanted to share this, Microsoft also provides a demo-environment to test and build up KQL statements. You only need an Azure AD Account with Access to the Azure Portal when visiting this link: https://aka.ms/lademo

Quick: What is KQL? KQL is a statement language to run queries against Sentinels big ocean of information and alerts. Not unlike we query SQL databases. Hence the name Kusto Query Language. To my understanding mutiple statements is a query.

Statement build-up

A KQL query is build up of four parts visualized by Microsoft like this, but in my own words I would say Data -> Filter -> Analyze/Prepare -> Result.

Data is everything collected in Sentinel, so you choose a data category like SecurityEvent. We then filter through SecurityEvent, for instance to only show EventID 4626. We can then move directly to the result, or we can prepare the result to have a better view of the data we are looking for.

The first statement would just be like this, and returns all security events logged that last 24 hours.

SecurityEvent

LET Variables (Filter)

Moving on we can add variables to the statements, called LET in KQL. Makes it easier to reuse the same input through out the query. And like in this code, we use it to set time range. Like today is November 3rd and we want to search only 7 days back. Sorry, can’t search into the future, not yet!

let timeOffset = 7d;
let discardEventId = 4688;

SecurityEvent
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)
| where EventID != discardEventId

“ago()” is a function that will take the current Date and Time and subtract the value provided.

I did encounter some issues using LET variables in the demo-environment, so if it the where-operators fail to find the value of a variable, try setting the value in the commands and you will get a result.

Dynamic tables or lists by LET (Filter)

We can also create dynamic tables, for instance if you search for SecurityEvents by a group of users, you can make the group part of a table/list by placing them in a variable (LET).

let suspiciousAccounts = datatable(account: string) [
    @"\administrator", 
    @"NT AUTHORITY\SYSTEM"
];
SecurityEvent | where Account in (suspiciousAccounts)

let LowActivityAccounts =
    SecurityEvent 
    | summarize cnt = count() by Account 
    | where cnt < 1000;
LowActivityAccounts | where Account contains "SQL"

Search Operator (Filter)

We can also use search to make it search through multiple logs after entry with “err” in it, lik this code:

search in (SecurityEvent,SecurityAlert,A*) "err"

For the first search query, you may need to adjust the Time range to “Last hour” in the query window to avoid an error.

But it is slower then using the Where Operator to eliminate your search and get more specific results.

Where Operator (Filter)

You can try these statements one by one, to see the different results, which will result in closer and closer results to what you are looking for.

SecurityEvent
| where TimeGenerated > ago(1d)

SecurityEvent
| where TimeGenerated > ago(1h) and EventID == "4624"

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| where AccountType =~ "user"

SecurityEvent | where EventID in (4624, 4625)

Extend Operator (Prepare)

Now we are moving onto the next phase, which is preparing the result for a better view to our human eyes. Using the Extend operator we can create a new column and add result from the substring.

SecurityEvent
| where ProcessName != "" and Process != ""
| extend StartDir =  substring(ProcessName,0, string_size(ProcessName)-string_size(Process))

Order by Operator (Prepare)

We can change the order of rows in the result by utilizing the Order by operator, to sort after any column or multiple columns. Either ascending or descending. And to continue from extend operator, we can add ‘order by’ telling the result to descend the StartDir column and ascend the Process column.

SecurityEvent
| where ProcessName != "" and Process != ""
| extend StartDir =  substring(ProcessName,0, string_size(ProcessName)-string_size(Process))
| order by StartDir desc, Process asc

Project Operators (Prepare)

We can add, remove or rename columns from the result using Project Operators

Operator	Description
project	Select the columns to include, rename or drop, and insert new computed columns.
project-away	Select what columns from the input to exclude from the output.
project-keep	Select what columns from the input to keep in the output.
project-rename	Select the columns to rename in the resulting output.
project-reorder	Set the column order in the resulting output.

Lets test run some statements with project operators, here we choose to only show the Computer and Account column.

SecurityEvent
| project Computer, Account

We can add this to the previous statement.

SecurityEvent
| where ProcessName != "" and Process != ""
| extend StartDir =  substring(ProcessName,0, string_size(ProcessName)-string_size(Process))
| order by StartDir desc, Process asc
| project Process, StartDir

Take away single columns using project-away, if you need to hide a column.

SecurityEvent
| where ProcessName != "" and Process != ""
| extend StartDir =  substring(ProcessName,0, string_size(ProcessName)-string_size(Process))
| order by StartDir desc, Process asc
| project-away ProcessName

Summarize Operator (Prepare)

Summarize or count operator will create a new column to show the result, so lets test some statements.

SecurityEvent | summarize by Activity

To single it down we can try using the where operator and summarize multiple columns.

SecurityEvent
| where EventID == "4688"
| summarize count() by Process, Computer

We can also name the new column adding ‘cnt=count() in the statement.

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| summarize cnt=count() by AccountType, Computer

Using a variant of the count, dcount, to count the number of unique results in column.

SecurityEvent
| summarize dcount(IpAddress)

Additional statement from the real-world

The following statement is a rule to detect MFA failures across multiple applications for the same account.

The where operator for ResultDescription will filter the result set for results including “MFA”. Next, the statement “summarize” produces a distinct count of application names and group by User and IP Address. Finally, there’s a check against a variable created (threshold) to see if the number exceeds the allowed amount.

let timeframe = 1d;

let threshold = 3;

SigninLogs
| where TimeGenerated >= ago(timeframe)
| where ResultDescription has "MFA"
| summarize applicationCount = dcount(AppDisplayName) by UserPrincipalName, IPAddress
| where applicationCount >= threshold

Filter results with summarize operator

We can use the arg_max or arg_min to only show the top or bottom row.

SecurityEvent 
| where Computer == "SQL12.na.contosohotels.com"
| summarize arg_max(TimeGenerated,*) by Computer

SecurityEvent 
| where Computer == "SQL12.na.contosohotels.com"
| summarize arg_min(TimeGenerated,*) by Computer

Also the order which statements are written/run in the query will affect the result, like these two.

SecurityEvent
| summarize arg_max(TimeGenerated, *) by Account
| where EventID == "4624"

SecurityEvent
| where EventID == "4624"
| summarize arg_max(TimeGenerated, *) by Account

Statement 1 will show all list of all accounts which last event was 4624 (Logon).

Statement 2 will show a list of all 4624 events sorted when it is was generated with the Account name in first column.

Go try them out in the demo-environment and this Microsoft explanation:

Statement 1 will have Accounts for which the last activity was a Logon.

The SecurityEvent table will first be summarized and return the most current row for each Account. Then only rows with EventID equal to 4624 (Login) will be returned.

Statement 2 will have the most recent Logon for Accounts that have logged in.

The SecurityEvent table will be filtered to only include EventID = 4624. Then these results will be summarized for the most current Logon row by Account.

Prepare data using the summarize operator

The make_ functions return a dynamic (JSON) array based on the specific function’s purpose.

With the make_list() function we can make a list of computers and all accounts listed in the events with EventID 4624.

With the make_set() function we can make a set/list of all unique accounts listed in the events with EventID 4624.

Render Operator

Using the rendor operator we can generate visualization of the results, and here are the supported visualization types.

• areachart
• barchart
• columnchart
• piechart
• scatterchart
• timechart

Lets test using barchart.

SecurityEvent 
| summarize count() by Account
| render barchart

Or using timechart.

SecurityEvent 
| summarize count() by Account
| render timechart

There seems to be an infinite ways to combine operators and present the results, either for deep dive in events, reporting numbers, broader look at events and correlating events by querying for similar events.