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

[barry_duggan]

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?

[michael_oxendin · Accepted solution]

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 · Accepted solution]

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

[barry_duggan]

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