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?

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

The | 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:

  1. creates a table called OfficeActivity
  2. takes all instances where OfficeWorkload is equal to MicrosoftTeams. After this filter has been run, it then,
  3. pipes the results to the next statement, which further filters the results to instances where the Operation equals MemberAdded.

Using operators on strings

The operator =~ in the example above uses case-insensitive comparisons (e.g. match MicrosoftTeams AND MICROSOFTTEAMS are treated as the same thing). This operator can also be represented as contains (e.g. OfficeWorkload contains MicrosoftTeams).

To make it case sensitive you could use == or contains_cs

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 4688.

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 StartTimeISO and EndTimeISO field values to datetime format, to use for the respective variables starttime and endtime.

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.

Where?

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 4688.

where is just one of many tabular operators in KQL.

Putting it all together

Sentinel Hunting new query

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).

Good luck!

…that helped me put this post together.




Join the Signals Corps on Discord

Join our public community of intelligence analysts and researchers sharing new content hourly.


Obstracts

Obstracts

Turn any blog into structured threat intelligence.

Stixify

Stixify. Extract machine readable intelligence from unstructured data.

Extract machine readable intelligence from unstructured data.


Vulmatch

Vulmatch

Know when software you use is vulnerable, how it is being exploited, and how to detect an attack.

SIEM Rules

SIEM Rules. Your detection engineering database.

View, modify, and deploy SIEM rules for threat hunting.