20 Mar 2020 10:04 AM
Hello,
recently I import custom geolocation to dynatrace, every one of then contains '-' in name, so its possible to filter by using 'city like "%-%"' clausule. My goal is to make chart where on x axis is time and every location is separate line - like splitting.
This SQL is part of the goal - it display data spliting by location, but no time is included.
SELECT city, AVG(userActionCount), AVG(duration), COUNT(*), SUM(totalErrorCount) FROM usersession WHERE useraction.application='xxx' and city like "%-%" GROUP BY city ORDER BY COUNT(*) DESC
This SQL has time on x axis, but all localization is group as one metric:
SELECT DATETIME(startTime,"HH:mm",'1m') as "mytime" ,AVG(useraction.visuallyCompleteTime) FROM usersession WHERE useraction.application='xxx' and city like "%-%" GROUP BY mytime ORDER BY mytime ASC
In naive approach I could make seperate SQL for every location - 'city like "loc-1"' and make 100 seperate dashlets.
My goal is to make SQL with makes all this in one chart.
---------------------------------------------------------------------------------
Part of this can be achieved by create calculated metric(new feature).
For example visuallyComplete is achievable, but more complicated cases, like count user action is impossible to make by this way.
Best Regards
Michał
16 Apr 2020 02:48 PM
There are some limitations with using the User Session SQL. I would recommend posting this as a RFE so the User Session SQL team will be able to review your use case and possibly build in a solution to this 🙂