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:
https://docs.microsoft.com/en-us/windows/security/threat-protection/auditing/event-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
