USQL: Visualize how many times a certain page is loaded?

ssmeets — Fri, 30 Sep 2022 11:10:06 GMT

Hello,

I'm trying to visualize the amount of times that the Login Page is being loaded in a certain timeframe. When I try to do this via USQL, I don't get data from the 'Last 1 hour' and the data changes everytime, because:

"The time frames in USQL refer to the time of the usersession, not to the useractions (even if the table is "FROM useraction"). Therefore, if you have a usersession lasting from 8:45 to 9:15 and your useraction is at 9:13; even if you apply a timeframe filter from 8:00 to 9:00; USQL will consider that useraction (usersession matches the timeframe), but the key performance metrics (regarding only useractions) will not."

As stated here on the forums. It also mentions a solution:

"Solution: To overcome this issue, you can add "and useraction.starttime BETWEEN ... AND ..." (which would be equivalent to useraction.starttime > ... and useraction.starttime < ..., syntax doesn't matter here) to filter only those useractions that are also part of the other dataset."

Unfortunately, when I try this solution I don't get any data as output. While there is in fact a lot of data to be shown. This is my query:

SELECT
datetime(startTime,"HH:mm","5m") AS [Time per 5 minutes],
count(name) AS [Occurrences of Login Page]
FROM useraction
WHERE name = "Login Page"
GROUP BY datetime(startTime,"HH:mm","5m"), name
ORDER BY datetime(startTime,"HH:mm","5m")

Right now, it only shows the amount of login-actions that occured in the sessions that were active for the last 1 hour (when that's my filter) and I want to chart the total amount in the last 1 hour (not just from active sessions in the last hour). Also, I would like to do that multiple times a day and on different days, so adding something like BETWEEN "11.12.19 08:00" AND "11.12.19 10:00" is not very workable.

Can anyone help me with visualizing this correctly? If this isn't possible, is there any other way I can visualize the amount of specific user actions that occured during a certain time period?

Thanks in advance!

Re: USQL: Visualize how many times a certain page is loaded?

skrystosik — Wed, 11 Dec 2019 18:21:15 GMT

First of all, USQL will work only for completed sessions. This means that you cannot use it for live data. If you want extend time frame you should just use time picker in DT:

Another option you may consider is marking those particular user action as Key User Actions. If you will make it (there is limit of 100 key user actions per application) you will be able to display amount of them on regular custom chart without writing queries.

And than use this metric:

If you want to chart XHR action you will find same metric but with XHR in name 🙂

Sebastian

Re: USQL: Visualize how many times a certain page is loaded?

ssmeets — Thu, 12 Dec 2019 09:30:12 GMT

Thanks Sebastian! I was thinking way too complicated. I have now visualized it via the key user actions metric you mentioned. Thanks so much! 🙂

topic Re: USQL: Visualize how many times a certain page is loaded? in Dashboarding

USQL: Visualize how many times a certain page is loaded?

Re: USQL: Visualize how many times a certain page is loaded?

Re: USQL: Visualize how many times a certain page is loaded?