From Threat Report to (KQL) Hunting Query

Threat intelligence reports are an essential source to be able to identify and mitigate security threats. However, the process of converting the information in these reports into actionable queries (such as Kusto Query Language (KQL)) can be challenging. In this blog post, we will explore the steps involved in going from a threat intelligence report to a KQL hunting query. This is done based on two #StopRansomware reports of the joint Cybersecurity Advisory (CSA).

#StopRansomware This joint Cybersecurity Advisory (CSA) is part of an ongoing #StopRansomware effort to publish advisories for network defenders that detail various ransomware variants and ransomware threat actors. These #StopRansomware advisories include recently and historically observed tactics, techniques, and procedures (TTPs) and indicators of compromise (IOCs) to help organizations protect against ransomware. Visit to see all #StopRansomware advisories and to learn more about other ransomware threats and no-cost resources.

The blog is divided into the following sections:

Interested in KQL? Subscribe to the monthly Kusto Insights newsletter!


Schematic Process

The schematic overview of the process from reading a Threat Report to evaluating the results of a Hunting Query can be seen below.

Schematic Process from Threat Report to hunting results

Read Threat ReportThe first phase is to read the report.
Gather Actionable InformationThis stage focuses on the indicators that are mentioned in the report, only select the ones applicable to your environment and sector. The output of this step is a list with actionable indicators.
Match Information with available dataBased on the previous step we have a list with actionable indicators, but the value of those indicators depends on the data that we have available. This phase matches the information from the indicators to the data tables you have in your security solutions.
Wrint Hunting queryThis is of course the most fun! ;), hitting the keyboard and searching in your data for any sign of the indicators.
Evaluate ResultsOnce your awesome query is built you might see some results, the last phase is to evaluate them to determine if this is indeed what you wanted to search for. If not you adjust the hunting query.
Incident ResponseOnce you have identified malicious activity you have to act upon it to contain the risk.

Reading The Report

The first step is to read the report and derive information from it, only if actionable information is collected we can start writing hunting queries. The report will contain a lot of information, but depending on your technology stack and threat landscape not all information might be relevant.

The next step is to collect relevant Indicators of Compromise (IOC), those IOCs can be put into different categories:

  1. Atomic IOCs, such as domains and IP addresses, that can detect adversary infrastructure and tools
  2. Computed IOCs, such as Yara rules and regular expressions, that detect known malicious artefacts or signs of activity
  3. Patterns and behaviours, such as analytics that detect adversary tactics, techniques, and procedures (TTPs)

Source: CISA Cybersecurity Incident & Vulnerability Response Playbooks

Those different Indicators of Compromise are related to the CTI Pyramid of Pain. The closer the indicator type is to the base of the pyramid, the easier it is for adversaries to change. The other side is that the more closely an indicator type aligns with the base of the pyramid, the simpler it is to gather information about it.

When moving to the top of the pyramid it becomes harder for adversaries to change the indicator, the other side of this is that intel about those also become more scarce.

Pyramid of Pain by David Bianco

The ransomware reports contain all of the three indicator categories, however, within the remainder of this blog the focus is on the Atomic and Behavioural indicators.

Knowing Your Data

The next phase is to map the indicators to the data you have available. Understanding the specific locations of data within your organization’s security solutions and tables is incredibly important for threat hunting. This step is crucial to be able to get valuable results. This comes down to knowing what data tables you have available and knowing what indicators can be used in which tables.

To list some examples based on tables in Defender For XDR:

DeviceNetworkEventsIP, DNS, URL
EmailEventsDNS, Emailaddresses
DeviceFileEventsFileNames, Hashes, Tools
DeviceProcessEventsCommands, Tools

List Actions Per Table The ListAllActionsAndOperations() KQL function lists all Actions and Operations at from all tables at ones, which returns a good overview of all the different types of events you have in your tables.

Hunting Atomic IOCs

The image below shows some of the IP indicators that have been mentioned, but this image needs to be translated to a list which can be used as input for a query. This can be done via regex, but also AI tooling can help by simply asking to extract all IPv4 addresses from this file and putting them in an array.

Hive Ransomware IP Indicators


The array is the starting point for a hunting query. Since the Potential IOC IP Addresses for compromise or exfiltration are related to network events, the DeviceNetworkEvents table is the best one to search for matches, resulting in the query below. The query uses the in() operator, only if a RemoteIP is equal to one of the IPs from the IPList results will be returned. If those IPs would have been related to sources from where adversaries sign in to your cloud applications the AADSignInEventsBeta in MDE or the SigninLogs in Sentinel could be leveraged to hunt for those sign-ins.

let IPList = dynamic(["", "", "", "", 
"", "", "", "", "", 
"", "", "", "", "", 
"", "", "", "", "", ""]);
| where RemoteIP in (IPList)


In the reports, four domains can be found, even though they are not the strongest indicators they could still be useful depending on your available data. If you do not have any process-based events it could be useful to leverage the firewall logs to hunt for sources that connect to those domains. In the case of this blog, we will again focus on the DeviceNetworkEvents. The query is slightly different than the one for IPs, in this case, the has_any() operator is used, this function is in essence a contains based on a input list.

let Domains = dynamic(["", "", "", ""]);
| where RemoteUrl has_any (Domains)
Alert & Block on IOCs
In Defender For Endpoint, one can add indicators to alert and/or block when one of those is found in your tenant. Currently, the following IOC types are supported: Hashes, IPs, URLs, Domains and certificates. Documentation: Create indicators

Hunting Patterns and Behaviors

Moving on to the patterns and behaviours that the Ransomware operators used, we also need to increase the complexity of the queries that we use to detect those behaviours. Just searching if the data contains some value in some field is not enough anymore.

File Based Behaviors

Starting with the first example of behaviours that are mentioned in the reports is storing a *.key file in the root directory.

*.key typically in the root directory, i.e., C:\ or /root

This textual indicator can be translated to a KQL query as seen below. The DeviceFileEvents table is the best one to find files that have been created in a particular location, if the file had to be executed the DeviceProcessEvents would have been your best choice. First, we filter most of the events by only selecting filenames that end with .key. This filter is made first because it will improve the speed of the query. parse_path will longer, thus with the filtered result only a subset of the rows needs to be parsed to collect the RootPath and DirectoryPath. The rest of the query filters only on file creations in the *C:* directory.

| where FileName endswith ".key"
| extend FolderDetails = parse_json(parse_path(FolderPath))
| extend RootPath = tostring(FolderDetails.RootPath), 
    DirectoryPath = tostring(FolderDetails.DirectoryPath)
| where RootPath == @"C:\" and DirectoryPath == "C:"
| project-reorder Timestamp, FolderPath, PreviousFileName

*.key activities in root directory

Process Based Behaviors

Next up are the process-based behaviours, which are the most valuable of all the indicators mentioned in the threat reports. This is also in some essence shown in the tables containing the IOCs, the fidelity column classifies all the commands as high. As could already be derived from the name of this behaviour we need to have logs that relate to processes and their creation, this can come in many forms, such as DeviceProcessEvents, SecurityEvents or Syslog. The deriviced commandline executions are listed below.

wevtutil.exe cl system
wevtutil.exe cl security
wevtutil.exe cl application
vssadmin.exe delete shadows /all /quiet
vssadmin.exe list shadows
wmic.exe SHADOWCOPY /nointeractive
wmic.exe shadowcopy delete
bcdedit.exe /set {default} bootstatuspolicy ignoreallfailures
bcdedit.exe /set {default} recoveryenabled no
rundll32 C:\Users\Public\adobelib.dll
cmd.exe /q /c taskkill /f /im sqlwriter.exe /im winmysqladmin.exe /im w3sqlmgr.exe /im sqlwb.exe 
    /im sqltob.exe /im sqlservr.exe /im sqlserver.exe /im sqlscan.exe /im sqlbrowser.exe 
    /im sqlrep.exe /im sqlmangr.exe /im sqlexp3.exe /im sqlexp2.exe /im sqlex
cmd.exe /q /c cd \ 1> \\\admin$\__1698618133.54 2>&1

To show the workings I have taken a subset of the commands mentioned above and put them in a dynamic list, but to improve the results I have not put the whole command in this list, only the variables. The separation between the variable and the file that initiates the command is done because the command line logs can differ for example “wevtutil.exe” cl system will not be matched if I had put all the full commands in the dynamic list. The second list is all the files that initiate the commands, they are logged in a separate field and can thereby be used as a filter, resulting in the query below.

let Commands = dynamic(['cl system','cl security', 'cl application', @'delete shadows /all /quiet', 
    'list shadows', @'shadowcopy /nointeractive', @'shadowcopy delete']);
let IntiatingFiles = dynamic(['wevtutil.exe', 'vssadmin.exe', 'wmic.exe', 'bcdedit.exe', 'rundll32.exe']);
| where ProcessCommandLine contains "wevtutil.exe"
| where FileName in~ (IntiatingFiles)
| extend ToLowerProcessCommandLine = tolower(ProcessCommandLine)
| project ProcessCommandLine, FileName, DeviceName

The commands that have been executed are quite specific therefore only focussing on the commandlines mentioned above is not enough. For example, if the ransomware operator wakes up one day and decides to run wevtutil.exe clean-log system instead of wevtutil.exe cl system the query above will not show any results. Therefore it is important to further dive into every technique/process that is executed. Let’s discuss how you can do this by looking at the wevtutil.exe and taskkill events.


Wevtutil is in this specific case used to clear event logs, this activity is also classified within MITRE ATT&CK as Indicator Removal: Clear Windows Event Logs (T1070.001) within the tactic Defense Evasion. Of course, there are more ways to clear the event logs but these threat actors have chosen this approach. A threat hunt to this particular technique can be very interesting ;)!

wevtutil.exe cl system
wevtutil.exe cl security
wevtutil.exe cl application

The first step is understanding how this specific technique is executed and how the program that initiates the action operates. The wevtutil documentation contains the following syntax which is relevant for this particular technique, removing indicators:

{cl | clear-log} <Logname>

This allows us to not only hunt the cl execution but more importantly also include clear-log. This results in the query mentioned below (Link for more context). This combines the information that is mentioned in the documentation, a command line must have wevtutil.exe and cl or clear-log.

| extend ProcessCommandLineToLower =  tolower(ProcessCommandLine)
| where ProcessCommandLineToLower has "wevtutil.exe" and ProcessCommandLineToLower has_any ("cl", "clear-log")
| project-reorder Timestamp, DeviceName, AccountSid, ProcessCommandLine, InitiatingProcessCommandLine 
Alerts alerts alerts

Even though it was not the goal of this blog to determine how well Defender For Endpoint was able to detect these ransomware activities, it is good to see that many alerts have been triggered. Only processes mentioned in the two #StopRansomware have been executed and that resulted in all these alerts.

Defender For Endpoint alerts


The last investigation for this blog is related to the taskkill commands that are mentioned in the report. This is a very specific list of executables that are killed. Note that all the tasks are related to some SQL service, this is something we can use while developing a query. But keep in mind that adversaries only need to change one parameter or leave one out and you will not detect anything if you try to detect the full command execution. To know exactly what taskkill can do we again take a look at the documentation to analyse the executed command, /f is a force parameter and /im specifies the name of the process to kill.

cmd.exe /q /c taskkill /f /im sqlwriter.exe /im winmysqladmin.exe /im w3sqlmgr.exe /im sqlwb.exe 
    /im sqltob.exe /im sqlservr.exe /im sqlserver.exe /im sqlscan.exe /im sqlbrowser.exe 
    /im sqlrep.exe /im sqlmangr.exe /im sqlexp3.exe /im sqlexp2.exe /im sqlex

To ensure that we will still detect this behaviour, even though an adversary might have changed the approach we start by defining which logs are needed. As already mentioned above process behaviours are related to commandline logs, thus we pick the DeviceProcessEvents table. Also note that in this case cmd.exe is used, but the same command without that part can be executed in PowerShell, be smarter than the adversary and be critical of what to include and what to exclude in your hunting query.

The query relies on two different variables as input, the TotalKilledThreshold variable determines how many different processes need to be killed, the example above contains 14 different processes that are killed, by altering this variable you can also detect subsets of the executed commandline. The TotalParametersThreshold variable determines how many parameters have to be used in the commandline.

let TotalKilledThreshold = 10;
let TotalParametersThreshold = 10;
| where FileName == "taskkill.exe"
| extend CommandLineParameters = split(ProcessCommandLine, " ")
| extend TotalParameters = array_length(CommandLineParameters)
// Extract allSQL related processes in the CommandLineParameters
| mv-apply KilledProcess = CommandLineParameters on (
    where KilledProcess contains "sql"
    | project KilledProcess
| summarize arg_max(Timestamp, *), AllKilledProcess = make_set(KilledProcess) by ReportId
| extend TotalKilledProcesses = array_length(AllKilledProcess)
| project-reorder Timestamp, ProcessCommandLine, TotalParameters, TotalKilledProcesses
| where TotalKilledProcesses >= TotalKilledThreshold and TotalParameters >= TotalParametersThreshold

Detecting Ransomware Threats

To strengthen your detection and/or hunting capabilities the KQL Repository contains queries that can help you develop those capabilities. This section will discuss some of those queries and what value they could bring to your environment.

KQL QueryDescriptionMITRE Technique
ASR Ransomware TriggerThis query detects when the ASR rule AsrRansomwareBlocked or AsrRansomwareAudited is triggered. MDE uses client and cloud heuristics to determine if a file resembles ransomware. This file could for example be the script that is used to encrypt files. No alert is generated by default by Defender For Endpoint, therefore it is recommended to create a custom detection rule to alert on this ASR trigger.T1486
Ransomware Note SearchThis query ingests SHA1 hashes of ransomware notes and searches in your environment if a match is found.
Shadow Copy DeletionThis query detects when a known ransomware command is used to delete shadow copies. A shadow copy is a backup or snapshot of a system, and often deleted by ransomware groups.T1490
Anomalous amount of SMB sessionsThis query is aimed to detect a host that performs SMB discovery by alerting if a device creates more then 100 (configurable) unique SMB sessions within 15 minutes. That is one of the characteristics of discovery/reconnaissance tools. The SMB sessions can be used to identify remote systems, which is often done to map the network.T1018

Happy hunting! 🏹

Questions? Feel free to reach out to me on any of my socials.