Last weeks post introduced the basics of Sentinel Detection and Hunting rules.
If you missed it, you should read it here first before reading this post any further.
In this weeks post we’ll look at the
query part of the rule.
Querires are written in Kusto Query Language (KQL).
Kusto was the original codename for the Azure Application Insights platform that Azure Monitor is now based on.
If you’re wondering where the name comes from, it’s named after Jacques Cousteau – a French undersea explorer – and you’ll see some cheeky references to Jacques in the Kusto documentation.
I find the best way to learn new languages is to first review some basic queries to get a “feel” for it.
Introducing the Pipe
| character (called a pipe) is very much the same thing as in PowerShell: it passes (or ‘pipes’) the data through into the next command.
It is essential to understand how the results flow through the pipe
|. Everything on the left of the pipe is processed then passed to the right of the pipe.
Let’s look at an example:
OfficeActivity | where OfficeWorkload =~ "MicrosoftTeams" | where Operation =~ "MemberAdded"
The query above:
- creates a table called
- takes all instances where
OfficeWorkloadis equal to
MicrosoftTeams. After this filter has been run, it then,
- pipes the results to the next statement, which further filters the results to instances where the
Using operators on strings
=~ in the example above uses case-insensitive comparisons (e.g. match
MICROSOFTTEAMS are treated as the same thing). This operator can also be represented as
OfficeWorkload contains MicrosoftTeams).
To make it case sensitive you could use
If you wanted results that did not contain
MicrosoftTeams you could use
!~ (case insensitive),
!contains (case insensitive),
!= (case sensitive), or
!contains_cs (case sensitive).
There are wide range of string operators you can use, from
startswith through to
endswith. Check them all out here.
There are other types of operators too. Numerical operators can be useful too. For example, when you want to do a simple calculation;
let ten = 5 * 2
or define a time range;
let twoWeekLookback = now() - 14d
Which brings us to the let operator.
There are occasions where we might need to define new variables to pass in later parts (pipes) of the query.
let discardEventId = 4688;
In this example, the
discardEventId variable is declared as
You can use values in the data returned by a query as variables too;
let starttime = todatetime(''); let endtime = todatetime('');
Here I use the todatetime function to convert the
EndTimeISO field values to datetime format, to use for the respective variables
We can also reuse these variables to calculate new ones. For example;
let starttime = todatetime(''); let endtime = todatetime(''); let timedifference = endtime - starttime;
Don’t forget to end the line with the
; character, so that the variable is properly set.
In my first example, I used the where operator. Building upon that example to include everything covered so far;
let discardEventId = 4688; OfficeActivity | where OfficeWorkload =~ "MicrosoftTeams" | where Operation =~ "MemberAdded" | where EventID != discardEventId
This updated example now adds a variable, used to exclude events piped to the final statement
where EventID does not equal
Putting it all together
Hunting and Detection rules from the official GitHub repository for Sentinel are automatically imported.
Now you know the basics, you can start creating your own rules in Sentinel.
You can add new queries via the UI (where you only need to paste the query section of the rule).
…that helped me put this post together.
- Sentinel Hunting Queries (Official Repo)
- Construct KQL statements for Microsoft Sentinel module (Microsoft training)
- Microsoft Sentinel Hunting Content (Official Repo)
- Kusto Query Language Basics (in MS365 Defender)
Join the Signals Corps on Discord
Join our public community of intelligence analysts and researchers sharing new content hourly.
Turn any blog into structured threat intelligence.
Extract machine readable intelligence from unstructured data.
Know when software you use is vulnerable, how it is being exploited, and how to detect an attack.
View, modify, and deploy SIEM rules for threat hunting.