topic Re: User session query in Dashboarding

User session query

service_apms — Wed, 31 Aug 2022 09:51:12 GMT

Hello everyone,

I have a quick question :

Iam trying to list the average duration of a user action by day but also compare it with D-7 (7 days before)

Iam using the query : with 7 days comparison

select day(startTime), month(starttime), AVG(duration) FROM useraction where name is "reprise de devis" group BY day(startTime), month(starttime)

The result i get :

What i want is to display each day with the D-7 beside it not separated like this.

So on January 1st for example i wanna have 2 bars => Purple Bar (Current day) Pink Bar (D-7)

Thanks

FYI : We used to have this on DCRUM

Re: User session query

skrystosik — Wed, 08 Jan 2020 11:15:17 GMT

I think you should use only grouping by month and add extra time filter to tile for -7 days. for the one that has to be comparision. Copy this tile without extra filters and you will have two bars.

Sebastian

Re: User session query

jonathan_schull — Wed, 08 Jan 2020 15:02:00 GMT

Hello,

This should be done using the "Datetime" function with the "E" parameter (which is for day of the week), and an interval of "1d" (for 1 day). Try using this with a timeframe of "Last 7 Days" while comparing to the timeframe 7 days ago:

SELECT DATETIME(starttime, "E", "1d"), AVG(duration) 
FROM useraction 
WHERE name is "reprise de devis"
GROUP BY DATETIME(starttime, "E", "1d")

It should look something like this:

Cheers,
Jonathan

Re: User session query

service_apms — Thu, 09 Jan 2020 09:04:01 GMT

Thank you all for your answers

Re: User session query

service_apms — Thu, 09 Jan 2020 09:35:37 GMT

Is there a way to order the result
i got the results i wanted but not in the right order Monday then thursday ...

Re: User session query

jonathan_schull — Tue, 14 Jan 2020 16:17:47 GMT

Unfortunately using the "E" input sorts the days alphabetically. There are a few things we can do to get closer to the display that you are looking for, but unfortunately it is not possible to sort days by days of the week on a rolling scale that considers what day it currently is. This would be a good thing to put in an RFE, to possibly put into Dynatrace at a later date.

I can think of 2 workarounds for this.

Instead of using "DATETIME(starttime, "E", "1d") " use "DATETIME(starttime, "e", "1d") ". This will display the days of the week as numbers as opposed to the spelled out day of the week. Meaning when sorted it will always display the days in order Sunday-Saturday. This may be a bit confusing when the current day is in the middle of the week, and being displayed in the middle of the chart.
You could also change the query to include MIN(starttime) like below:

SELECT DATETIME(starttime, "E", "1d"), AVG(duration), MIN(starttime)
FROM useraction 
WHERE name is "reprise de devis"
GROUP BY DATETIME(starttime, "E", "1d")
ORDER BY MIN(starttime)

If you use this, the results will be displayed sorted by week day, and on a rolling scale that considers the current day. The drawback of using this method, is that it will add in 2 bars to the chart for each day, displaying "MIN(starttime)"

Cheers,

Jonathan