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 | |
--------------------------------------
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 | | |