This post is updated constantly with queries from the field about the Microsoft 365 Defender and other security controls (ITDR, CASB, EDR, CSPM, CNAPP, etc.). Some of the queries are standard, and others are more advanced. Additionally, I’m breaking down the queries into building blocks and explanations.

Because the security controls don’t know how to identify many types of attacks, we need to create queries to fill the gaps.

The following hunting queries are helpful for Microsoft Sentinel and Advanced Hunting.

Azure Storage – Identify Anonymous access to a Blob Container

This query is useful for identifying successful anonymous requests to blob storage in Azure. It can monitor access to your blob storage and detect any suspicious activity or potential security issues.

StorageBlobLogs
| where TimeGenerated > ago(15d)
| where ServiceType == "blob"
and Type == "StorageBlobLogs"
and AuthenticationType == "Anonymous"
and StatusCode == "200"
  • StorageBlobLogs is a table in Azure Monitor that stores log data related to operations on blobs in Azure Blob Storage.
  • ServiceType is a property of log data that indicates the type of service that generated the log entry. In this case, it filters log entries where the ServiceType is “blob”, meaning they are related to blob storage.
  • Type is a property of log data that indicates the type of log entry. In this case, it is filtering for log entries where the Type is “StorageBlobLogs”, which means they are related to operations on blobs in Azure Blob Storage.
  • AuthenticationType is a log data property that indicates the authentication type used for the operation that generated the log entry. In this case, it filters log entries where the AuthenticationType is “Anonymous”, meaning the operation was performed without authentication.
  • StatusCode is a property of log data that indicates the status code returned by the operation that generated the log entry. In this case, it is filtering for log entries where the StatusCode is “200”, which means the operation was successful.
  • summarize is a keyword that summarizes the filtered log data by counting the number of log entries that meet the specified criteria.

Defender for Office – Email count by NetworkMessageId

This query identifies emails sent with URLs that were subsequently blocked by a user.

let MSG =
UrlClickEvents
| summarize dcnt=dcount(NetworkMessageId) by NetworkMessageId 
| project-away dcnt;
search in (UrlClickEvents,EmailUrlInfo) NetworkMessageId in (MSG) 
| join EmailEvents on NetworkMessageId
| project TimeGenerated,IsClickedThrough, Connectors, EmailLanguage, SenderFromAddress
//| where IsClickedThrough == false
//| where UserLevelAction == "Block"
//| summarize count()by Url
  • MsgID = creates a variable called MsgID and assigns the result of the following query.
  • UrlClickEvents is the name of a table in the data source.
  • | where TimeGenerated > ago(24h) filters the results to only include records where the TimeGenerated field is within the last 24 hours.
  • | summarize dcnt=dcount(NetworkMessageId) by NetworkMessageId groups the results by the NetworkMessageId field and counts the number of unique NetworkMessageId values in the dcnt column.
  • | project-away dcnt removes the dcnt column from the results.
  • search in (UrlClickEvents,EmailUrlInfo) NetworkMessageId in (MsgID) searches for records in both the UrlClickEvents and EmailUrlInfo tables where the NetworkMessageId field matches any of the values in the MsgID variable.
  • | join EmailEvents on NetworkMessageId joins the results with the EmailEvents table on the NetworkMessageId field.
  • | where UserLevelAction == "Block" filters the results to only include records where the UserLevelAction field equals “Block”.

Defender for Office – URL clicked that contains an unusual port

This query filters UrlClickEvents where the ActionType is “ClickAllowed”, and extends the table to include the number of redirects made by the URL clicked, as well as a parsed version of the URL. It then filters out URLs with empty or standard HTTPS ports and those with more than one redirect.

UrlClickEvents
| where ActionType == "ClickAllowed"
| extend Redirects = (array_length(todynamic(UrlChain))) - 1
| extend ParsedUrl = parse_url(tostring(Url))
| where ParsedUrl.Port !in ("", "443")
| where Redirects == 1
| project TimeGenerated, AccountUpn, NetworkMessageId, Url, UrlChain
  • UrlClickEvents: This is the name of the table that the query will operate on. Presumably, this table contains data about URLs that users have clicked on.
  • | where ActionType == "ClickAllowed": This line filters the table to only include rows where the ActionType column equals “ClickAllowed”. This suggests the table may contain other actions besides clicking on a URL.
  • | extend Redirects = (array_length(todynamic(UrlChain))) - 1: This line creates a new column called “Redirects” and populates it with the number of redirects that occurred when the URL was clicked. It does this by converting the UrlChain column (which presumably contains a list of URLs that the original URL was redirected to) to a dynamic array, getting the length of that array, and subtracting 1 (since the original URL is not technically a redirect).
  • | extend ParsedUrl = parse_url(tostring(Url)): This line creates another new column called “ParsedUrl” and populates it with a parsed version of the original URL. The parse_url() function takes a string argument (in this case, the Url column) and returns a dynamic object containing various URL components.
  • | where ParsedUrl.Port !in ("", "443"): This line filters out any rows where the URL’s port number is either empty or equal to 443. This suggests that the query only interests URLs that use non-standard ports.
  •  where Redirects == 1: This line further filters the table to only include rows where the number of redirects is exactly 1. This suggests that the query only interests URLs that were redirected once.

Defender for Identity – Ldap search

(Account Discovery/Permission Groups Discovery/Remote System Discovery, etc. )

This query retrieves LDAP search events from the IdentityQueryEvents table that occurred within the last day, where the Query field contains the phrase “LDAP Search.” The DeviceName, AdditionalFields, Application, QueryTarget, and Query fields are projected, and AdditionalFields are parsed as JSON to extract its components. The AttributeList, ScopeOfSearch, SearchFilter, and DistinguishedName components are then extended as separate columns.

IdentityQueryEvents
| where Timestamp >= ago(1d)
| where Query contains "LDAP Search"
| project DeviceName, AdditionalFields, Application, QueryTarget, Query
| extend ldap = parse_json(AdditionalFields)
| extend AttributeList = ldap.AttributeList
| extend ScopeOfSearch = ldap.ScopeOfSearch
| extend SearchFilter = ldap.SearchFilter
| extend DistinguishName = ldap.DistinguishedName
//| where SearchFilter !contains "objectclass=*"
| summarize LdapActions=count() by Query, DeviceName, QueryTarget
  • IdentityQueryEvents: Specifies the table we are querying.
  • | where Timestamp >= ago(1d): Filters the results to only include events within the last day.
  • | where Query contains "LDAP Search": Further filters the results to only include events where the Query field contains the phrase “LDAP Search”.
  • | project DeviceName, AdditionalFields, Application, QueryTarget, Query: Projects in the fields we are interested in.
  • | extend ldap = parse_json(AdditionalFields): Converts the AdditionalFields column from a string to a JSON object, and then extends a new column called ldap with the resulting JSON object.
  • | extend AttributeList = ldap.AttributeList: Extends a new column called AttributeList with the value of the AttributeList component of the ldap JSON object.
  • | extend ScopeOfSearch = ldap.ScopeOfSearch: Extends a new column called ScopeOfSearch with the value of the ScopeOfSearch component of the ldap JSON object.
  • | extend SearchFilter = ldap.SearchFilter: Extends a new column called SearchFilter with the value of the SearchFilter component of the ldap JSON object.
  • | extend DistinguishName = ldap.DistinguishedName: Extends a new column called DistinguishName with the value of the DistinguishedName component of the ldap JSON object.
  • //| where SearchFilter !contains "objectclass=*": This line is commented out, but it includes a filter that would exclude search filters that contain the phrase “objectclass=*”.
  • | summarize LdapActions=count() by Query, DeviceName, QueryTarget: Summarizes the results by grouping them by Query, DeviceName, and QueryTarget, and counting the number of LDAP actions for each group. The resulting column is named LdapActions.
  • The commented line that starts with “//” indicates that a filter was previously applied to exclude search filters that contain “objectclass=*” but it has been commented out and is not currently being applied.

Defender for Identity – Potential Brute-force attack

This query retrieves logon events from the past 24 hours in Active Directory and summarizes them by ActionType, AccountName, FailureReason, and DestinationDeviceName. The commented line at the end filters the results to only include events where the BFCount (the count of failed logon attempts) is greater than or equal to 25.

IdentityLogonEvents
| where Timestamp >= ago(1d)
| where Application == @"Active Directory"
| summarize BFCount=count() by ActionType, AccountName, FailureReason, DestinationDeviceName //| where BFCount >= 25

If you want to see all logon events without filters based on the BFCount, you can remove the commented out line at the end. If you want to adjust the time range to look for events, you can modify the “ago(1d)” expression to another time range (e.g., ago(4h) for the past 4 hours).

This summarizes the events based on several attributes:

  • BFCount: This is the count of failed logon attempts.
  • ActionType: This is the type of logon action (e.g., Logon, Logoff, Unlock).
  • AccountName: This is the account name used to perform the logon action.
  • FailureReason: This is the reason for the logon failure (e.g. bad password, locked account).
  • DestinationDeviceName: This is the name of the device on which the logon action was attempted.

Defender for Identity – AD Recon

The query retrieves information about alerts and alert evidence related to Microsoft Defender for Identity. The TimeRange variable is set to one day. The query first filters the AlertInfo table to include only alerts that have occurred within the last day and have a DetectionSource of “Microsoft Defender for Identity.”

let TimeRange = 1d;
AlertInfo
| where Timestamp > ago(TimeRange)
| where DetectionSource == "Microsoft Defender for Identity"
| join (AlertEvidence
| where Timestamp > ago(TimeRange)
| where EntityType == "Machine"
and EvidenceDirection == "Source"
and ServiceSource == "Microsoft Defender for Identity"
) on AlertId
| summarize by Timestamp, DeviceName, AttackTechniques, Category, Severity, EvidenceRole
  • The first line defines a variable TimeRange and sets it to a duration of one day. The d at the end indicates that this duration is in days.
  • AlertInfo is a table containing information about alerts generated by the system.
  • The first where statement filters AlertInfo to include only alerts that have occurred within the last day.
  • The second where statement further filters the results to include only alerts generated by “Microsoft Defender for Identity”.
  • The join statement combines AlertInfo with AlertEvidence on the AlertId field. AlertEvidence is another table containing information about the evidence collected during an alert.
  • The first where statement within the join filters AlertEvidence to include only evidence that has occurred within the last day.
  • The second where statement within the join further filters the results to include only evidence related to machines, with an evidence direction of “Source”, and a service source of “Microsoft Defender for Identity”.
  • The summarize statement groups the results by Timestamp (the time the alert occurred), DeviceName, AttackTechniques, Category, Severity, and EvidenceRole. The resulting table summarizes alerts and evidence related to Microsoft Defender for Identity within the last day, grouped by these fields.

Defender for Endpoint – Potential for WSL LOLBIN

This query you provided searches for events on the device’s event on certain conditions. Specifically, it filters events where the initiating process’s file name ends with “wsl.exe,” and the initiating process’s command line contains one of the following substrings: ” -e”, ” –exec”, ” –system”, or ” /mnt/c”.

DeviceProcessEvents
| where InitiatingProcessFileName endswith @'\wsl.exe'
 and InitiatingProcessCommandLine contains ' -e '
 or InitiatingProcessCommandLine contains ' --exec '
 or InitiatingProcessCommandLine contains ' --system '
 or InitiatingProcessCommandLine contains ' /mnt/c'
  • where filters data in a table based on a specified condition.
  • InitiatingProcessFileName is a field in the DeviceProcessEvents table that contains the name of the process that initiated the event.
  • endswith is a string operator used to check if the value of a field ends with a specified string. In this case, it checks if the field’s value ends with “wsl.exe”.
  • @'\wsl.exe' is a regular expression used to match the string “wsl.exe” at the end of the InitiatingProcessFileName field value? The @ symbol indicates that the regular expression is a string literal.
  • InitiatingProcessCommandLine is a field in the DeviceProcessEvents table that contains the command-line arguments used to start the process.
  • contains is a string operator used to check if a string is contained within another string. In this case, it is checking if the InitiatingProcessCommandLine field contains any of the following strings: ” -e “, ” –exec “, ” –system “, or ” /mnt/c”.
  • The or operator is used to join multiple conditions in the where clause, indicating that anyone can be true for a row to be included in the result.

The query selects the DeviceProcessEvents table and filters it to include only events where the process name ends with “wsl.exe” and the command arguments include one of the specified strings. This suggests that the query is looking for instances of WSL being used to run Linux commands or access files on the C drive in Windows.

Tip: Additionally, the query can contain the following command lines:

:  - ' -e '  - ' --exec'  - ' --system'  - ' --shell-type '  - ' /mnt/c' # Path to mounted "C:\" partition (Indication of running Windows binaries via WSL)  - ' --user root'  - ' -u root

Security Event – Suspicious Whoami Execution

Detects the execution of “whoami.exe” with the “/all” flag or redirection options to export the results to a file for later use.

This query search for events that might indicate suspicious activity related to the execution of the “whoami.exe” utility, which is a standard Windows command-line tool used to display system information and user privileges. It looks for specific parameters that might indicate that the tool is being used for malicious purposes or to collect information about the system.

SecurityEvent 
| where EventID == 1 
| where (((NewProcessName endswith @'\whoami.exe' 
or OriginalFileName =~ @'whoami.exe') 
and (CommandLine contains @' -all' 
or CommandLine contains @' /all' 
or CommandLine contains @' /FO CSV' 
or CommandLine contains @' -FO CSV')) 
or (CommandLine contains @'whoami' 
and CommandLine contains @'>'))
  • SecurityEvent: This is the name of the table or data source being queried. It likely contains data about security events logged by the operating system or some security software.
  • where EventID == 1: This command filters the data to only include events where the EventID field equals 1. This is likely a specific type of security event that the query author is interested in.
  • where (((NewProcessName endswith @'\whoami.exe' or OriginalFileName =~ @'whoami.exe') and (CommandLine contains @' -all' or CommandLine contains @' /all' or CommandLine contains @' /FO CSV' or CommandLine contains @' -FO CSV')) or (CommandLine contains @'whoami' and CommandLine contains @'>')): This command applies additional filters to the data. It uses nested boolean logic to check for two different conditions:
    • The first condition is enclosed in parentheses and checks for new processes with the name “whoami.exe” (using the NewProcessName field) or existing processes with the original file name “whoami.exe” (using the OriginalFileName field). The endswith and =~ operators are used for exact and partial matching, respectively. If either of these conditions are true, the command line is further checked for specific parameters (-all, /all, /FO CSV, or -FO CSV) using the contains operator.
    • The second condition is enclosed in parentheses and checks for command lines that contain the string “whoami” and also contain the “>” symbol, which indicates that the output is being redirected to a file.

Security Event – Suspicious with Double Extension File

Detect execution of suspicious double extension files in ParentCommandLine

This query search for security events where the parent process name or command line contains certain file extensions commonly associated with document, spreadsheet, presentation, and text files, as well as JavaScript files, but will use CommandLine instead of ParentCommandLine to identify the command line of the process.

SecurityEvent
| where ((ParentProcessName endswith @'.doc.lnk' or ParentProcessName endswith @'.docx.lnk' or ParentProcessName endswith @'.xls.lnk' or ParentProcessName endswith @'.xlsx.lnk' or ParentProcessName endswith @'.ppt.lnk' or ParentProcessName endswith @'.pptx.lnk' or ParentProcessName endswith @'.rtf.lnk' or ParentProcessName endswith @'.pdf.lnk' or ParentProcessName endswith @'.txt.lnk' or ParentProcessName endswith @'.doc.js' or ParentProcessName endswith @'.docx.js' or ParentProcessName endswith @'.xls.js' or ParentProcessName endswith @'.xlsx.js' or ParentProcessName endswith @'.ppt.js' or ParentProcessName endswith @'.pptx.js' or ParentProcessName endswith @'.rtf.js' or ParentProcessName endswith @'.pdf.js' or ParentProcessName endswith @'.txt.js') or (CommandLine contains @'.doc.lnk' or CommandLine contains @'.docx.lnk' or CommandLine contains @'.xls.lnk' or CommandLine contains @'.xlsx.lnk' or CommandLine contains @'.ppt.lnk' or CommandLine contains @'.pptx.lnk' or CommandLine contains @'.rtf.lnk' or CommandLine contains @'.pdf.lnk' or CommandLine contains @'.txt.lnk' or CommandLine contains @'.doc.js' or CommandLine contains @'.docx.js' or CommandLine contains @'.xls.js' or CommandLine contains @'.xlsx.js' or CommandLine contains @'.ppt.js' or CommandLine contains @'.pptx.js' or CommandLine contains @'.rtf.js' or CommandLine contains @'.pdf.js' or CommandLine contains @'.txt.js'))
  • SecurityEvent: This specifies the data source that the query is going to search. In this case, it’s assumed to be a table containing security event data such as logon events, process creation events, and other security-related events.
  • where: This operator filters the query results based on a specified condition. In this query, the where operator is filtering events that match specific criteria.
  • The or operator is used to combining two sets of criteria:a. ParentProcessName endswith: This string operator returns true if the parent process name ends with a specified value. In this query, it’s checking if the parent process name ends with any of the specified file extensions, such as .docx.lnk or .pdf.js.b. CommandLine contains: This string operator returns true if the command line contains a specified value. It checks if the command line contains any of the specified file extensions.
  • The @ symbol before the file extensions indicates that the string should be treated as a literal value rather than a regular expression. This can be useful to avoid false positives or unexpected results.

Overall, this query searches for security events where the parent process name or command line contains specific file extensions commonly associated with document, spreadsheet, presentation, and text files, as well as JavaScript files. Looking for these data patterns can help identify potential threats or suspicious activity related to using these file types.

More queries and tips on Twitter and the Advanced Hunting 4 Hero’s.

One thought on “Practical Hunting from the field”

  1. Pingback: לחפש פוליסי בגישה מותנית (פוליסי CAP) - Cyber Threat Hunting

Leave a Reply