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

How can I query the number of unique users over a time period

barry_duggan
Newcomer

Id like to create a query that will show me the number of unique users per day.

I would like this data displayed in a line chart

The query that I tried is as follows


SELECT DATETIME(starttime, 'MM-dd') , COUNT(Distinct(internalUserId)) FROM usersession where userType = 'REAL_USER' and internalUserId IS NOT NULL and useraction.application = 'My App Name'   GROUP BY DATETIME(starttime, 'MM-dd')  ORDER BY DATETIME(starttime, 'MM-dd') LIMIT 5000


The issue I have is that when trying this I get an error stating:

Using distinct counts within date/time aggregations is not yet supported!


My query works correctly if I remove the DISTINCT selector but the count value seems unrealistically high. This is probably because a single user id can occur in multiple sessions.


How can I get a result from dynatrace that will allow me to plot the number of unique users over a linear time period?


3 REPLIES 3

michael_oxendin
Dynatrace Helper
Dynatrace Helper

Hi Barry,


My first thought would be to user the UserID rather than the internaluserID, as that seems to provide me with more reasonable results.


SELECT DATETIME(starttime, 'MM-dd') , COUNT(userID) FROM usersession where userType = 'REAL_USER' and internalUserId IS NOT NULL and useraction.application = My app GROUP BY DATETIME(starttime, 'MM-dd') ORDER BY DATETIME(starttime, 'MM-dd') LIMIT 5000


Thanks,

Michael Oxendine

christian_gusta
Dynatrace Helper
Dynatrace Helper

Hi Barry,


Yes, sadly this is not supported yet. The only way to get this is by removing the dateTime function and just use the startTime, but then you will not get them bucketed by day.


SELECT starttime, COUNT(Distinct(internalUserId)) FROM usersession where userType = 'REAL_USER' and internalUserId IS NOT NULL and useraction.application = 'My App Name' GROUP BY starttime LIMIT 5000

If we Omit Distinct and just use IS NOT NULL will there be duplicate counts for internalUserID ?

Featured Posts