CODEWITHSUNDEEP
azure-data-explorerkql
gsscoder
gsscoder

Reputation: 3392

KQL: aggregate row by time shift and get value of the more recent row

I think it's more easy to look at the data. We've an application that tracks all user interaction inside an intranet.

eventType pageUrl timestamp timeOnPageMs
pageEvent https://url1.com/ 2021-11-05T06:10:11.591Z 0
pageEvent https://url1.com/ 2021-11-05T06:20:11.591Z 23123
pageEvent https://url2.com/ 2021-11-05T06:11:11.591Z 0
pageEvent https://url2.com/ 2021-11-05T06:30:11.591Z 23123

Open page event are identified having timeOnPageMs=0, otherwise it's close page event.

I want to define a query that extracts all page open events, but with timeOnPageMs of the page close event.

eventType pageUrl timestamp timeOnPageMs
pageEvent https://url1.com/ 2021-11-05T06:10:11.591Z 23123
pageEvent https://url2.com/ 2021-11-05T06:11:11.591Z 33123

I tried using a UDF that lookups the required value, but it seems not possible as described in last reply here.

Thanks in advance for anyone who'll help!

Giacomo S.S.

Upvotes: 0

Views: 547

Answers (1)

Slavik N
Slavik N

Reputation: 5298

You'll need a SessionId to correlate events (to be able to handle cases when there are multiple Open events of the same URL, followed by multiple Close events of the same URL).

And then this is how you solve it:

datatable(eventType:string, pageUrl:string, timestamp:datetime, timeOnPageMs:long, sessionId:string)
[
    "pageEvent","https://url1.com/",datetime(2021-11-05T06:10:11.591Z),0,"id1",
    "pageEvent","https://url1.com/",datetime(2021-11-05T06:10:15.591Z),0,"id2",
    "pageEvent","https://url1.com/",datetime(2021-11-05T06:11:12.591Z),1500,"id2",
    "pageEvent","https://url1.com/",datetime(2021-11-05T06:20:11.591Z),23123,"id1",
    "pageEvent","https://url2.com/",datetime(2021-11-05T06:11:11.591Z),0,"id3",
    "pageEvent","https://url2.com/",datetime(2021-11-05T06:30:11.591Z),23123,"id3"
]
| summarize take_any(eventType, pageUrl), min(timestamp), max(timeOnPageMs) by sessionId

Result:

sessionId eventType pageUrl min_timestamp max_timeOnPageMs
id1 pageEvent https://url1.com/ 2021-11-05 06:10:11.5910000 23123
id2 pageEvent https://url1.com/ 2021-11-05 06:10:15.5910000 1500
id3 pageEvent https://url2.com/ 2021-11-05 06:11:11.5910000 23123

Upvotes: 1

Related Questions