Forum Discussion
Summarize dynamic array?
MicrosoftHere's one option:
datatable(Data:dynamic) [ dynamic([9, 12, 24]), dynamic([17, 12, 37]), dynamic([9, 17, 37]), ] | mv-expand with_itemindex = ArrayIndex Data to typeof(int) | summarize c = count() by Data, ArrayIndex | summarize arg_max(c, Data) by ArrayIndex | project-away c
Yoni Thanks for the reply! However, these sets aren't pre-programmed sets, i.e. would it be possible to create a datatable like this?:
datatable(Data:dynamic)
[
dynamic([Data1, Data2, Data3])
]Where Data1, Data2, and Data3 are three distinct variables that change?
- Yoni
using the `datatable` operator in that sample was just in order to have some input data set. the "point" (answer to your original question) was in the lines starting from `| mv-expand ...`
if that doesn't solve your issue -perhaps, you can show a sample of how your _real_ data looks like, so that we can work with that?
Yoni Sorry, I misunderstood. Here's my exact example.
I have a string array called 'Tiles' which looks like ["1", "2", "3", ""]
My ultimate goal is to summarize 'Tiles' (ignoring the last element in the array since it is always empty), to show the most common number in each element of the array for around 100 different values for 'Tiles'.
For example, let's say 5 values of 'Tiles' are:
["1", "2", "3", ""]
["1", "12", "9", ""]
["12", "12", "7", ""]
["4", "6", "9", ""]
["9", "5", "5", ""]
The goal would be to produce an output that shows:
Tile Most Common Number 1 1 2 12 3 9 The furthest I've gotten is splitting the array into integer variables and seeing how many times a given value appears for the first element in the array (sorry for bad code example):
let winner_chose_tiles = ['events.all'] | where FullName_Name == "winner_chose_tiles"; winner_chose_tiles | project Entity_Id, Tiles = split(EventData["Tiles"], ",") | project Tile1 = toint(Tiles[0]), Tile2 = toint(Tiles[1]), Tile3 = toint(Tiles[2]), Entity_Id | summarize FirstTile = count() by Tile1- Yoni
based on my understanding of your description, this should work (like in the original example). if my understanding isn't correct, please provide the input data set using the `datatable` operator, so that it's clearer how your input data set looks like
datatable(EventData:dynamic) [ dynamic({"Tiles":["1", "2", "3", ""]}), dynamic({"Tiles":["1", "12", "9", ""]}), dynamic({"Tiles":["12", "12", "7", ""]}), dynamic({"Tiles":["4", "6", "9", ""]}), dynamic({"Tiles":["9", "5", "5", ""]}), ] | mv-expand with_itemindex = Tile Value = EventData.Tiles | where isnotempty(Value) | summarize c = count() by Value = toint(Value), Tile = Tile + 1 | summarize arg_max(c, Value) by Tile | project-away c
this returns:
Tile Value 1 1 2 12 3 9