0

I'm trying to build a dashboard in Azure Sentinel's workbook. One of the columns is a JSON Array of varying length. I'd like to split that array so that each element in the array becomes its own column, but I can't figure out a good way to do that. Basically I want this

-----------------------------------
Company | products
-----------------------------------
Apple     | [iMac, iPhone, iPad]
Microsoft | [xBox, xBox 360]
Google    | [Chromebook]
--------------------------------------

To become

-----------------------------------
Company | product_1 | product_2 | product_3
-----------------------------------
Apple | iMac        |iPhone     |iPad
Microsoft | xBox    |xBox 360   |
Google | Chromebook |           |
--------------------------------------
jfeldzy
  • 90
  • 9

1 Answers1

3

not highly-efficient, but here's an option:

datatable(company:string, products:dynamic)
[
    "Apple",     dynamic(['iMac', 'iPhone', 'iPad']),
    "Microsoft", dynamic(['xBox', 'xBox 360']),
    "Google",    dynamic(['Chromebook']),
]
| mv-apply with_itemindex=i products on (
    extend p = pack(strcat("product_", i+1), products)
    | summarize b = make_bag(p)
)
| evaluate bag_unpack(b)
| project-reorder company, product* asc

-->

| company   | product_1  | product_2 | product_3 |
|-----------|------------|-----------|-----------|
| Apple     | iMac       | iPhone    | iPad      |
| Microsoft | xBox       | xBox 360  |           |
| Google    | Chromebook |           |           |
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • hm I think this should work. I'm going to keep this open for a bit longer in case someone has something a bit more efficient – jfeldzy Jan 27 '21 at 02:07
  • Trying this out and it looks like order is not preserved using this method. Is there a way to tweak this to guarantee order is preserved? – jfeldzy Jan 27 '21 at 02:24
  • You may want to clarify *which* order isn't preserved (rows? columns?) and provide an example to demonstrate the issue you're asking about. – Yoni L. Jan 27 '21 at 05:43
  • the order of columns isn't preserved. So for example, the table could up looking something like ````product_2 | product 1 | product 4 | product 3``` My guess is that it's ordering based off hash table "bag" the values are stored in, which is why is loses its order – jfeldzy Jan 27 '21 at 21:21
  • you could use `project-reorder` to overcome that. i've updated my reply with that example – Yoni L. Jan 27 '21 at 21:47
  • Well that deserves getting the full answer then! – jfeldzy Jan 28 '21 at 15:07