Contribute to the Community of Queries

Have a great idea for a hunting, investigation, detection query that can be shared with the Sentinel Community? Want that to be brought directly into the Microsoft Sentinel Service and Microsoft 365 Defender for direct customer use?

Well, let’s get started.

First, what’s your idea and is there a similar query already in place.

• Check the current GitHub repo to see what’s there.
• Review Current GitHub queries and parsers.

Next,

• Is your idea new? Great add it in!
• Is your idea an improvement of an existing query? Amazing, submit changes!
• Is your idea similar, but slightly different? Excellent, copy an existing query and change it to meet your needs.
• Point is, the community is flexible, it can be simple queries to complex queries to custom data set queries. We want all security experts to contribute their knowledge and experience from investigations and hunting done on-prem and in the cloud.

Now onto the How

Create a query you want to share using these guidelines:

• Query header format is in YAML.
• Use the Query Style Guide to understand the specifics of fields in the YAML header.
• The Query section is limited to 10,000 characters, see further details in Query Style Guide.
• For Detections and Hunting Queries, be sure to include the new entityMappings section at the bottom otherwise DetectionTemplateStructureValidationTests will fail. Currently, this is limited to 5 separate entityTypes.

Example YAML schema and format from a Detection query:

id: 80da0a8f-cfe1-4cd0-a895-8bc1771a720e
name: Security Event log cleared
description: |
  '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.'
severity: Medium
requiredDataConnectors:
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - DefenseEvasion
relevantTechniques:
  - T1107
query: |
  SecurityEvent
  | where EventID == 1102 and EventSourceName == "Microsoft-Windows-Eventlog" 
  | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), EventCount = count() by Computer, Account, EventID, Activity
  | extend timestamp = StartTimeUtc, AccountCustomEntity = Account, HostCustomEntity = Computer
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: HostCustomEntity
version: 1.0.0

Example YAML schema and format from a Hunting query:

id: 9fd6f61d-2cc3-48de-acf5-7194e78d6ea1
name: Windows System Time changed on hosts
description: |
  'Identifies when the system time was changed on a Windows host which can indicate potential timestomping activities.
  Reference: Event ID 4616 is only available when the full event collection is enabled - https://docs.microsoft.com/azure/sentinel/connect-windows-security-events'
requiredDataConnectors:
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
tactics:
  - DefenseEvasion
relevantTechniques:
  - T1070
query: |
  SecurityEvent
  | where EventID == 4616
  | where not(ProcessName has_any (":\\Windows\\System32\\svchost.exe", ":\\Program Files\\VMware\\VMware Tools\\vmtoolsd.exe"))
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by Computer, EventID, Activity, Account, AccountType, NewTime, PreviousTime, ProcessName, ProcessId, SubjectAccount, SubjectUserSid, SourceComputerId, _ResourceId
  | extend timestamp = StartTime, HostCustomEntity = Computer, AccountCustomEntity = SubjectAccount
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: HostCustomEntity

Example parser

Example of what a parser should look like take from the PulseConnectSecure parser

Note: This is a TXT file not a YAML file and should be in its own folder in the Parsers folder

// Title:           Pulse Connect Secure Data Parser
// Author:          Microsoft
// Version:         1.1
// Last Updated:    06/16/2020
// Comment:         Added support for version 8.0R7 and above
//  
// DESCRIPTION:
// This parser takes raw Pulse Connect Secure logs from a Syslog data stream and parses the data into a normalized schema
//
// USAGE:
// 1. Open Log Analytics/Azure Sentinel Logs blade. Copy the query below and paste into the Logs query window. 
// 2. In the query window, on the second line of the query, enter the hostname(s) of your Pulse Connect Secure device(s) and any other unique identifiers for the logstream. 
//    For example: | where Computer in ("server1, server2") and Facility == "local7"
// 3. Click the Save button above the query. A pane will appear on the right, select "as Function" from the drop down. Enter a Function Name.
//    In order for the Pulse Connect Secure logs to work with pre-built queries and workbooks the Function Alias must be set to - PulseConnectSecure
// 4. Function App usually take 10-15 minutes to activate. You can then use Function Alias for other queries
//
//
// REFERENCE: 
// Using functions in Azure monitor log queries: https://docs.microsoft.com/azure/azure-monitor/log-query/functions
// Pulse Connect Secure Event Log Format: https://docs.pulsesecure.net/WebHelp/Content/PCS/PCS_AdminGuide_8.2/Displaying%20Events%20Logs.htm
//
// LOG SAMPLES:
// This parser assumes the raw log are formatted as follows:
// 
//      2020-05-01 05:36:14 - ive - [10.0.0.0] user100(ABC Realm)[Personal_PC No RDP] - WebRequest ok : Host: sample.abc.com, Request: GET /Citrix/XDSWeb/dample/js/ctxs.webui.min_0204820BD028.js HTTP/1.1
//      2020-05-01 00:07:21 - ive - [127.0.0.1] System()[] - User Accounts modified. Removed username ABC\user34 from authentication server ABC Active Directory.
//
//
Syslog
| where Computer in ("datasource") and Facility == "local7"
// Version 8.0R7 and below using the Standard format
| extend Parser = extract_all(@'^(\d{4}\-\d{2}-\d{2})\s(\d{2}\:\d{2}:\d{2})\s(\S+)\s(\S+)\s(\S+)\s\[(\S+)\]\s(\S+)\((.*)?\)\[(.*)\]\s\-\s(.*)',dynamic([1,2,3,4,5,6,7,8,9,10]),SyslogMessage)
| mv-expand Parser
| extend LogTime = todatetime(strcat(tostring(Parser[0]),'T',tostring(Parser[1]))), 
    Node = tostring(Parser[3]), 
    Source_IP = tostring(Parser[5]), 
    User = tostring(Parser[6]), 
    Realm = tostring(Parser[7]), 
    EventID = tostring(Parser[8]), 
    Messages = tostring(Parser[9])
| project-away Parser
// The section below is for parsing WebTrends Enhanced Log Format (WELF) logs.  If you are NOT using WELF, then keep this section commented out otherwise uncomment each line to use
// Version 8.0R7 and above using the WELF format
//| extend User = extract(@'user=(\S+)',1,SyslogMessage),
//    EventID = extract(@'id=(\S+)',1,SyslogMessage),
//    Pri = extract(@'pri=(\S+)',1,SyslogMessage),
//    Node = extract(@'vpn=\"(\S+)\"',1,SyslogMessage),
//    Realm = extract(@'realm=\"([\w\s\:\.]+)\"',1,SyslogMessage),
//    Roles = extract(@'roles=\"([\w\s\:\.]+)\"',1,SyslogMessage),
//    Type = extract(@'type=(\S+)',1,SyslogMessage),
//    Messages = extract(@'msg=\"([\w\s\:\.]+)\"',1,SyslogMessage),
//    Source_IP = extract(@'fw=([\d\.]+)',1,SyslogMessage)

Add in your new or updated query to GitHub

To submit a new or updated query or parser via these methods:

• Submit for review directly on GitHub website
  • Browse to the folder you want to upload your file to
  • Choose Upload Files and browse to your YAML file.
  • You will be required to create your own branch and then submit the Pull Request for review.
• Use GitHub Desktop or Visual Studio or VSCode
  • Clone the repo
  • Create your own branch
  • Do your additions/updates in GitHub Desktop
  • Push your changes to GitHub

Pull Request

• After you push your changes, you will need to submit the PR
• After submission, check the Pull Request for comments
• Make changes as suggested and update your branch or explain why no change is needed. Resolve the comment when done.
• If your Pull Request does not get approved within 7 days, please post to the Issues section.
• For detections - once your pull request is completed, after at most 2 weeks, your template will be available in Azure Sentinel template gallery.

Validation failures

If your PR has validation failures, the most common are referenced in the code landing page Readme.md