I figured it out, it was simpler than I realised.
Aggregate parent & child together, output aggregated results to CSV and then use the parent's ParentProcessID as the ProcessID to query to get the grandparent. Finally, aggregate everything again to join the grandparent into the previous parent/child aggregation.
For each query, you can preserve the original process IDs with a simple eval ThisProcessID=ProcessID and then use them in the final aggregation/table view. You also need to rename the fields for the parent and grandparent queries so they don't get merged into the same field during aggregation, i.e. 'FileName' becomes 'ParentFileName'.
In my use-case I had a lot more fields than below and I needed to zip some values and expand them later to prevent multi-value aggregation, so I've included that too.
See example query below.
| inputcsv dispatch=t child_with_parent_processes_aggregated.csv
| append [
search event=ProcessExecution earliest=-1y latest=now [
| inputcsv dispatch=t child_processes.csv
| append [
search event=ProcessExecution earliest=-1y latest=now [
search event=ProcessExecution FileName="cmd.exe"
| eval ChildProcessID=ProcessID
| rename ParentProcessID AS ProcessID
| outputcsv dispatch=t child_processes.csv
| fields ProcessID
]
| rename FileName AS ParentFileName
]
| eval zipped=mvzip(FileName,ChildProcessID,"!!!!!cpid=")
| stats values(*) as * by ProcessID
| mvexpand zipped
| rex field=zipped "^(?<FileName>.*)!!!!!cpid=(?<ChildProcessID>.*)$"
| eval ParentProcessID=ProcessID
| rename ParentProcessID as ProcessID
| table ParentFileName ParentProcessID FileName ChildProcessID ProcessID
| outputcsv dispatch=t child_with_parent_processes_aggregated.csv
| fields TargetProcessId_decimal
]
| rename FileName AS GrandParentFileName
| eval GrandParentProcessID=ProcessID
]
| eval zipped=mvzip(mvzip(mvzip(FileName,ChildProcessID,"!!!!!cpid="),ParentFileName,"!!!!!pname="),ParentProcessID,"!!!!!ppid=")
| stats values(*) as * by ProcessID
| mvexpand zipped
| rex field=zipped "^(?<FileName>.*)!!!!!cpid=(?<ChildProcessID>.*)!!!!!pname=(?<ParentFileName>.*)!!!!!ppid=(?<ParentProcessID>.*)$"
| rename ChildProcessID as ProcessID
| table GrandParentFileName GrandParentProcessID ParentFileName ParentProcessID FileName ProcessID
... View more
