topic Re: How can I query the number of unique users over a time period in Dashboarding

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

barry_duggan — Fri, 03 Jul 2020 13:54:08 GMT

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?

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

michael_oxendin — Mon, 06 Jul 2020 17:38:27 GMT

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

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

christian_gusta — Tue, 07 Jul 2020 10:09:54 GMT

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

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

barry_duggan — Tue, 07 Jul 2020 11:13:54 GMT

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