Search code examples
row-numberazure-log-analyticskql

Getting number of rows using Left Join kql? Function 'row_number' cannot be invoked in current context. Details: the row set must be serialized

I have the following query:

let p1 = pageViews | where url has "xxx";
p1
| join kind=inner (pageViews 
        | where  url !has "xxx")
on session_Id
| project timestamp1, session_Id1, url1, client_CountryOrRegion1, client_StateOrProvince1, client_City1, user_Id1

It does get users that originated from a certain provider and then looks at which URLs they are going to.

I am now trying to get how many users I got from that provider.

I could just do distinct session_Id and count but what I would like to do is add two columns, first for specific session_id and then increment it when it changes and another one to increment for the number of requests made.

i.e

enter image description here

I tried:

 let p1 = pageViews | where url has "project-management";
p1
| join kind=inner (pageViews 
        | where  url !has "project-management")
on session_Id
| project timestamp1, session_Id1, url1, client_CountryOrRegion1, client_StateOrProvince1, client_City1, user_Id1 
| extend Rank=row_number(1)

but it gave me

Function 'row_number' cannot be invoked in current context. Details: the row set must be serialized

Solution

  • The records in the output aren't sorted, therefore there's no meaning to row_number().

    row_number() only works on serialized records, which you have after using order by, or serialize.

    So the solution to your question is to add | serialize before | extend Rank=row_number(1).