How to project additional column after using parse and mv-expand in kusto

0
votes

Here's a sample for string column, I would want to split this string column into multiple columns based on the key values of dictionary and if there are more keys, I want to make them into different rows.

<column2>: "for each user:[{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"},
{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"}]"```

I want to trim initial string "for each user:" and create columns for id, publisher, type, Identity.Id, Identity.sid, roles, permissions, name and since there are two dictionaries in the above sample, I want to insert the second item into a new row.

I also want <column1> in the output
2
azure-data-explorerkql
You got two different answers below. Please review them and accept the one that helps you (see this to know why and how). If you're still missing info, please add a comment describing what's missing. - Slavik N

2 Answers

1
votes
print message = ```<column1>: "val",
<column2>: "for each user:[{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"},
{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"}]"```
| parse message with * '<column1>: "' column1 '"' * '<column2>: "for each user:' dict:dynamic '"'
| mv-expand dict
| project dict.id, dict.publisher, dict.type, dict.Identity.Id, column1 // add the rest of your properties of interest
dict_id dict_publisher dict_type dict_Identity_Id column1
1 0 00000000-0000-0000-0000-000000000000 val
1 0 00000000-0000-0000-0000-000000000000 val
0
votes

Here you go:

datatable(Column2: string) [
    'for each user:[{"id":1,"Publisher":null,"type":0,"Identity":{"Id":"00000000-0000-0000-0000-000000000000","sid":"134567890"},"roles":[],"permissions":[],"name":"ABC"},{"id":1,"Publisher":null,"type":0,"Identity":{"Id":"00000000-0000-0000-0000-000000000000","sid":"134567890"},"roles":[],"permissions":[],"name":"ABC"}]"'
]
| parse Column2 with 'for each user:' Json:dynamic '"'
| project Json
| mv-expand Json
| evaluate bag_unpack(Json)

Result:

id Identity name permissions roles type
1 {
"Id": "00000000-0000-0000-0000-000000000000",
"sid": "134567890"
}		 ABC [] [] 0
1 {
"Id": "00000000-0000-0000-0000-000000000000",
"sid": "134567890"
}		 ABC [] [] 0

Step by step explanation:

  1. Use parse to extract the json part that you're interested at into a column named Json
  2. Project only the Json column (as you don't care about the original input string)
  3. Use mv-expand to split the array in the Json column into separate elements (each one will get his own record)
  4. Use evaluate bag_unpack(Json) to have a separate column for every key in the Json column