Row_number() or other analytical functions possible in appinisghts?

Question

I have a pretty simple problem, but I am using appInsights and cant seem to figure out how to replicate row_number() function in aiql or any of the functions (no sense of partition by).

I have pageviews table and I order them by session and timestamp. I would like to add a rown_number column to the set

pageViews | where timestamp > ago(14d) | order by session_id, timestamp desc
| extend rn =row_number() partition by session_id

Does anyone know if that is possible in aiql

Answer 1

There's a way to do it, but it isn't very pretty.

To get something like paging, you need to make a complicated query, and use summarize and makeList and invent a rowNum field in your query, then use mvexpand to re-expand the lists and then filter by the rowNum.

from this question about paging, which is generally the question that follows rownum questions: how do I Page results from Azure Application Insights Analytics API

Answer 2

There's now a better way as there's a support for row_number function. To use it, simply pipe the following statement

...| serialize | extend rn = row_number()