Using extend to add a count column in Azure Stream Analytics/Application Insights

Question

I have an Application Insights Azure Stream Analytics query that looks like this...

requests
| summarize count() by bin(duration, 1000)
| order by duration asc nulls last

...which gives me something like this, which shows the number of requests binned by duration in seconds, recorded in Application Insights.

| 0    | 1000 |
| 1000 | 500  |
| 2000 | 200  |

I would like to able to add another column which shows the count of exceptions from all requests in each bin.

I understand that extend is used to add additional columns, but to do so I would have to reference the 'outer' expression to get the bin constraints, which I don't know how to do. Is this the best way to do this? Or am I better off trying to join the two tables together and then doing the summarize?

Thanks

EranG EranG · Accepted Answer · 2017-02-28T10:59:27

As you suspected - extend will not help you much here. You need is to run join kind=leftouter on the operation IDs (leftouter is needed so you won't drop requests that did not have any exceptions):

requests
| join kind=leftouter (
    exceptions
    | summarize exceptionsCount = count() by operation_Id
) on operation_Id
| summarize count(), sum(exceptionsCount) by bin(duration, 1000)
| order by duration asc nulls last

Using extend to add a count column in Azure Stream Analytics/Application Insights

1 Answers