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

Can you describe what you're trying to achieve? It might be able to do what you want without needing row numbers — EranG, May 18 '17 at 11:58
Possible duplicate of [Page results from Azure Application Insights Analytics API](http://stackoverflow.com/questions/43371188/page-results-from-azure-application-insights-analytics-api) — John Gardner, May 18 '17 at 20:42
possible duplicate, if the followup question is "how do i use row number to page results" — John Gardner, May 18 '17 at 20:43
I want to order user sessions and sort of do a data flow for each page - where people start browsing and how they explore the website — luckyluke, May 22 '17 at 22:09

score 5 · Answer 1 · answered May 23 '17 at 10:45

5

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()

answered May 23 '17 at 10:45

Dan Hadari

244
1
1

score 0 · Answer 2 · edited May 23 '17 at 11:55

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

Row_number() or other analytical functions possible in appinisghts?

2 Answers2