Query Style Guide

Goal

This document aims to create a uniform style for Sentinel 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 Azure Sentinel UX with the requirements below as needed.

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 explained below
• "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.
• 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

Indicates the confidence level of the detections. Note that you can use alertDetailsOverride to provide a dynamic severity that will depend on the actual outcome of the query.

• Low – Could potentially cause noise or is a detection that would need additional detections to raise the overall severity.
• Medium – These are generally quiet but may require some additional investigation to verify the impact of the attack.
• High – These are high confidence, rare detections that are generally guaranteed to indicate compromise or a high level impact of an attack.

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.

• The name MUST NOT have any spaces
• Example – InitialAccess or LateralMovement

relevantTechniques

Required for Detections and Hunting Queries

Relevant MITRE Techniques ID

• MUST match MITRE Tactics
• Example: T1100 or T1120

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

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.

We now have an old and a new entity mapping method. During the transition phase, you must still include both.

Old Entity mapping method

Still Required

Only one entity of each type can be extracted (per result entry). The extraction is based on the following:

• timestamp
  • This is generally TimeGenerated
• AccountCustomEntity
  • This is generally the Account, AccountName, UserPrincipalName, UserId property depending on the datatype
  • When those are not available, you can substitute SID, AADUserId or other strong identifiers
  • Extracted field is mapped to Account.Name in the standardized Alert Schema
• HostCustomEntity
  • This is generally the Host, Computer, System, Device property depending on the datatype
  • Extracted field is mapped to Host.HostName in the standardized Alert Schema
• IPCustomEntity
  • This is generally the IP, IPAddress, ClientIP, RemoteIP, DestinationIP property depending on the datatype
  • Extracted field is mapped as IP.Address
• URLCustomEntity
  • This is generally the URL, URLClicked, RequestURL, Site_Url, csUriQuery property depending on the datatype

At the end of your query, use the following syntax to map the entities for easy Rule creation, example:

For non-Summarized - | extend timestamp = TimeGenerated, HostCustomEntity = Computer, AccountCustomEntity = UserPrincipalName, IPCustomEntity = RemoteIP, URLCustomEntity = URL

For summarized - | extend timestamp = StartTime, HostCustomEntity = Computer, AccountCustomEntity = UserPrincipalName, IPCustomEntity = RemoteIP, URLCustomEntity = URL

New Entity mapping method

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

The schema for Dynamic Alert Details:

alertDetailsOverride:
  alertDisplayNameFormat: free text with field names embedded using the format {{columnName}} # Up to 256 chars
  alertDescriptionFormat: free text with field names embedded using the format  {{columnName}} # Up to 5000 chars
  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 feature is currently being implemented

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.

Full Examples

https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SigninLogs/FailedLogonToAzurePortal.yaml

https://github.com/Azure/Azure-Sentinel/blob/master/Hunting%20Queries/SecurityEvent/ProcessEntropy.yaml

---