XQL - what am I doing wrong?

[RFeyertag]

Hello dear community!

I am testing some XQLs from the last webinar. 

In my test I fired following XQLs one on one, but I do not understand why the left join doesn't work. I allways get 7 results with the host inventory users preset. Without I get 12 results with 10 unique Users. 

--XQL Begin

config case_sensitive = false
| dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4720
| fields action_evtlog_message , action_evtlog_data_fields , action_evtlog_event_id , agent_hostname , agent_ip_addresses , action_evtlog_description , agent_hostname
| alter Creator_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserName")
| alter Creator_Account_SID = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserSid")
| alter Created_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.TargetUserName")
| alter Created_Account_SID = json_extract(action_evtlog_data_fields , "$.TargetSid")
| alter Creator_Domain_name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectDomainName")
| alter Creator_User_Type = if ((Creator_Domain_name = agent_hostname ), "Local_User", "Domain_User")
| join conflict_strategy = both type = left (
preset = host_inventory_users
| fields endpoint_name , disabled as Is_Created_Account_Disabled , password_expired as Is_Created_Account_password_expired, name , full_name as Created_Account_full_name ) as AddedHostInventory addedhostinventory.endpoint_name = agent_hostname
| filter name = Created_Account_name
| fields Creator_Account_Name,Created_Account_Name , Is_Created_Account_Disabled , Is_Created_Account_password_expired , agent_hostname , Creator_Domain_name ,Creator_User_Type , action_evtlog_event_id , action_evtlog_message , action_evtlog_data_fields

--XQL End

Has anyone an idea what is causing this? I tried right, inner and left combinations with left, right and both conflict strategy. No one works for me. I'd like to get the 12 results from the first query before the join and additionally the results from the second one. 

BR

Rob

[bbucao]

Hi RFeyertag,

It is difficult to tell for sure from just looking at the query since I do not have the telemetry in my environment to test this exact query, but I do see you have additional filtering after your join stage. If you remove that filtering I believe you should see all results from the query before the join, in addition to results from the joined query that intersect with the parent result set, as long as you are using a left join.

Thanks,
Ben

[RFeyertag]

Hey @bbucao

I analyzed the results with and without the left join. As you can see, these two rows are missing (I deleted the IPs, SIDs and computer names out) and need to know how I can change the script to see them too with the left join into host inventory.

BR

Rob

[zarnous]

Hey @RFeyertag 

If the users you dont see after adding the joint for the host insights add-on were/ are deleted/ removed, then that might be the reason you see only 7 users with host insights added to the query , since the preset for the host users shows the current (existing) users on the endpoints, and joining this with the original query gives you the 7 results you pointed to.

Hope that helps! 
Z