Application Insight Analytics Pivot

Question

Is there a way to pivot in Azure Application insight analytic queries? SQL has a Pivot Keyword, can similar be achieved in Application insight Analytics?

When I run the below query I get exceptions and count, but I would like to see a day on day trending

exceptions 
| where timestamp >= ago(24h) 
| extend Api = replace(@"/(\d+)",@"/xxxx", operation_Name)
| summarize count() by type
| sort by count_ desc 
| limit 10
| project Exception = type, Count = count_ 

I am looking for something below day wise.

Answer 1

The easiest way to achieve something similar to what you need is by using:

exceptions
| where timestamp >= ago(7d)
| summarize count() by type, bin(timestamp, 1d) 

This will give in the output one line per-type, per-day. Not exactly what you wanted but it will look good when rendered in graph (will give you a line for each type).

To get a table similar to what you put in your example would be more difficult, but this query should do the trick:

exceptions 
| where timestamp >= startofday(ago(3d)) 
| extend Api = replace(@"/(\d+)",@"/xxxx", operation_Name)
| summarize count() by type, bin(timestamp, 1d)
| summarize 
    Today = sumif(count_, timestamp == startofday(now())),
    Today_1 = sumif(count_, timestamp == startofday(ago(1d))),
    Today_2 = sumif(count_, timestamp == startofday(ago(2d))),
    Today_3 = sumif(count_, timestamp == startofday(ago(3d)))
    by type