-
Notifications
You must be signed in to change notification settings - Fork 3k
Query Style Guide
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.
File format is YAML and should be validated with any YAML syntax validator, there are many online.
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.
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
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 |
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. |
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.
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. |
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.
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) |
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
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.
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
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.
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
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
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;
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
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.
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
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.
entityMappings:
- entityType: <EntityType>
fieldMappings:
- identifier: <V3PropertyName>
columnName: <ColumnName>
- identifier: ... # Up to 3 identifiers
- entityType: ... # Up to 10 entity mappings
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
- 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.
Optional |
Custom Details are defined as a key-value pairs of property name and column name. More info on Custom Details can be found here.
customDetails:
customDetailsProperty1: columnName1
customDetailsProperty2: columnName2
... # Up to 20 custom details
customDetails:
Computers: Computer
IPs: ComputerIP
- Up to 20 custom details (i.e. key-value pairs) can be defined per template.
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.
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.
- Ingest Custom Logs via REST API