0

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. enter image description here

Shiju Samuel
  • 1,373
  • 6
  • 22
  • 45

1 Answers1

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
EranG
  • 822
  • 4
  • 10
  • This is not what I want, I need to build an HTML to send a mail and cannot use the graph. Is there any other way to convert rows to columns, any pointers or guidance will be helpful – Shiju Samuel Jun 21 '17 at 04:23
  • I edited my answer to yield something like what you wanted – EranG Jun 21 '17 at 11:22