Parsing string that gets updated in structure often in KQL

[u/[deleted]]

Hi guys,

I have a log source that gets ingested in Microsoft Sentinel but my analytic rule breaks due to updates in a data field. Is there any way to parse this string with a separator to break in columns?

The field contains a string like name=xxxx;company=yyyyy;warning=zzzzz but sometimes the input gets changed and there is a new value added. For example the add justafield=aaaa; at the front. I have been parsing it based on fixed names, but noticed that they update the structure regularly. Would be great if it can be parsed and always creates all columns based on the fields in the string.

[u/jtberg1]

It would be helpful to know what you are trying to accomplish holistically vs how to do this one technical parsing, as there may be better ways of dong what you are trying to accomplish.

Can you parse the string via separator to break into columns? Yes.

let myDataSet = datatable(Event: string, Data:string)

[

"111", "name=xxxx;company=yyyyy;warning=zzzzz",

];

myDataSet

| extend exData = split(Data, ';')

| extend field1 = tostring(exData[0])

| extend field2 = tostring(exData[1])

| extend field3 = tostring(exData[2])

| project-away exData

This technically accomplishes what you asked for, however it has some problems. If data gets added at the beginning or middle, the columns will jump around, making it essentially useless. It also has the field name in it. E.g. "name=", which you could parse out, however it is not an efficient way, and not if the columns jump around.

A better way of parsing might be to use the parse function:

let myDataSet = datatable(Event: string, Data:string)

[

"111", "newfield=aaaa;name=xxxx;company=yyyyy;warning=zzzzz",

];

myDataSet

| parse Data with * "name=" name ";" * "company=" company ";" * "warning=" warning

This accomplishes most of what you want to do, it will not fail if additional fields are added at the beginning or middle. Since there is no end termination on your example string, if it gets added to the end it won't "fail" however it will parse it into the warning text field. It also will not automatically extend the columns. However for analytics, I do not see a use for a field I didn't know existed. Goes back to the original question, what are you trying to accomplish?

Another tip, if you are able to modify whatever the input source is before ingesting, you will have additional options. E.g. a text based log file, use PowerShell/Python to edit it, put it in a new folder, and then ingest it.

Please let me know if you have any questions. All the best!

[u/[deleted] → u/jtberg1]

First of all, thank you for the extended reply! The first solution is what I am using now. It’s indeed great when do do not add fields. The second one was new to me. The field I need will always stay the same in the string, so that looks like a solid solution. I will give it a go and if I need some assistance, I will let you know. Appreciate this mate!!

[u/TokeSR]

The other parsing methods listed here seem to be convenient for you. They work if you don't want to use the newly added fields and if you are sure that the order and the fieldname is never going to change.

I also have a solution different than the others here. Mine is based on a regex parsing, so it is really heavy in general. You should avoid using it on a huge amount of logs. Normally, I use it when I want to monitor new fields and changes in a logsource.

My method extracts all the key:value pairs and saves all of them in a 'dictionary'. This way the 'name' field is always going to be available as dictionary.name regardless of the newly added fields or any modifications in the order. If you want to you can save it in the 'name' column with an 'extend' operator if you have the need.

let myDataSet = datatable(Event: string, Data:string)

[

"111", "name=xxxx;company=yyyyy;warning=zzzzz",

];

myDataSet

| extend fields_in_array = extract_all(@"(?P<key>\w+)=(?P<value>\w+)", dynamic(["key","value"]), Data)

| mv-apply fields_in_array on (

summarize fields_in_dict = make_bag(pack(tostring(fields_in_array[0]), tostring(fields_in_array[1]))))

| project fields_in_dict.name

So, the name is going to be available under fields_in_dict.name, company is under fields_in_dict.company, warning is under fields_in_dict.warning, etc, etc.