topic Function usage in USQL query in WHERE statement in Open Q&A

Function usage in USQL query in WHERE statement

gofman_anatoly — Thu, 27 May 2021 13:58:02 GMT

Hello

If there a way to create USQL query with function (for example - AVG) in WHERE statement?

Sample - a query that returns count of user actions where visually complete time more when average for this user action.

select count(visuallyCompleteTime) FROM useraction where name="%something%" and visuallyCompleteTime > AVG(visuallyCompleteTime)

Best Regards

Tolik.

Re: Function usage in USQL query in WHERE statement

Babar_Qayyum — Mon, 29 Mar 2021 08:45:39 GMT

Hello @gofman_anatoly

Did you get a chance to look into the below documentation for the Keywords and functions, also the limitation?

https://www.dynatrace.com/support/help/how-to-use-dynatrace/real-user-monitoring/how-to-use-real-user-monitoring/cross-application-user-session-analytics/custom-queries-segmentation-and-aggregation-of-session-data/

Regards,

Babar

Re: Function usage in USQL query in WHERE statement

trevor_masseng1 — Mon, 29 Mar 2021 15:26:23 GMT

Hi,
It is not possible to do this with a single USQL query, but it is possible to make this comparison manually between two USQL tiles, or automatically with two API requests.
I'd recommend either creating a dashboard tile with the Avg user action duration as a Single Value, and another with each user action w/visually complete time printed out in a table for comparison.
Or using the USQL API to run both queries outside of Dynatrace, extracting entries from one JSON response that exceeded the average found by the other request's response.