article USQL user session count difference when using global timeframe vs when using endTime/startTime in where clause. in Troubleshooting

USQL user session count difference when using global timeframe vs when using endTime/startTime in where clause.

KiranSomanatti — Mon, 26 Jun 2023 11:23:38 GMT

There is a difference between using the startTime/endTime in the WHERE clause and the Global time frame in the USQL.

USQL Global timeframe filter in WebUI will match all user sessions that were active between from and to time.
That means it will match all user sessions that started and ended within the timeframe filter, all user sessions that started before the timeframe and ended within the timeframe, all user sessions that started within the timeframe and ended after the timeframe, and even all user sessions that started before the timeframe and ended after the timeframe

and when you use the endTime where clause this will match only sessions that have the last useraction timestamp within this timeframe added in the query

when you use the startTime where clause this will match only sessions that have the first useraction timestamp within this time frame added in the query

Example:
08:30 09:30
timeframe filter |-----------------------------------------------|
session 1 |----------|
session 2 |-------------------|
session 3 |---------|
session 4 |---------------------------------------------------------|

With Endtime (in the uSQL query):

SELECT COUNT(*) FROM usersession WHERE endTime > 1687422600000 AND endTime < 1687426200000

using the Where clause endTime count will be - 2 (session 1 and session 2)

Without Endtime (timeframe was set at the Global level 8:30 to 9:30 June 22)

SELECT COUNT(*) FROM usersession

using the Global time frame count will be - 4 (all 4 sessions)

Re: USQL user session count difference when using global timeframe vs when using endTime/startTime in where clause.

David_Hauger — Wed, 28 Jun 2023 07:44:07 GMT

Side note:
If you want to have the global timeframe behaviour expressed as a filter, "all sessions active between 8:00 and 9:00" could be represented as "endTime after 8:00 and startTime before 9:00"
The options are:

all sessions that started in this timeframe:
starttime within range (> 8:30 and < 9:30): sessions 2 and 3
all sessions that ended in this timeframe:
endtime within range (> 8:30 and < 9:30): sessions 1 and 2
all sessions that started and ended in this timeframe:
starttime > 8:30 and endtime < 9:30: session 2
all sessions that were active in this timeframe:
starttime < 9:30 and endtime > 8:30: sessions 1,2,3,4 (-> global timeframe)

Reason for picking that option for the global timeframe: It includes most session; if you are interested in any other option, you can add the according filter.
If we had used any other definition, e.g. session 4 would never appear, despite it was active during the whole timeframe and there would be no way to see "all active sessions".

Maybe related:

When you use

SELECT ... from usersession WHERE useraction.startTime ...

it matches all sessions where AT LEAST ONE useraction matched your criteria; and you'll get all matching usersessions.

SELECT DISTINCT useraction.starttime from usersession WHERE useraction.startTime > 1687422600000

may still return you starttimes that are below the value.
This feature is nice for co-occurrence analysis. If you are just interested in useractions, use

SELECT ... from useraction WHERE [useraction.]startTime ...