Search code examples
azureazure-application-insightskql

App insight query to get result for each id and category mapping with number of count

Event 1
Custom Event
Prop1- abc
customDimensions
  ID - [4,6,11] //multiple id’s
  Category- Q //can be one of P,Q,R,S

Event 2
Custom Event
Prop1- abc
customDimensions
  ID - [10,11,25,2] //multiple id’s
  Category- Q //can be one of P,Q,R,S

Event 3
Custom Event
Prop1- abc
customDimensions
  ID - [11,9,26,8] //multiple id’s
  Category- R //can be one of P,Q,R,S

These are application insight events, I'm looking for result records as below in 3 columns mapped for each ID, Category, and count

ID - Category - count
11 - Q - 2
11 - R - 1
4 - Q - 1
6 - Q - 1
26 - R - 1
....

enter image description here

Solution

  • As posted by the asker in the comments, here is the syntax which we need to use to expand the CustomDimensions into columns:

    mv-expand split(customDimensions_ID,',')

    Check mv-expand operator for more information.