Create a simple KQL parser for Azure Sentinel

Molx32
4 min readMay 29, 2020

--

In this post, we focus on a Windows Server 2012 log channel : Microsoft-Windows-RemoteDesktopServices-RdpCoreTS/Operational. This channel gives information regarding failed authentications on RDP, remote IP addresses, etc… and is useful for security analysis. In this article, we will see how the parser works, which might be interesting in order to tweak it as needed or to create your own parser.

Access the data

When integrated to Azure Sentinel, these logs are not well presented, and cannot be used as is. The first two columns on the picture below shows data encapsulated in an XML-formatted string.

RdpCoreTS — Bad parsing

Fortunately, KQL gives us a built-in parse_xml function which “Jsonifies” our data. On the picture below, we can see the result of this function on the EventData column.

By recursively developping each JSON object we can see where is the interesting data. In this case, it is in the Data object. Thus, our parser will look like this.

We identified the data we want, but instead of the JSON, we need the actual values i.e. the string values. In the picture below (EventID 131), the #text field is associated to a value (“TCP”), while the @Name field is associated to the parameter name (“ConnType”).

From there, we want to create a new column named ConnType, and containing the value TCP. We also want to do this for the other objects. I won’t get into the details of JSON parsing, but only gives the code that is understandable with some knowledge.

The value associated to ClientIP looks like <ip-address>:<port>. That’s why a third column named ClientPort is created. This last picture shows the three new columns created (extend statement) from the JSON data we just parsed.

Parse all events

Well, as you may have noticed, what I did above only works for the EventID 131. In this section, we’ll see how to do this with all events. First of all, in the previous example, we have too much columns we don’t care about. That’s why we will project some of these columns, and project-away others.

This request gives all events, but in XML format.

Now, we create a function named RemoteDesktopServices<EventID> for every different EventID we need to parse. Although data will be different for each EventID, functions rely on the same general structure.

Function for EventID 131

Finally, we make a union between all the functions to gather the results in a single table. Also, we need to project all the columns we created (extend statement) within the functions. Then we order it by TimeGenerated.

When we merge those three parts of request, we have the results shown below. If an additional event need to be parsed, the following steps must be followed:

  1. Add a function e.g. RemoteDesktopServices132
  2. Add this function to the union statement
  3. Add the new columns created within the function to the project statement

You can check the complete parser on my Github. There are also a lot of parsers accessible from Azure Sentinel official repository as well!

In this article, I gave an overview on how to create a parser for the Microsoft-Windows-RemoteDesktopServices-RdpCoreTS/Operational channel. The instructions presented in this article may be applicable to many other parsers.

--

--

Molx32

I am a cybersecurity engineer, mainly working on Microsoft solutions.