DTRH: Finding New XQL Fields and Joining Data

Showing results for 
Show  only  | Search instead for 
Did you mean: 

DTRH: Finding New XQL Fields and Joining Data

L0 Member




DTRH: Finding New XQL Fields and Joining Data


I was trying to look at some user login information through XQL and I started poking around the different fields that were being returned. I began one of the user login sample queries available in the query library and made a few small tweaks to de duplicate all the logins just so I could get a list of whatever account logged in that day.




The initial filter sets are event type and limits the results to a Windows event log ID 4624, which represents log ins. In the next line though you will notice that we extract a number of the field values from action_evtlog_message.The results returned looked like this:





 I was curious what the numeric value Logon_Type meant and what was all contained in that field so I copied the entire value and took a look to see if I could determine this, In doing so I noticed that it was basically the entire event log as captured in the Windows event log.


                   Sample Windows Event Log                                            XQL Field action_evtlog_message





This was interesting because it enlightened me to more of the fields that are probably available in the event log and other event logs that we capture.  Though even after going through all the data captured there still was no information to what the Logon_Type  actually represented. A little Google searching and I came to this Microsoft article which goes into detail around this event ID 4624:



What I found in this article was the list of what the Logon types were and the description. This information was extremely valuable in understanding the various aspects that the logon could. For example was it a user physically at the system or over the network, was this a service account, were the credentials somehow passed in cleartext? I’m not sure why this information would not be in the log itself but as an analyst or someone investigating account activity it is extremely useful.


This is actually a perfect use case for 2 XQL features, importing datasets and joining data!


First, I need the logon information provided on the Microsoft website in a format that XDR uses for imported datasets which can be either json or comma separated values. CSV was the obvious choice so I made the text file and copied the fields over separated by commas. The first line of the file actually needs to be the field names and those will be searchable in XQL so I used LogonType, LogonTitle, and LogonDescription.  Each line contains the respective values for those 3 fields and here is the final result:





With this file completed I navigated back in the XDR console to ⚙️->Settings->Data Management and clicked +Lookup to import my newly created dataset.





Once the uploaded file is processed if becomes a new dataset usable within XQL. The name of the file becomes the name of the dataset and as previously mentioned the first line will define the field names. The file I used had a .txt extension and this also becomes part of the dataset name so to do this “cleaner” its probably best to leave off the file extension and just make sure the name of the file is something that would make sense when using.


Now what I wanted to do was associate the information from the Microsoft website around what the logon types meant with the numeric value we displayed for LOGON_TYPE field in XQL.  To do this I used the XQL stage command ‘join’. The join command merges 2 datasets together based on a matching criterion that you define. In this case both the dataset we originally searched on had the field LOGON_TYPE and are created dataset had a matching LOGONTYPE field. In the query below you can see where I added the join to the original query , the joined dataset gets the name you specify it with using the AS function and then that name is the index used before the fields. In my case I called it LT  and I then need to match LT.LOGONTYPE with LOGON_TYPE:




This would be the field that we matched and would add the additional logon title and description to our results where the match occurred.  The joined created results that now provide much more context and information that we did not have prior to importing the new dataset. As you can see the result give you additional detail that would be very useful in an investigation








L7 Applicator

Thanks for posting this.

LIVEcommunity team member
Stay Secure,
Don't forget to Like items if a post is helpful to you!

L1 Bithead

Would you mind sharing the XQL code as text file for us to test tweak to fit our need? 

  • 2 replies
  • 78 Subscriptions
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!