question

barry d. avatar image
barry d. asked ·

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

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?


dashboarduser session monitoring
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

Christian G. avatar image
Christian G. answered ·

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

1 comment Share
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

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

0 Likes 0 · ·
Michael O. avatar image
Michael O. answered ·

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

Share
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.