01 May 2019 05:20 PM
I am trying to use the API to get real user counts per application. I have tried the following but it isn't matching what the console is showing. Can someone shed some light on this? It seems I am getting a fraction of the userids and some of the ones that are anon it is returning those as 'null'
select usersession.userid FROM usersession where usersession.userType = 'REAL_USER' AND useraction.application = 'My Awesome Application' GROUP BY usersession.userid
Solved! Go to Solution.
01 May 2019 05:36 PM
It looks like the API is capping at 50 rows.
01 May 2019 08:34 PM
I believe from my testing the maximum rows that can be returned is 5000. So, adding a "LIMIT 5000" to the end of the USQL request would be required.
Kind regards,
Joshua Pavlica
01 May 2019 08:38 PM
Yes I was able to get it to return the data although if I tell it to give me the count of userids in the query, the number doesn't match the user count from the User session console. I am thinking I don't have a filter or it is ignoring the timeframe filter because each time I run the query the number changes.
Query I am trying:
startTimestamp=1556733966561&endTimestamp=1556737361768&query=select count(userid) FROM usersession where usersession.userType = 'REAL_USER' AND useraction.application = 'My Awesome App' LIMIT 5000
01 May 2019 08:40 PM
Hey Robert!
The USQL and User Sessions API only work for completed/finished sessions, whereas the in-UI User Sessions page will also show "Live"/in-progress sessions.
If your timestamp is recent enough, this could be the reason why you are seeing differences.
Kind regards,
Joshua Pavlica
01 May 2019 09:04 PM
Yes, I set the startime to be 0 so that it will give me the last 2 hours and I changed the UI to show me the last 2 hours? I am trying to get the unique users that have access my website in the last hour. The same data that shows up when I click on the User Sessions in the UI. You are saying though that the API will only give me the completed correct? I am just trying to get a count of how many users have been to my website in the last 2 hours. So this would be the count of users that have completed sessions and current live users.
01 May 2019 09:34 PM
Live sessions are most commonly seen during the last 2 hours, so that is correct that the USQL results will be different by only having the completed sessions displayed.
Kind regards,
Joshua Pavlica
01 May 2019 09:43 PM
Is there any way in the API to get live session data? If I am only counting completed sessions, I am not really getting the true count of users that have been or are still on the website in the last 2 hours. The UI gives me this information.
01 May 2019 10:00 PM
Not using the USQL API, no. There is an upcoming improvement in the next quarter or so to include a "user count" for Web Applications via the Timeseries API. This might help with your use-case.
Kind regards,
Joshua Pavlica
01 May 2019 10:20 PM
Thank you for that information! What I probably will end up doing is just checking the numbers once a day at a time when there shouldn't be much traffic on the website. That at least will give me a good ball park of users that used the system during the day.
01 May 2019 10:21 PM
You're very welcome, Robert!
And sounds good!
Kind regards,
Joshua Pavlica
30 Sep 2022 04:51 PM
Greetings Joshua,
I'm currently trying to get the same exact data as Robert, I was checking the Timeseries API but I couldn't understand how this can be possible using it.
I was also reading and find out that there's an "extrapolationLevel" field that according to the documentation, it means:
The number is the denominator of a fraction and indicates the amount of actual data. The value 1 means that the result contains only the actual data. The value 4 means that result is calculated using 1/4 of the actual data.
How can I change this value in order to be set to 1 since I've tried passing it on the params request but it seems like it is not mutable.
Here's an example of my results:
Request:
import requests
headers = {
'accept': 'application/json',
'Authorization': 'Api-Token dt0c01.xxxxxx-xxxxxxx',
}
params = {
'query': 'SELECT COUNT(DISTINCT ip) AS \'distinct_ip\' FROM usersession WHERE (userType=\'REAL_USER\' AND (useraction.application=\'sandals.com\' OR userevent.application=\'sandals.com\' OR usererror.application=\'sandals.com\')) LIMIT 1000',
'startTimestamp': '3600000',
'addDeepLinkFields': 'false',
'explain': 'false',
}
response = requests.get('https://hudxxxxx.live.dynatrace.com/api/v1/userSessionQueryLanguage/table', params=params, headers=headers)
print(response.json())
Response:
{
"extrapolationLevel": 4,
"columnNames": [
"distinct_ip"
],
"values": [
[
1489990
]
]
}
According to my Session Query results, the "values" should be around 500-600ish
Can you please help me!
Regards,
01 May 2019 07:59 PM
Yep I found where you can set this: The framework always applies an upper limit to prevent system overload. If LIMIT
is not used, 50 results are returned by default.