[Microsoft Defender] Query Exceeds Allowed Limits

[The-Stuke]

Description

When running the Microsoft Defender collector we receive an error for query limits exceeded. This most likely is happening due to a large environment of hosts and the original query in the collector does not attempt to limit the queries. I have played with the query a bit to help optimize it, but it probably needs more optimization or reworked to work so it does not fail due to hitting query execution limits in large environments.

Environment

1. OS (where OpenBAS server runs): Docker
2. OpenBAS version: 1.15.0
3. OpenBAS client: N/A
4. Other environment details:

Reproducible Steps

1. Run the original query

let null_if_not_implant_sig = (sig:string) { iff((sig startswith "obas-implant"), sig, ""); };
let normalisePath = (pathString:string) { tolower(trim_end("/", replace_string(pathString, "\\\\", "/"))); };
let augmentedDeviceEvents = DeviceEvents
    | where isnotnull(InitiatingProcessId) and InitiatingProcessId != 0
    | extend normalised_filename=normalisePath(FileName),
             normalised_folder_path=normalisePath(FolderPath),
             process_hash=hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime))
    | project DeviceId, normalised_filename, normalised_folder_path, process_hash;
let augmentedFileEvents = DeviceFileEvents
    | extend normalised_filename=normalisePath(FileName),
             normalised_folder_path=normalisePath(parse_path(FolderPath).DirectoryPath),
             process_hash=hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime))
    | project DeviceId, normalised_filename, normalised_folder_path, process_hash;
let augmentedAllFilesEvents = augmentedDeviceEvents
    | union augmentedFileEvents
    | distinct DeviceId, normalised_filename, normalised_folder_path, process_hash;
let singleMachinePerAlert = AlertEvidence
    | where EntityType has "Machine" and EvidenceRole has "Impacted"
    | distinct AlertId, DeviceId, DeviceName;
let fileEvidence = singleMachinePerAlert
    | join AlertEvidence on $left.AlertId == $right.AlertId
    | where EntityType has "File"
    | extend normalised_filename=normalisePath(FileName), normalised_folder_path=normalisePath(FolderPath), d=parse_json(AdditionalFields)
    | join kind=inner augmentedAllFilesEvents on $left.DeviceId == $right.DeviceId and $left.normalised_filename == $right.normalised_filename and $left.normalised_folder_path == $right.normalised_folder_path
    | project AlertId, FirstActivityTimestamp = d.CreatedTimeUtc, LastActivityTimestamp = Timestamp, Title, EntityType, DeviceId, DeviceName, Identifier=normalised_filename, LastRemediationState=d.LastRemediationState, DetectionStatus=d.DetectionStatus, normalised_folder_path, process_hash;
let processEvidence = singleMachinePerAlert
    | join AlertEvidence on $left.AlertId == $right.AlertId
    | where EntityType has "Process"
    | extend normalised_filename=normalisePath(FileName), d=parse_json(AdditionalFields)
    | extend process_hash=hash(strcat(DeviceId, d.ProcessId, normalised_filename, todatetime(d.CreationTimeUtc)))
    | project AlertId, FirstActivityTimestamp = d.CreatedTimeUtc, LastActivityTimestamp = Timestamp, Title, EntityType, DeviceId, DeviceName, Identifier=normalised_filename, LastRemediationState=d.LastRemediationState, DetectionStatus=d.DetectionStatus, normalised_folder_path="<empty>", process_hash;
let hashedProcessEvents = DeviceProcessEvents
    | extend process_hash = hash(strcat(DeviceId, ProcessId, normalisePath(FileName), ProcessCreationTime)), parent_hash = hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime));
let tree = hashedProcessEvents
    | join kind=inner hashedProcessEvents on $left.parent_hash == $right.process_hash
    | make-graph process_hash --> process_hash1 with hashedProcessEvents on process_hash
    | graph-match (parent)<-[spawnedBy*1..100]-(child)
        project child.process_hash, child.ProcessId, child.FileName, child.ProcessCommandLine, child.ProcessCreationTime, parent.ProcessId, parent.FileName, parent.ProcessCommandLine, parent.ProcessCreationTime, Path = strcat(spawnedBy.ProcessId, " ", spawnedBy.ProcessCommandLine), sig=normalisePath(coalesce(null_if_not_implant_sig(child.FileName), null_if_not_implant_sig(parent.FileName)))
    | extend PathLength = array_length(Path)
    | where isnotempty(sig);
fileEvidence
| union processEvidence
| join kind=inner tree on $left.process_hash == $right.child_process_hash
| project-rename ParentProcessImageFileName=sig, CommandLine=child_ProcessCommandLine
| extend data=bag_pack_columns(EntityType, FirstActivityTimestamp, LastActivityTimestamp, Title, Identifier, LastRemediationState, DetectionStatus, ParentProcessImageFileName, CommandLine)
| summarize evidence=make_list(data) by AlertId, DeviceName

2. See error message

Query limits exceeded
Error message
Query execution has exceeded the allowed limits
How to resolve
The query execution was preempted. This could possibly be due to high CPU and/or memory resource consumption. Optimize your query by following best practices and try again

Expected Output

The query to run correctly and not fail.

Actual Output

Error screenshot

Additional information

A few suggestions is when running the query to help save on resources defining a let let obas_agents = dynamic(["obas_host1", "obas_host2", "obas_host3"]); this will help limit searches to the specific hosts that are running the agents. Then when referencing it use | where DeviceName has_any (obas_agents) this will allow any case and if it partly contains the hostname as sometimes the hosts name might not be exactly the same as OpenBAS as DeviceName can contain a FQDN instead of just the hostname.

Also limiting the time frame that is being searched to around where the injection was ran might also help with the limits that are being hit. Maybe limit it a few hours before and after the start of the injection time so there is enough coverage.

Below is a rough draft query to add searching for the OpenBAS Agent names and limiting the time frame. It still probably needs tweaking and testing, but it is start. It seems to run but I am getting hanging on lines 50 through 62 of this query. I am not familiar enough with this section of the query to see how we could optimize it.

// Define OpenBAS Agents running payloads
let obas_agents = dynamic(["openbas_agent1", "openbas_agent2"]);
// Define Time frame when activity could happen
let startTime = datetime(2025-04-03 12:00:00);
let endTime = datetime(2025-04-03 15:40:00);
let null_if_not_implant_sig = (sig:string) { iff((sig startswith "obas-implant"), sig, ""); };
let normalisePath = (pathString:string) { tolower(trim_end("/", replace_string(pathString, "\\\\", "/"))); };
// Look at all DeviceEvents limited to the OpenBAS Agents
let augmentedDeviceEvents = DeviceEvents
    | where Timestamp between (startTime .. endTime) and DeviceName has_any (obas_agents)
    | where isnotnull(InitiatingProcessId) and InitiatingProcessId != 0
    | extend normalised_filename=normalisePath(FileName),
             normalised_folder_path=normalisePath(FolderPath),
             process_hash=hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime))
    | project DeviceId, normalised_filename, normalised_folder_path, process_hash;
// Look at all DeviceFileEvents limited to the OpenBAS Agents
let augmentedFileEvents = DeviceFileEvents
    | where Timestamp between (startTime .. endTime) and DeviceName has_any (obas_agents)
    | extend normalised_filename=normalisePath(FileName),
             normalised_folder_path=normalisePath(parse_path(FolderPath).DirectoryPath),
             process_hash=hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime))
    | project DeviceId, normalised_filename, normalised_folder_path, process_hash;
// Combine all DeviceEvents and DeviceFileEvents
let augmentedAllFilesEvents = augmentedDeviceEvents
    | union augmentedFileEvents
    | distinct DeviceId, normalised_filename, normalised_folder_path, process_hash;
// Pull AlertEvidence related to the OpenBAS Agents
let singleMachinePerAlert = AlertEvidence
    | where Timestamp between (startTime .. endTime) and ServiceSource == 'Microsoft Defender for Endpoint' and EntityType has "Machine" and EvidenceRole has "Impacted" and DeviceName has_any (obas_agents)
    | where EntityType has "Machine" and EvidenceRole has "Impacted"
    | distinct AlertId, DeviceId, DeviceName;
// Search AlertEvidence for FileEvidence
let fileEvidence = singleMachinePerAlert
    | join AlertEvidence on $left.AlertId == $right.AlertId
    | where EntityType has "File"
    | extend normalised_filename=normalisePath(FileName), normalised_folder_path=normalisePath(FolderPath), d=parse_json(AdditionalFields)
    | join kind=inner augmentedAllFilesEvents on $left.DeviceId == $right.DeviceId and $left.normalised_filename == $right.normalised_filename and $left.normalised_folder_path == $right.normalised_folder_path
    | project AlertId, FirstActivityTimestamp = d.CreatedTimeUtc, LastActivityTimestamp = Timestamp, Title, EntityType, DeviceId, DeviceName, Identifier=normalised_filename, LastRemediationState=d.LastRemediationState, DetectionStatus=d.DetectionStatus, normalised_folder_path, process_hash;
// Search AlertEvidence for ProcessEvidence
let processEvidence = singleMachinePerAlert
    | join AlertEvidence on $left.AlertId == $right.AlertId
    | where EntityType has "Process"
    | extend normalised_filename=normalisePath(FileName), d=parse_json(AdditionalFields)
    | extend process_hash=hash(strcat(DeviceId, d.ProcessId, normalised_filename, todatetime(d.CreationTimeUtc)))
    | project AlertId, FirstActivityTimestamp = d.CreatedTimeUtc, LastActivityTimestamp = Timestamp, Title, EntityType, DeviceId, DeviceName, Identifier=normalised_filename, LastRemediationState=d.LastRemediationState, DetectionStatus=d.DetectionStatus, normalised_folder_path="<empty>", process_hash;
// Look at all DeviceProcessEvents limited to the OpenBAS Agents
let hashedProcessEvents = DeviceProcessEvents
    | where Timestamp between (startTime .. endTime) and DeviceName has_any (obas_agents)
    | extend process_hash = hash(strcat(DeviceId, ProcessId, normalisePath(FileName), ProcessCreationTime)), parent_hash = hash(strcat(DeviceId, InitiatingProcessId, normalisePath(InitiatingProcessFileName), InitiatingProcessCreationTime));
let tree = hashedProcessEvents
    | join kind=inner hashedProcessEvents on $left.parent_hash == $right.process_hash
    | make-graph process_hash --> process_hash1 with hashedProcessEvents on process_hash
    | graph-match (parent)<-[spawnedBy*1..100]-(child)
        project child.process_hash, child.ProcessId, child.FileName, child.ProcessCommandLine, child.ProcessCreationTime, parent.ProcessId, parent.FileName, parent.ProcessCommandLine, parent.ProcessCreationTime, Path = strcat(spawnedBy.ProcessId, " ", spawnedBy.ProcessCommandLine), sig=normalisePath(coalesce(null_if_not_implant_sig(child.FileName), null_if_not_implant_sig(parent.FileName)))
    | extend PathLength = array_length(Path)
    | where isnotempty(sig);
fileEvidence
| union processEvidence
| join kind=inner tree on $left.process_hash == $right.child_process_hash
| project-rename ParentProcessImageFileName=sig, CommandLine=child_ProcessCommandLine
| extend data=bag_pack_columns(EntityType, FirstActivityTimestamp, LastActivityTimestamp, Title, Identifier, LastRemediationState, DetectionStatus, ParentProcessImageFileName, CommandLine)
| summarize evidence=make_list(data) by AlertId, DeviceName

The above query works much better if I take out this section of it

let tree = hashedProcessEvents
    | join kind=inner hashedProcessEvents on $left.parent_hash == $right.process_hash
    | make-graph process_hash --> process_hash1 with hashedProcessEvents on process_hash
    | graph-match (parent)<-[spawnedBy*1..100]-(child)
        project child.process_hash, child.ProcessId, child.FileName, child.ProcessCommandLine, child.ProcessCreationTime, parent.ProcessId, parent.FileName, parent.ProcessCommandLine, parent.ProcessCreationTime, Path = strcat(spawnedBy.ProcessId, " ", spawnedBy.ProcessCommandLine), sig=normalisePath(coalesce(null_if_not_implant_sig(child.FileName), null_if_not_implant_sig(parent.FileName)))
    | extend PathLength = array_length(Path)
    | where isnotempty(sig);

and

| join kind=inner tree on $left.process_hash == $right.child_process_hash
| project-rename ParentProcessImageFileName=sig, CommandLine=child_ProcessCommandLine
| extend data=bag_pack_columns(EntityType, FirstActivityTimestamp, LastActivityTimestamp, Title, Identifier, LastRemediationState, DetectionStatus, ParentProcessImageFileName, CommandLine)
| summarize evidence=make_list(data) by AlertId, DeviceName

Screenshots (optional)