split customDimensions into 3 jsons and then project using kusto query

user_16693903 0
Viewed 65

So, I have a Kusto query

requests
| where customDimensions has'Scope' and customDimensions['Scope'] != 'Unauthenticated' and cloud_RoleName == 'cloud1'
| project x = tostring(customDimensions['x'], y = tostring(customDimensions['y'] ...

i want the split my customDimensions values into 3 jsons. then project it as

| project json1 = json1, json2 = json2, json3 = json3

but i not able to group/split the customDimensions i tried dynamic, datatable but they dont take variables as inputs for e.g dynamic_to_json(dynamic({'x': customDimensions['x'] })) throws an error same for the data tables

Error that the above statement gives is it should end with '}' where clearly my syntax is correct. i feel it somehow does not accesses the customDimensions variable inside dynamic or datatable

could anyone please guide me on this..

I want the output as

| json1 | json2 | json3 |
|{...}  | {...} | {...} |

all the 3 json's will be created from customDimensions or other columns of the same table;

about customDimensions https://camerondwyer.com/2020/05/26/how-to-use-application-insights-custom-properties-in-azure-monitor-log-kusto-queries/

assume there is any thing in customDimensions like

customDimensions = {x:X, y:Y, a:A, ...}

json1 should have {x, y z}
json2 should have {r, t, p,}
json3 should have {w, m, n}
kusto-explorer kql azure-data-explorer c#
1 Answers

Following a conversation with @Sandeep Ranjan:

The challenge here was to extract fields from a JSON document stored as dynamic and then pack them together to 3 new separate JSON documents.

Extracting fields is straightforward, E,g.:

customDimensions.myfield

or

customDimensions["myfield"]

The later can be used for fields names with spaces and/or special characters, E.g.:

customDimensions["my field"]
customDimensions["my-field!"]

Packing the fields can be done with 3 different functions that are synonyms to each other.
Those functions are pack_dictionary(), pack() & bag_pack()

Here is a quick sample:

let requests = datatable(customDimensions:dynamic) [dynamic({"x":1, "y":2, "z":3, "a":"hello", "b":"world", "k1":"v1", "k2":"v2", "k3":"v3", "k4":"v4"})];
requests
| project-rename cd = customDimensions // just to make things a little bit shorter
| extend json_1 = pack_dictionary("x",cd .x, "y", cd.y, "z", cd.z)
| extend json_2 = pack_dictionary("a", cd.a, "b", cd.b)
| extend json_3 = pack_dictionary("k1", cd.k1, "k2", cd.k2, "k3", cd.k3, "k4", cd.k4)
cd json_1 json_2 json_3
{"x":1,"y":2,"z":3,"a":"hello","b":"world","k1":"v1","k2":"v2","k3":"v3","k4":"v4"} {"x":1,"y":2,"z":3} {"a":"hello","b":"world"} {"k1":"v1","k2":"v2","k3":"v3","k4":"v4"}

Fiddle

user_63364790
Related