cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
xu_guo
Dynatrace Organizer
Dynatrace Organizer

This page will serve as an overview of various USQL examples.

 

USQL examples

  • Average Document Interactive Time for India
    SELECT starttime, AVG(useraction.documentInteractiveTime) AS "DOM Interactive" 
    FROM usersession 
    WHERE country = "India" 
    GROUP BY starttime
  • Average Network Time for India
    SELECT starttime, AVG(useraction.networkTime) AS "Network time" 
    FROM usersession 
    WHERE country = "India" 
    GROUP BY starttime
  • Top Server Response Time by Country
    SELECT country, AVG(useraction.serverTime) AS "Servertime" 
    FROM usersession 
    GROUP BY country limit 5
  • Top Visually Complete Time by Country
    SELECT country, AVG(useraction.visuallyCompleteTime) AS "Visually Complete" 
    FROM usersession 
    GROUP BY country limit 5
  • Frustrated User Actions by Country
    SELECT country, COUNT(useraction.apdexCategory) 
    FROM usersession 
    WHERE useraction.apdexCategory = "FRUSTRATED" 
    GROUP BY country limit 5
  • Apdex Distribution Breakdown
    SELECT apdexCategory, COUNT(apdexCategory) 
    FROM useraction 
    WHERE apdexCategory != "UNKNOWN" 
    GROUP BY apdexCategory
  • Specific Page Load Count by Country
    SELECT country, COUNT(useraction.name) 
    FROM usersession 
    WHERE useraction.name = "Loading of page /" 
    GROUP BY country
  • Specific Page Load Count by Specific Country
    SELECT starttime, COUNT(useraction.name) 
    FROM usersession 
    WHERE useraction.name = "Loading of page /" AND country = "India" 
    GROUP BY starttime
  • User Session Count for an Application
    SELECT DISTINCT DATETIME(starttime, 'MM-dd', '1d'), COUNT(*) 
    FROM usersession
    WHERE useraction.internalApplicationId = "APPLICATIONID"
  • Apdex Distribution Breakdown filtered by Application
    SELECT apdexCategory, COUNT(apdexCategory) 
    FROM useraction 
    WHERE apdexCategory != "UNKNOWN" AND application="appnamehere" 
    GROUP BY apdexCategory
  • User actions and application with HTTP error counts
    SELECT DISTINCT useraction.name AS "User Action Name", useraction.application AS "Application", SUM(httpRequestsWithErrors) AS "HTTP Errors" 
    FROM useraction WHERE httpRequestsWithErrors IS NOT NULL 
    GROUP BY useraction.application, useraction.name, httpRequestsWithErrors 
    ORDER BY sum(httpRequestsWithErrors) DESC 
    LIMIT 20
  • Applications with HTTP errors
    SELECT DISTINCT useraction.application AS "Application", SUM(httpRequestsWithErrors) AS "HTTP Errors" 
    FROM useraction 
    WHERE httpRequestsWithErrors IS NOT NULL 
    GROUP BY useraction.application, httpRequestsWithErrors 
    ORDER BY sum(httpRequestsWithErrors) DESC 
    LIMIT 20
  • Count bounce rate of each pages
    SELECT useraction.name as "Page Name", COUNT(*) as "Page Views", COUNT(DISTINCT usersession.internalUserId) AS "Unique Page Views", AVG(useraction.duration) As "Average Session", count(bounce) as "# Bounce sessions" FROM usersession WHERE bounce IS true GROUP BY useraction.name
  • Users browser type and its version
    SELECT COUNT(browserMajorVersion) as 'User Session Count', browserMajorVersion as 'Browser Version' FROM usersession GROUP BY browserMajorVersion
  • Sales Funnel
    SELECT FUNNEL(useraction.name='xxxxx' AS "Visits Store", useraction.name= 'xxxxxx' AS "Views Product", useraction.name = 'xxxxx' AS "Starts Checkout", useraction.name= 'xxxxx' AS "Offer Upsells", useraction.name = 'xxxxx' AS "Complete Purchase") FROM usersession
  • Counting specific JS error on an entire application grouped per hour
    select HOUR(usersession.startTime), usererror.name, count(usererror.name) from usererror where name="Script error." group by usererror.name,HOUR(usersession.startTime) order by usererror.name ASC

Current Limitations

  • How to find out how many redirects happened from a specific URL to another URL - We do not have a metric to capture redirects
  • How to use aggregate when trying to compute for success % - (errors / total) - It is currently not possible to calculate while using aggregation
  • How to find the average time spent on each page - There is no metric to calculate duration of staying on a page
Version history
Last update:
‎13 Sep 2023 03:53 PM
Updated by:
Comments
ChadTurner
DynaMight Legend
DynaMight Legend

This is a great list of USQL for users starting out! thanks for sharing this @xu_guo 

apasoquen1
Helper

Awesome examples!

alter
Observer

 

 

I agree this is a great post. Just the bounces part is misleading, since once the WHERE clause is bounce IS true, the number of "Page Views" will be the same as "Bounce sessions", since bounce sessions consist of exactly one page.

To get a better picture, a slight adjustment is needed:

 

SELECT useraction.name as "Page Name", COUNT(*) as "Page Views", COUNT(DISTINCT usersession.internalUserId) AS "Unique Page Views", AVG(useraction.duration) As "Average Session", CONDITION(COUNT(*) , WHERE bounce= true) as "# Bounce sessions" FROM usersession WHERE userType IS "REAL_USER" GROUP BY useraction.name

 

 

radek_jasinski
DynaMight Guru
DynaMight Guru

Very useful especially for users who are new to Dynatrace. Thank you!

Peter_Youssef
Champion

Hello @xu_guo 👩‍💻 :dynaspin:

Thank you so much for the organized and insightful examples.

DanielS
DynaMight Guru
DynaMight Guru

Thanks for sharing @xu_guo !!!!