XQL - Process Tree Analysis - Join Statements

[AvesterFahimipour]

Hello,
I am currently trying to create a five depth process tree to perform long tail analysis on it.
The query in KQL language can be found here

The moment I perform the first join statement everything breaks
Here is the almost complete code but you can just test the first join.

dataset = xdr_data 
| filter event_type = ENUM.PROCESS 
| fields agent_hostname , agent_id ,
action_process_image_name as InitiatingProcessG3ParentFileName,action_process_image_sha256 as InitiatingProcessG3ParentSHA256 ,action_process_os_pid as InitiatingProcessG3ParentId, action_process_image_command_line as InitiatingProcessG3ParentCommandLine ,action_process_instance_execution_time as InitiatingProcessG3ParentCreationTime, actor_process_image_name as InitiatingProcessG4ParentFileName,actor_process_image_sha256 as InitiatingProcessG4ParentSHA256,actor_process_os_pid as InitiatingProcessG4ParentId,actor_process_command_line as InitiatingProcessG4ParentCommandLine, actor_process_execution_time as InitiatingProcessG4ParentCreationTime
| join type = left (dataset = xdr_data 
| filter event_type = ENUM.PROCESS 
| fields agent_id ,action_process_image_name as InitiatingProcessG2ParentFileName,action_process_image_sha256 as InitiatingProcessG2ParentSHA256 ,
action_process_os_pid as InitiatingProcessG2ParentId, action_process_image_command_line as InitiatingProcessG2ParentCommandLine ,
action_process_instance_execution_time as InitiatingProcessG2ParentCreationTime, actor_process_image_name as InitiatingProcessG3ParentFileName,
actor_process_image_sha256 as InitiatingProcessG3ParentSHA256,actor_process_os_pid as InitiatingProcessG3ParentId,actor_process_command_line as InitiatingProcessG3ParentCommandLine,
actor_process_execution_time as InitiatingProcessG3ParentCreationTime
) as G3 G3.agent_id = agent_id and g3.InitiatingProcessG3ParentFileName = InitiatingProcessG3ParentFileName and G3.InitiatingProcessG3ParentId = InitiatingProcessG3ParentId and G3.InitiatingProcessG3ParentCreationTime = InitiatingProcessG3ParentCreationTime 
| join type = left (dataset = xdr_data 
| filter event_type = ENUM.PROCESS 
| fields agent_id ,action_process_image_name as InitiatingProcessG1ParentFileName,action_process_image_sha256 as InitiatingProcessG1ParentSHA256 ,
action_process_os_pid as InitiatingProcessG1ParentId, action_process_image_command_line as InitiatingProcessG1ParentCommandLine ,
action_process_instance_execution_time as InitiatingProcessG1ParentCreationTime, actor_process_image_name as InitiatingProcessG2ParentFileName,
actor_process_image_sha256 as InitiatingProcessG2ParentSHA256,actor_process_os_pid as InitiatingProcessG2ParentId,actor_process_command_line as InitiatingProcessG2ParentCommandLine,
actor_process_execution_time as InitiatingProcessG2ParentCreationTime
) as G2 g2.agent_id = agent_id and g2.InitiatingProcessG2ParentFileName = InitiatingProcessG2ParentFileName and g2.InitiatingProcessG2ParentId = InitiatingProcessG2ParentId and g2.InitiatingProcessG2ParentCreationTime = InitiatingProcessG2ParentCreationTime 
| join type = left (dataset = xdr_data 
| filter event_type = ENUM.PROCESS 
| fields agent_id ,action_process_image_name as InitiatingProcessParentFileName,action_process_image_sha256 as InitiatingProcessParentSHA256 ,
action_process_os_pid as InitiatingProcessParentId, action_process_image_command_line as InitiatingProcessParentCommandLine ,
action_process_instance_execution_time as InitiatingProcessParentCreationTime, actor_process_image_name as InitiatingProcessG1ParentFileName,
actor_process_image_sha256 as InitiatingProcessG1ParentSHA256,actor_process_os_pid as InitiatingProcessG1ParentId,actor_process_command_line as InitiatingProcessG1ParentCommandLine,
actor_process_execution_time as InitiatingProcessG1ParentCreationTime
) as G1 g1.agent_id = agent_id and g1.InitiatingProcessG1ParentFileName = InitiatingProcessG1ParentFileName and g1.InitiatingProcessG1ParentId = InitiatingProcessG1ParentId and g1.InitiatingProcessG1ParentCreationTime = InitiatingProcessG1ParentCreationTime 
| join type = left (dataset = xdr_data 
| filter event_type = ENUM.PROCESS
| fields agent_id ,action_process_image_name as InitiatingProcessFileName,action_process_image_sha256 as InitiatingProcessSHA256 ,
action_process_os_pid as InitiatingProcessId, action_process_image_command_line as InitiatingProcessCommandLine ,
action_process_instance_execution_time as InitiatingProcessCreationTime, actor_process_image_name as InitiatingProcessParentFileName,
actor_process_image_sha256 as InitiatingProcessParentSHA256,actor_process_os_pid as InitiatingProcessParentId,actor_process_command_line as InitiatingProcessParentCommandLine,
actor_process_execution_time as InitiatingProcessParentCreationTime
) as P p.agent_id = agent_id and p.InitiatingProcessParentFileName = InitiatingProcessParentFileName and p.InitiatingProcessParentId = InitiatingProcessParentId and InitiatingProcessParentCreationTime = InitiatingProcessParentCreationTime 
| comp count() by InitiatingProcessFileName ,InitiatingProcessParentFileName , InitiatingProcessG1ParentFileName , InitiatingProcessG2ParentFileName , InitiatingProcessG3ParentFileName , InitiatingProcessG4ParentFileName 

 The above code is not complete but after testing for several hours with one single join I have now given up and came here for assistance.

What is causing the process tree to break? because I am only getting null fields.
When adding and removing the actor (G4) the behaviour completely changes and I have no explanation on why.
Based on the document it seems that left gives you the left side and the inner so leftouter by default.
"Returns all records from the parent result set, plus any records from the join result set that intersect with the parent result set."

[AvesterFahimipour]

I made further updates it seems that I have to use sub type process start and  _time for action process creation time
I still don't know why the join does not work
You can use the query below to try to fix the join if possible.

dataset = xdr_data 
| filter event_type = ENUM.PROCESS and event_sub_type = ENUM.PROCESS_START 
| fields agent_hostname , agent_id ,
action_process_image_name as InitiatingProcessG3ParentFileName,action_process_image_sha256 as InitiatingProcessG3ParentSHA256 ,action_process_os_pid as InitiatingProcessG3ParentId, action_process_image_command_line as InitiatingProcessG3ParentCommandLine , _time as InitiatingProcessG3ParentCreationTime, actor_process_image_name as InitiatingProcessG4ParentFileName,actor_process_image_sha256 as InitiatingProcessG4ParentSHA256,actor_process_os_pid as InitiatingProcessG4ParentId,actor_process_command_line as InitiatingProcessG4ParentCommandLine, actor_process_execution_time as InitiatingProcessG4ParentCreationTime
| alter InitiatingProcessG3ParentCreationTime = to_epoch(InitiatingProcessG3ParentCreationTime, "MILLIS")
| join type = left conflict_strategy = both (dataset = xdr_data 
| filter event_type = ENUM.PROCESS and event_sub_type = ENUM.PROCESS_START
| fields agent_id ,action_process_image_name as InitiatingProcessG2ParentFileName,action_process_image_sha256 as InitiatingProcessG2ParentSHA256 ,
action_process_os_pid as InitiatingProcessG2ParentId, action_process_image_command_line as InitiatingProcessG2ParentCommandLine ,
_time as InitiatingProcessG2ParentCreationTime, actor_process_image_name as InitiatingProcessG3ParentFileName,
actor_process_image_sha256 as InitiatingProcessG3ParentSHA256,actor_process_os_pid as InitiatingProcessG3ParentId,actor_process_command_line as InitiatingProcessG3ParentCommandLine,
actor_process_execution_time as InitiatingProcessG3ParentCreationTime
| alter InitiatingProcessG2ParentCreationTime = to_epoch(InitiatingProcessG2ParentCreationTime, "MILLIS")
) as G3 G3.agent_id = agent_id and G3.InitiatingProcessG3ParentFileName = InitiatingProcessG3ParentFileName and G3.InitiatingProcessG3ParentId = InitiatingProcessG3ParentId and G3.InitiatingProcessG3ParentCreationTime = InitiatingProcessG3ParentCreationTime 

[AvesterFahimipour · Accepted solution]

I fixed it the execution times did not match so had to remove that