Query Style Guide

Goal

This document aims to create a uniform style for Microsoft Sentinel and Microsoft 365 Defender content provided to and by Microsoft. We encourage external contributors to follow this same guidance, but this is not enforced. Microsoft will review and update any query that is pulled into the Microsoft Sentinel UX with the requirements below as needed. Queries for Microsoft 365 Defender will flow into both Microsoft Sentinel and Microsoft 365 Defender.

Detections and Hunting Queries

Format

File format is YAML and should be validated with any YAML syntax validator, there are many online.

id

Required for Detections and Hunting Queries

This is a standard GUID. You can generate from just about any development tool, online GUID generator, or from PowerShell via the New-GUID cmdlet.

• Must NOT collide with other GUIDs.
• Be cautious when reusing files.

kind

Required for Detections Queries only

This specifies the kind of detection. Accepted values are:

• "scheduled" - requires defining additional properties listed below (queryFrequency, queryPeriod, triggerThreshold, triggerOperator)
• "NRT" - Near Real Time

name

Required for Detections and Hunting Queries

A short name of the detection in the form of a label. This should include what the detection is about without reading the full description. Note that you can use alertDetailsOverride to provide a dynamic name that will make it easier for analysts to understand the alert.

• It should be clear which entities are performing the suspicious activities on which datatype.
• Use Sentence case capitalization
• Do NOT end with a period
• Length SHOULD NOT exceed 50 chars when possible
• Terms to avoid (applies also to Description):

Examples
Avoid	Use
IP	IPAddress
Execute	Run
Suspicious,Suspect	Unexpected, Anomalous, Rare

description

Required for Detections and Hunting Queries

Details the purpose of the query and any references such as EventID explanations or URL references. Note that you can use alertDetailsOverride to provide a dynamic description that will make it easier for analysts to understand the alert.

• Starts with - "This query searches for" or "Identifies"
• Is not a copy of the name field, it needs to be more descriptive.
• 'Description' for 'Hunting Query' should NOT exceed 255 characters.
• Should attempt to be a max of 5 sentences.
• Do NOT Describe the Data source (connector or datatype).
• Do NOT provide a Technical explanation for the query language used.
• Standard English capitalization for description section.

What NOT to do	Instead, do this
clients with a high reverse DNS count could be carrying out scanning activity. Alert is generated if the IP performing such reverse DNS lookups was not seen doing so in the preceding 7-day period.	This query identifies IP addresses performing a high rate of reverse DNS lookups and has not been seen doing this lookup in the previous 7 days.

severity

Required for Detections only

The level of impact on a target environment caused by the activity the analytic is triggered on should it be a true positive.

• Informational: The incident may not represent a security threat directly but may be of interest for follow up investigation, or to add context or situational awareness to an analyst.
• Low: Immediate impact is minimal, and a threat actor would need to conduct multiple steps before achieving impact on an environment.
• Medium: The threat actor could perform some impact on the environment with this activity, but it would be limited in scope or require additional activity.
• High: The activity identified provides the threat actor with wide ranging access to conduct actions on the environment or is triggered by impact on the environment.

Additional Notes:

• Severity level defaults are not a guarantee of current or environment impact level.
• This applies only to Azure Sentinel analytic templates. Severity in the Alerts table is otherwise controlled by the security service for which the alert came from.
• You can use alertDetailsOverride to provide a dynamic severity that will depend on the actual outcome of the query.

requiredDataConnectors

If there is no current data connector mapping, then an open brace must be used - requiredDataConnectors: []

GitHub contains a general list based on the folder structure - https://github.com/Azure/Azure-Sentinel/tree/master/Detections.

connectorId

Required for Detections and Hunting Queries

One or more from the list of Data Connectors. connectorId: values are not exact matches to the list in the URL above. See reference below dataTypes section for connectorId and dataTypes mapping. This needs to be the same as the id value of data connectors which is a text field. If the analytic rule or hunting query does not have a data connector dependency this can be blank.

dataTypes

Required for Detections and Hunting Queries

One or more from the list of Data Types in your workspace. GitHub contains a general list based on folder structure.

Note: For partners that create Data Connectors, the connectorId maps to the id value in your JSON. From the template -

connectorId is based on the "id" in the JSON

"id": "ProviderNameApplianceName"

dataTypes value is based on "name" in the dataTypes section of the JSON

*If the detection or hunting query operates on a Kusto Function/ Parser instead of the table (like Syslog, CommonEventFormat, _CL), dataTypes should be the Kusto Function name / Parser name and not the table name.

"dataTypes": [ { "name": "CommonSecurityLog (DATATYPE_NAME)", "lastDataReceivedQuery": "\nCommonSecurityLog\n| where DeviceVendor == \"PROVIDER NAME\"\n }

Table below has the built in connectorId and dataTypes mappings for the UX:

connectorId	dataType
AWS	AWSCloudTrail
AzureActiveDirectory	SigninLogs | AuditLogs
AzureActiveDirectoryIdentityProtection	SecurityAlert (IPC)
AzureActivity	AzureActivity
AzureAdvancedThreatProtection	SecurityAlert (AATP)
AzureInformationProtection	InformationProtectionLogs_CL | SecurityAlert (AIP)
AzureMonitor(IIS)	W3CIISLog
AzureMonitor(VMInsights)	VMConnection
AzureMonitor(WireData)	WireData
AzureSecurityCenter	SecurityAlert (ASC)
IoT	SecurityAlert (ASC for IoT)
BarracudaCloudFirewall	Syslog(Barracuda)
Barracuda	CommonSecurityLog (Barracuda) | Barracuda_CL
CheckPoint	CommonSecurityLog (CheckPoint)
CiscoASA	CommonSecurityLog (Cisco)
Citrix	CitrixAnalytics_SAlerts_CL
CEF	CommonSecurityLog
CyberArk	CyberArk
DNS	DnsEvents | DnsInventory
ExtraHopNetworks	CommonSecurityLog (‘ExtraHop’)
F5BigIp	F5Telemetry_LTM_CL | F5Telemetry_system_CL | F5Telemetry_ASM_CL
F5	CommonSecurityLog (F5)
Fortinet	CommonSecurityLog (Fortinet)
MicrosoftCloudAppSecurity	SecurityAlert (MCAS) | McasShadowItReporting
MicrosoftDefenderAdvancedThreatProtection	SecurityAlert (MDATP)
MicrosoftThreatProtection	DeviceEvents | DeviceFileEvents | DeviceImageLoadEvents | DeviceInfo | DeviceLogonEvents | DeviceNetworkEvents | DeviceProcessEvents | DeviceRegistryEvents
WAF	AzureDiagnostics (Application Gateways)
Office365	OfficeActivity (SharePoint) | OfficeActivity (Exchange) | OfficeActivity (Teams)
OfficeATP	SecurityAlert (Office 365 Security & Compliance)
OneIdentity	CommonSecurityLog (OneIdentity)
PaloAltoNetworks	CommonSecurityLog (PaloAlto)
SecurityEvents	SecurityEvents
Symantec	SymantecICDx_CL
Syslog	Syslog
ThreatIntelligenceTaxii	ThreatIntelligenceIndicator
ThreatIntelligence	ThreatIntelligenceIndicator
TrendMicro	CommonSecurityLog (TrendMicroDeepSecurity)
WindowsEventForwarding	WindowsEvent
WindowsFireWall	WindowsFirewall
Zscaler	CommonSecurityLog (Zscaler)

queryPeriod

Required for Scheduled Detections only

The time frame that the query will run across, such as the last 3 days.

• Expressed in Kusto Query Language (KQL) TimeSpan Format (for example - 3 days is 3d, 2 hours is 2h).
• Any learning or reference period MUST be included within this time.
• Maximal Value Supported (technical limitation): 14d

queryFrequency

Required for Scheduled Detections only

How often the query runs against the data. Queries can run as frequent as every 5 minutes or as infrequent as every 2 weeks.

• Expressed in Kusto Query Language (KQL) TimeSpan Format
• QueryFrequency must be less than, or equal to, the QueryPeriod.
• If the QueryPeriod is greater than or equal to 2 days (2d), the QueryFrequency value MUST NOT be less than 1 hour (1h) and is usually only used for High severity detections
• Customer can adjust lower if they see fit, but we don't want to impact perf in customers environments by default.

triggerOperator

Required for Scheduled Detections only

Indicates the mechanism that triggers the alert, such as greater than a count of 6 (in this case, an alert will be triggered if the number of results returned from the query is higher than 6).

Supported values:

• gt – Greater Than
• lt – Less Than
• eq – Equal To

triggerThreshold

Required for Scheduled Detections only

Indicates the threshold count related to the mechanism that triggers the alert, such as equal to 1.

Supported Values:

• Int – Any integer between 0 and 10000.

If the AlertTriggerOperator is set to Greater Than and the AlertTriggerThreshold is set to 1, then the alert will trigger if the number of results from the query is higher than 1.

tactics

Required for Detections and Hunting Queries

Relevant MITRE Tactics. Note that you can use alertDetailsOverride to provide a dynamic tactic that will depend on the actual outcome of the query.

• ATT&CK Framework v13 Supported
• Names MUST NOT have any spaces
• Example – InitialAccess or LateralMovement

relevantTechniques

Required for Detections and Hunting Queries

Relevant MITRE Techniques ID

• ATT&CK Framework v13 Supported
• MUST match MITRE Tactics
• Example: T1100 or T1120 or T1595.003

query

Required for Detections and Hunting Queries

This is the query that will run every "QueryFrequency" time, and trigger an alert if the number of results from the query meets the condition defined in "triggerThreshold" and "triggerOperator".
Kusto Query Language (KQL)

• The query is limited to 10,000 characters. If your query section is longer, then look to reduce the number of characters. Generally, this is due to including a static list of items used for comparison in the query body. It is recommended that these lists are moved to using a Watchlist function, custom json/csv with your list or custom function with your list.
• The query body must have at least one space in front of each line, we standardized on 2 for ease of reading.
• If you are submitting a query for a datatype that does not have a folder in the Detections folder or Hunting Queries folder, be sure to name the sub-folder that will contain the YAML files the name of the table being queried.
  • For example if your query is against the AzureDevOpsAuditing table, then create a folder named AzureDevOpsAuditing
• Define the human readable names for explicit constants:
  • let FailedLoginEventID = 4625;
  • let countThreshold = 6;
• Use of comments to clarify the query is highly recommended.
  • Comments must be on a separate line, not at the end of a query statement line
  • // Removing noisy processes for an environment, adjust as needed
• If you are referencing a parser instead of a table name, then be sure to be clear about this in the description and with a comment next to the parser function reference. The parser must be imported first into the workspace, otherwise these queries will not recognize it as a valid query.
• At least return every available entity field for mapping. See Entity Mapping below.
• Sanitize the returned table so that it provides only the necessary properties to investigate further
• No TimeGenerated filter is required when a simple lookback is used across the entire query. This will be controlled by the queryPeriod value in the YAML.
• If there is baselining or historical comparison, such as comparing today to the previous 7 days, then the query must include a time bounded filter such as | where TimeGenerated >= ago(lookback) as the YAML template does not currently support multiple queryPeriod values.
  • Recommend not using timeframes lower than 1d unless for a very specific reason
  • Not recommended to go over 14 days as performance can be impacted.
• Summarize when needed, if you do be sure to include the time field (usually TimeGenerated) as it is needed in the Entity part.
  • Bring thru both the min() and max() like so - | summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated)
  • Use only StartTime and EndTime, do NOT assign the fields the names StartTimeUtc or EndTimeUtc as this can conflict with UX preferences by users
  • Additionally, bring thru as many fields as possible to help the user understand the context of the alert. It is recommended you bring thru at least one of primary entities: Host, Account, IP

Required for Hunting Queries

Hunting queries should not include a lookback timeframe unless the query is a join or has a need for a very specific lookback time. The Hunting blade in Azure Sentinel passes in the lookback time to the query. Do not include this type of filter in your queries submitted to the Hunting folder. | where TimeGenerated >= ago(7d) If a lookback time is required as is for this hunting query then the time values need to be mapped a specific way.

• Implementation example:

  let starttime = todatetime('{{StartTimeISO}}');

  let endtime = todatetime('{{EndTimeISO}}');

  let lookback = starttime - 7d;

eventGroupingSettings

An alert rule can create a separate alert for each result of the query. For example, a rule that identifies 3rd party alerts in the event steam, may want to create an Azure Sentinel alert record for each source alert.

For single alert for all query results (the default), use

eventGroupingSettings:
  aggregationKind: SingleAlert

For an alert per result use:

eventGroupingSettings:
  aggregationKind: AlertPerResult

entityMappings

Required for Detections and recommended for Hunting Queries

Mapping is the process of extracting entities from query’s results for later use in features such as the Investigation Graph, Incidents, Bookmarks.

Hunting query prefer to include

Can be included, not required but supports common term for time value in the results

• timestamp

  • This is generally TimeGenerated

• Implementation examples: | extend timestamp = TimeGenerated or | extend timestamp = StartTime

Entity mapping

Required

Mapping entities now supports Entity Type and Entity Identifier. The list of entity types and their identifiers can be found here (also in the table below).

Add a new section to the YAML template after the Query section for each entity type you want to assign. At least 1 EntityType is required.

Note: The field referenced for columnName not required to be the old name mapping. In the example below AccountCustomEntity is used, it could be SubjectAccount or UserPrincipalName, but it MUST be an output from your query.

Note: We have special identifier mappings for Account and Host, you can use FullName as the identifier for both of these. See below example.

General format of new entity mapping

entityMappings:
  - entityType: <EntityType>
    fieldMappings:
      - identifier: <V3PropertyName>
        columnName: <ColumnName>
      - identifier: ... # Up to 3 identifiers
  - entityType: ... # Up to 10 entity mappings

An example of new entity mapping definition

entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: HostCustomEntity
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: ClientIP
  - entityType: DNS
    fieldMappings:
      - identifier: DomainName
        columnName: Name

Limitations and Rules

• Up to 10 entity mappings can be defined per template.
• Up to 3 field mappings (i.e. identifiers) can be defined per entity mapping.
• The entityType MUST match one of the following (case sensitive):
  • Account
  • Host
  • IP
  • Malware
  • File
  • Process
  • CloudApplication
  • DNS
  • AzureResource
  • FileHash
  • RegistryKey
  • RegistryValue
  • SecurityGroup
  • URL
  • Mailbox
  • MailCluster
  • MailMessage
  • SubmissionMail
• The identifiers MUST match the property names for the entityType as they appear in the following table (case sensitive):

entityType	Available identifiers
Account	Name, FullName, NTDomain, DnsDomain, UPNSuffix, Sid, AadTenantId, AadUserId, PUID, IsDomainJoined, DisplayName, ObjectGuid
Host	DnsDomain, NTDomain, HostName, FullName, NetBiosName, AzureID, OMSAgentID, OSFamily, OSVersion, IsDomainJoined
IP	Address
Malware	Name, Category
File	Directory, Name
Process	ProcessId, CommandLine, ElevationToken, CreationTimeUtc
CloudApplication	AppId, Name, InstanceName
DNS	DomainName
AzureResource	ResourceId
FileHash	Algorithm, Value
RegistryKey	Hive, Key
RegistryValue	Name, Value, ValueType
SecurityGroup	DistinguishedName, SID, ObjectGuid
URL	Url
Mailbox	MailboxPrimaryAddress, DisplayName, Upn, ExternalDirectoryObjectId, RiskLevel
MailCluster	NetworkMessageIds, CountByDeliveryStatus, CountByThreatType, CountByProtectionStatus, Threats, Query, QueryTime, MailCount, IsVolumeAnomaly, Source, ClusterSourceIdentifier, ClusterSourceType, ClusterQueryStartTime, ClusterQueryEndTime, ClusterGroup
MailMessage	Recipient, Urls, Threats, Sender, P1Sender, P1SenderDisplayName, P1SenderDomain, SenderIP, P2Sender, P2SenderDisplayName, P2SenderDomain, ReceivedDate, NetworkMessageId, InternetMessageId, Subject, BodyFingerprintBin1, BodyFingerprintBin2, BodyFingerprintBin3, BodyFingerprintBin4, BodyFingerprintBin5, AntispamDirection, DeliveryAction, DeliveryLocation, Language, ThreatDetectionMethods
SubmissionMail	NetworkMessageId, Timestamp, Recipient, Sender, SenderIp, Subject, ReportType, SubmissionId, SubmissionDate, Submitter

A list of all the entity types in Azure Sentinel and their identifiers can be found here. More info on entities in Azure Sentinel can be found here.

customDetails

Optional

Custom Details allow the template to surface event data in the alerts that are constructed from the events, making the event data part of the alert properties. In effect, this gives immediate event content visibility in the created security incidents, enabling triaging, investigating, drawing conclusions, and responding with much greater speed and efficiency.

Custom Details are defined as a key-value pairs of property name and column name. More info on Custom Details can be found here.

General format of customDetails

customDetails:
  customDetailsProperty1: columnName1
  customDetailsProperty2: columnName2
  ... # Up to 20 custom details

An example of customDetails definition

customDetails:
  Computers: Computer
  IPs: ComputerIP

Limitations and Rules

• Up to 20 custom details (i.e. key-value pairs) can be defined per template.

alertDetailsOverride

Optional

Alert Details allow analytic rules to have dynamic values for the Displayed name, Description, Tactics and Severity properties of the alert. By using dynamic alert details, the same rule can generate different incidents, for example with different severity. Also, the information displayed to the analyst can include variable information such as relevant entity names to help the analyst understand the incident faster.

Note the limit on size and number of placeholders for alertDisplayNameFormat and alertDescriptionFormat:

• There can be no more than 3 parameters included in a Name or Description.
• The Name length cannot exceed 256 characters, and the Description cannot exceed 5000.
• The column name that appears in the curly braces must match the expected column name exactly (without leading or trailing whitespace). For example, {{columnName}} and not {{ columnName }}.

The schema for Dynamic Alert Details:

alertDetailsOverride:
  alertDisplayNameFormat: free text with field names embedded using the format {{columnName}} # Up to 256 chars and 3 placeholders
  alertDescriptionFormat: free text with field names embedded using the format  {{columnName}} # Up to 5000 chars and 3 placeholders
  alertTacticsColumnName: dynamicTacticColumnName
  alertSeverityColumnName: dynamicSeverityColumnName

An example of Dynamic Alert Details:

alertDetailsOverride:
  alertDisplayNameFormat: rule {{columnName1}} display name
  alertDescriptionFormat: rule {{columnName2}} display name
  alertTacticsColumnName: dynamicTactic
  alertSeverityColumnName: dynamicSeverity

where columnName1, columnName2, dynamicTactic, and dynamicSeverity are output fields of the scheduled alert query.

Version

Required for Detections only

This is the version of this template. When customer creates a rule based on a template, sentinel saves the version of the template that this rule was created from.
Then, if a new version of the template is being published, customers will be notified in the UX that a new version is available.
The version is in the format a.b.c, when a is the major version, b is the minor version, and c is a patch. The version field should be the last line of the template.

Full Examples

https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft%20Entra%20ID/Analytic%20Rules/FailedLogonToAzurePortal.yaml

https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Malware%20Protection%20Essentials/Hunting%20Queries/FileCretaedInStartupFolder.yaml

---