XQL - what am I doing wrong?

Showing results for 
Show  only  | Search instead for 
Did you mean: 
Please sign in to see details of an important advisory in our Customer Advisories area.

XQL - what am I doing wrong?

L4 Transporter

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. 






L3 Networker

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.


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.





L2 Linker

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! 

  • 3 replies
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!