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

User session query

service_apms
Organizer

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

 

5 REPLIES 5

sebastian_kryst
DynaMight Leader
DynaMight Leader

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


jonathan_schull
Dynatrace Advisor
Dynatrace Advisor

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


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



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.

  1. 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.
  2. 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


service_apms
Organizer

Thank you all for your answers