If you are reading this blog post via a 3rd party source it is very likely that many parts of it will not render correctly. Please view the post on signalscorps.com for the full interactive viewing experience.
In this post I will take a look at creating basic Kusto rules for Microsoft Sentinel (and show a manual conversion of a Sigma rule to Kusto rule).
Continuing on from the last tutorial that examined Google’s YARA-L rule format, this week I will do the same with Kusto queries used by Microsoft Sentinel.
Microsoft Sentinel, whilst not exclusively a SIEM (it started life as a log aggregation platform), it is now beginning to compete well with other SIEM’s in the market that have grown from similar beginnings (Splunk, Elastic, etc.).
Given many organisations are still heavy Microsoft shops, it is not surprising. Exchange for email. Azure for cloud servers. Windows OS’s.
This tutorial will introduce the types of Sentinel Rules, how Kusto queries are written, and finally, how to convert a Kusto Query in Microsoft Sentinel into Sigma format.
Sentinel Rules vs Queries
It is first important to make the distinction in Sentinel between the Rule and the Query (sometimes you will hear the two terms used interchangeably).
Microsoft Sentinel uses Rules. A Rule contains a Kusto Query.
The Kusto Query Language (KQL) is not unique to Sentinel, or security. Unlike YARA-L which is specific to security events. Microsoft, who created the language, describe it as;
Kusto Query Language is a simple and productive language for querying big data.
Kusto was the original codename for the Azure Application Insights platform that Azure Monitor is now based on.
If you are 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.
Hunting vs. Detection Rules in Sentinel
Microsoft Sentinel has two types of security content; Hunting Rules and Detection Rules.
The two are very similar in structure, but it is important to understand the difference in there intended use.
A Detection Rule is typically used for automated analysis. It is designed to be run on a schedule (inc. real-time) or when another event occurs (e.g. MCAS raising an alert) to identify security incidents.
Hunting Rules are designed to be run on-demand, typically during a hunt.
Both rule types are structured in YAML and most of the properties used by both rule types are the same.
The biggest difference between the two rule types is that Detection rules usually have time and threshold properties that can be defined to reduce noise on scheduled runs, e.g.
kind: scheduled queryFrequency: 1d queryPeriod: 1d triggerOperator: gt triggerThreshold: 0
Hunting Rules do not have such properties because they are not triggered by time or by a condition – they are executed on-demand.
You can see the required properties needed for each type of Rule in the Query Style Guide.
Hunting Rules will also typically require a human to interpret the results and decide on the next step based on the result. As a general rule of thumb, if Hunting Rules were made into Detection Rules there would be a lot of false positives.
Let me illustrate using an example of each rule;
- Hunting Rule: Windows System Time changed on hosts: Identifies when the system time was changed on a Windows host which can indicate potential timestomping activities. Event ID 4616 is only available when the full event collection is enabled.
- Detection Rule: Security Event log cleared: Checks for event id 1102 which indicates the security event log was cleared. It uses Event Source Name “Microsoft-Windows-Eventlog” to avoid generating false positives from other sources, like AD FS servers for instance.
You can see the Detection Rule is looking for an event that is clearly a security incident – security events should not be cleared. However, the Hunting Query identifies system time changes, which can be indicative of malicious AND benign activity.
The Sentinel content repository also contains directories for each type of Rule which can be a good resource in comparing the two types;
I like to think of Sentinel Rules as structured into five parts:
- Metadata: general information about the rule
- Data Connectors: the data the rule should consider
- Query: the detection
- Schedule: how often/when it should be run
- Trigger information: information for when the rule is triggered
id: GUID (Required for Detection and Hunting)
name: A short name of the detection in the form of a label (Required for Detection and Hunting)
description: Details the purpose of the query and any references (Required for Detection and Hunting)
severity: The level of impact on a target environment caused by the activity (Required for Detection)
version: This is the version of this template (Required for Detection)
kind: This specifies the kind of detection (Required for Detection)
Here is an example of a Detection rule using all these properties;
id: 223db5c1-1bf8-47d8-8806-bed401b356a4 name: Failed login attempts to Azure Portal description: | 'Identifies failed login attempts in the Azure Active Directory SigninLogs to the Azure Portal. Many failed logon attempts or some failed logon attempts from multiple IPs could indicate a potential brute force attack. The following are excluded due to success and non-failure results: References: https://docs.microsoft.com/azure/active-directory/reports-monitoring/reference-sign-ins-error-codes 0 - successful logon 50125 - Sign-in was interrupted due to a password reset or password registration entry. 50140 - This error occurred due to 'Keep me signed in' interrupt when the user was signing-in.' severity: Low version: 1.0.2 kind: Scheduled tactics: - CredentialAccess relevantTechniques: - T1110
Note, you will see the description property is split into multiple lines. Whilst I could not find a line length recommendation for Sentinel Rules, it seems most examples are < 100. I therefore recommend using a maximum line length of 100 for your Rules.
A note on MITRE ATT&CK
MITRE ATT&CK provides a standard way to classify the behavior of a threat using tactics and techniques.
Sentinel Rules support MITRE ATT&CK classification using the properties;
tactics: Relevant MITRE Tactics (Required for Detection and Hunting)
relevantTechniques: Relevant MITRE Techniques ID (Required for Detection and Hunting)
These were shown in the previous example, and here is another example; of a query identifying phishing attempts can be classified as MITRE ATT&CK in a Sentinel Rule as;
tactics: - InitialAccess relevantTechniques: - T1566
You need to specify a MITRE ATT&CK
tactics and, optionally, a
relevantTechniques that belongs to the selected tactic.
Tactics are passed by name (remove any whitespace, e.g. the tactic
Initial Access becomes
InitialAccess). Techniques can be defined using their ATT&CK ID.
Note, at the time of writing, ATT&CK sub-techniques are not supported.
A note on Custom Properties (optional)
You can use the
customDetails property to provide a list of custom properties. Custom Details are defined as a key-value pairs of property name and column name, for example;
customDetails: author: Signals Corps falsePositives: Likely to create some noise if DEBUG log level used
Custom Properties are included in the alert and therefore give immediate event context to the analyst for the alert. You can read more about how Custom Properties can be used here.
2. Data Connectors
Microsoft Sentinel comes with several data connectors for Microsoft and non-Microsoft products to help get data in.
A Data Connectors job is to collect and parse data from a source into Sentinel.
In the context of a Rule, the Data Connectors section defines the event data sources the Rule should consider using the
connectorId and the
dataTypes it supports;
requiredDataConnectors: - connectorId: AzureActivity dataTypes: - AzureActivity
In the above example the Azure Activity connector is being used, and only data from the Azure Activity data type that connector produces should be searched.
Some Data Connectors can produce one or more data types that can be used in a rule, for example;
requiredDataConnectors: - connectorId: AzureActiveDirectory dataTypes: - SigninLogs - AuditLogs
query is the part of the rule that is responsible for detecting security events in Sentinel. As noted earlier, queries are written in KQL.
Whilst I will not go into the full details of constructing KQL queries in this post (this tutorial, Construct KQL statements for Microsoft Sentinel module, is a good start for that, I will touch on some of the basics to get you started.
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 me show you an example:
query: | OfficeActivity | where OfficeWorkload =~ "MicrosoftTeams" | where Operation =~ "MemberAdded"
The query above:
- creates a table called
- takes all instances where the
OfficeWorkloadfield is 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;
query: | let ten = 5 * 2 WindowsEvent | where EventId > ten
Which brings us to the let operator.
There are occasions where you might need to define new variables to pass in later parts (pipes) of the query.
query: | let discardEventId = 4688; OfficeActivity | where EventID != discardEventId
In this example, the
discardEventId variable is declared as
4688 and I am then using the where filter to match on all events where
EventID does not equal
You can use values in the data returned by a query as variables too;
query: | let starttime = todatetime(''); let endtime = todatetime(''); OfficeActivity | where TimeGenerated between(starttime..endtime) | where OfficeWorkload =~ "MicrosoftTeams"
Here I use the todatetime function to convert the
EndTimeISO field values to datetime format, to use for the respective variables
I can also reuse these variables to calculate new ones. For example;
query: | let starttime = todatetime(''); let endtime = todatetime(''); let timedifference = endtime - starttime; OfficeActivity | where Session > 1000
Don’t forget to end the line with the
; character, so that the variable is properly set.
In my first examples, I used the where operator. Building upon that example to include everything covered so far;
query: | 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
where is just one of many tabular operators in KQL.
4. Schedule (detection rules only)
Detection Rules of
kind: scheduled require the following additional properties;
queryFrequency: How often the query runs against the data. Queries can run as frequent as every 5 minutes or as infrequent as every 2 weeks.
queryPeriod: The time frame that the query will run across, such as the last 3 days.
triggerOperator: Indicates the mechanism that triggers the alert, such as greater than a count of 6.
triggerThreshold: Indicates the threshold count related to the mechanism that triggers the alert, such as equal to 1.
Here is an example YAML definition for a Detection Rule of
queryFrequency: 1d queryPeriod: 7d triggerOperator: gt triggerThreshold: 0
Here the Rules rules once every day (
1d) and will look back at data in Sentinel for the last 7 days (
The rule will on trigger if the Query returns more than (
gt) 0 results.
Detection Rules of
kind: NRT (Near Real Time) do not need these properties as they are run against live events flowing into Sentinel.
5. Trigger information
Grouping alerts (optional)
It is only a matter of time until a query produces a match. Sentinel Rules offer ways of dealing with matches to support analysts working the triggered events.
eventGroupingSettings property allows you to define how multiple triggers of a Rule should be handled.
You might want to alert each time the query is true, in which case you could set;
eventGroupingSettings: aggregationKind: AlertPerResult
Alternatively, you might choose to group all the results triggered by a Rule into one alert;
eventGroupingSettings: aggregationKind: SingleAlert
SingleAlert is the default if no
eventGroupingSettings properties are defined.
Mapping entities (required Detection, recommended Hunting)
When detections are triggered by Microsoft Sentinel, they also contain data items that Sentinel can recognise and classify into categories as entities. The
entityMappings property in a Rule defines these.
Entities provide a definition of to an event which in turn can be used with other Sentinel features such as the Investigation Graph, Incidents, Bookmarks once a rule has triggered.
You will find a useful reference table of entity types and supported identifiers available here.
Here is an example;
entityMappings: - entityType: IP fieldMappings: - identifier: Address columnName: IPCustomEntity
In the example above, the
IPCustomEntity column value (created/exposed in the query) is used to map the value of this column to the IP Address entity. The
columnName could be anything but it must be an output from your query.
For example, in this query (line 88) you will see
| extend timestamp = StartTime, AccountCustomEntity = UserPrincipalName, IPCustomEntity = IPAddress
It is the output of this part of the query that is then mapped to the IP Address entityType/identifier.
Sentinel will now recognise the results of the query as containing the defined entity type (IP Address), which can then be used with other Sentinel features (e.g. right Quick Investigation function for IP Address entity in the triggered rule).
Alert details (optional)
Alert Details allow Rules to have dynamic values for the Displayed name, Description, Tactics and Severity properties of the alert. This is done using the
An example of Dynamic Alert Details:
alertDetailsOverride: alertDisplayNameFormat: rule display name alertDescriptionFormat: rule display name alertTacticsColumnName: dynamicTactic alertSeverityColumnName: dynamicSeverity
dynamicSeverity are output fields of the scheduled alert query.
By using dynamic alert details, the same rule can generate different incidents, for example with different severity. The information displayed to the analyst can also include variable information such as relevant entity names to help the analyst understand the incident faster.
Sigma Rule to Microsoft Sentinel Rule
Now you know a little more about Sentinel Rules, lets try and manually create one from an existing Sigma Rule.
Here is a public Sigma Rule detecting the deletion of malware detections in Defender;
title: Windows Defender Malware Detection History Deletion id: 2afe6582-e149-11ea-87d0-0242ac130003 status: experimental description: Windows Defender logs when the history of detected infections is deleted. Log file will contain the message "Windows Defender Antivirus has removed history of malware and other potentially unwanted software". author: Cian Heasley references: - https://docs.microsoft.com/en-us/windows/security/threat-protection/microsoft-defender-antivirus/troubleshoot-microsoft-defender-antivirus date: 2020/08/13 modified: 2021/05/30 tags: - attack.defense_evasion - attack.t1070.001 logsource: product: windows service: windefend detection: selection: EventID: 1013 condition: selection fields: - EventID - EventType falsepositives: - Deletion of Defender malware detections history for legitimate reasons level: high
First I can map all of the metadata like so;
id: d17b3028-f438-4aee-8b34-2a1a3ce0fbe2 name: Windows Defender Malware Detection History Deletion description: Windows Defender logs when the history of detected infections is deleted. Log file will contain the message "Windows Defender Antivirus has removed history of malware and other potentially unwanted software". severity: High version: 0.0.1 tactics: - DefenseEvasion relevantTechniques: - T1070 customDetails: sigma_uuid: 2afe6582-e149-11ea-87d0-0242ac130003 sigma_status: experimental sigma_author: Cian Heasley sigma_references: ['https://docs.microsoft.com/en-us/windows/security/threat-protection/microsoft-defender-antivirus/troubleshoot-microsoft-defender-antivirus'] sigma_date: 2020/08/13 sigma_modified: 2021/05/30 sigma_falsepositives: ['Deletion of Defender malware detections history for legitimate reasons'] sigma_fields: ['EventID', 'EventType']
Note, GUIDs used in Sentinel Rules are slightly different to UUIDs (v4) used by Sigma Rules. I have therefore generated a new GUID for this rule and mapped the original Sigma UUID as a Custom Property.
Similarly, for all fields that do not map directly I have used Custom Properties to define them.
You might also have noticed I use the
T1070 in the Sentinel Rule, whereas the sub-technique is listed in the Sigma Rule. This is because Sentinel does not currently support ATT&CK sub-techniques.
Now I can map the
logsource to the correct Data Connectors. In this case, Windows events will be tracked in Windows event logs. So the Data Connector and Data Type needed is;
requiredDataConnectors: - connectorId: WindowsEventForwarding dataTypes: - WindowsEvent
Now all that is left is to write the query. In the example Sigma Rule only one Search Identifier is specified; to match on
EventID: 1013 AND
The WindowsEvent data table has the column EventID.
query: | WindowsEvent | where EventID == 1013
I have decided to treat this as a Hunting Rule, as opposed to a Detection Rule, as the query is likely to catch quite a few false positives – there are legitimate reasons Defender removes history.
Pro-tip: If you are writing queries in a text editor, and not the Sentinel UI, I would recommend validating the syntax of your query is correct by pasting it once written in the Sentinel UI.
Putting this together, it gives a final Sentinel Hunting Rule;
id: d17b3028-f438-4aee-8b34-2a1a3ce0fbe2 name: Windows Defender Malware Detection History Deletion description: Windows Defender logs when the history of detected infections is deleted. Log file will contain the message "Windows Defender Antivirus has removed history of malware and other potentially unwanted software". severity: High version: 0.0.1 tactics: - DefenseEvasion relevantTechniques: - T1070 customDetails: sigma_uuid: 2afe6582-e149-11ea-87d0-0242ac130003 sigma_author: Cian Heasley sigma_references: ['https://docs.microsoft.com/en-us/windows/security/threat-protection/microsoft-defender-antivirus/troubleshoot-microsoft-defender-antivirus'] sigma_date: 2020/08/13 sigma_modified: 2021/05/30 sigma_falsepositives: ['Deletion of Defender malware detections history for legitimate reasons'] sigma_fields: ['EventID', 'EventType'] requiredDataConnectors: - connectorId: WindowsEventForwarding dataTypes: - WindowsEvent query: | WindowsEvent | where EventID == 1013
Which I can save as
WindowsDefenderMalwareDetectionHistoryDeletion.yaml ready for use in Sentinel.
Useful links for writing Sentinel Rules
…that helped me put this post together.
- Sentinel Hunting Queries
- Microsoft Sentinel Hunting Content
- Construct KQL statements for Microsoft Sentinel module (Microsoft training)
- Kusto Query Language Basics (in MS365 Defender)
- Kusto Cheat Sheet
Next up: Splunk SPL
In the next part of this tutorial I will look at one final query language; Splunk SPL.
Sigma Certification (Virtual and In Person)
The content used in this post is a small subset of our full training material used in our Sigma training.
If you want to join a select group of certified Sigma professionals, subscribe to our newsletter below to be notified of new course dates.
Discuss this post
Never miss an update
Sign up to receive new articles in your inbox as they published.