USQL - Load time by browser type

[aoliva]

Hi all,

My goal is the following:

For a given key user action, I want to find the median visually complete time for each browser type (Desktop, Mobile, and Tablet). I'm using USQL to attempt this, with the following query:

SELECT MEDIAN(useraction.visuallyCompleteTime) FROM useraction WHERE userAction.name="XXXXXXXXX" AND usersession.browserType="Mobile Browser" AND usersession.userType="REAL_USER" AND useraction.application="XXXXX"

The results that I'm getting, however, do not match my tenant. I get a value of 4.37 seconds from the query, and 4.5 seconds from our SAAS tenant. This is a small difference, but I'm wondering if there is any way I can improve my query to get the numbers to match.

So two questions:
- Is my query the correct way to find the median load times of different browser types for a specific action?

- If so, is this difference that I'm seeing just a quirk of Dynatrace? As long as the values from the query are always off from the tenant I'm ok as long as the difference is small.

Thanks

[dave_mauney]

Did you factor out live sessions in the UI? Also, how exactly did you test in the UI?

Here are a couple of queries I was playing with to test this but I am not really clear on a good way to verify them against the UI:

SELECT MEDIAN(useraction.visuallyCompleteTime) FROM useraction GROUP BY useraction.application, usersession.userType, userAction.name, usersession.browserType

SELECT MEDIAN(useraction.visuallyCompleteTime) FROM useraction GROUP BY useraction.application

[David_Hauger · Accepted solution]

Well, there are some potential differences as these systems were built to serve different use cases and not to be compared against each other. However, you can compare the data and for each potential reason there is a solution:

1. The Timezone needs to be checked. The custom metrics in the UI use the client time zone, the USQL API uses UTC time.
   Solution: Make sure you adjust your time frame accordingly.
2. 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.
   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.
3. Timeframe in user session search and USQL are also rounded (with Sprint 159 the rounding error was reduced, but may still be 100s for last 2h, 6min for timeframes between 1 day and 1 week, 16min for > 1week, etc), but that's a minor thing.
   Solution: You can solve this discrepancy with the additional filter mentioned in 2. as well.
4. Make sure that the extrapolation level is in USQL is 1. Otherwise, in order to provide faster responses, we run the query on a subset of the data and extrapolate the values. if it is one, the complete raw data was used; if it is e.g. 4, then 1/4 of your data was analyzed. This happens usually for bigger load or bigger time frames.
   Solution: To solve this and to get more exact data, just reduce your timeframe. You can either try how far you can reduce your time frame until there is no extrapolation any more (you might just be at the edge; we extrapolate only with magnitudes of 4, so 4, 16, 64, etc) or simply divide it by the extrapolation level you received, then it will always run on raw data.
5. Different filters might be applied. This is the most unlikely reason for a difference, but possible.
   Solution: Make sure that the same filters are applied on both screens that you compare. Management zones, Real users, Applications are the most common filters that you might have applied.
6. USQL only works on completed user sessions. Live user sessions will not appear.
   Solution: Choose a time frame in the past where you are sure that all usersessions have already timed out; so not "last something", but a time frame that ends e.g. 1 day in the past.

[aoliva]

This is a great and comprehensive answer, thanks so much. There is definitely more for me to consider when I run these queries. I appreciate the solutions you provided for the variety of cases.

[ssmeets]

Hi David,

I'm trying to recreate your solution to issue number 2. I want to chart all occurrences of the Login page in the last 2 hours and intervals of 5 minutes. 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 = "Inlogpagina"
group by datetime(startTime,"HH:mm","5m"), name
order by datetime(startTime,"HH:mm","5m") limit 1000

Everything works fine, except for the fact that it indeed uses the time of the user session, like you stated already. When I try to recreate your solution, I don't get any data as output. While there is in fact a lot of data to be shown. Can you help me?