The XenApp and XenDesktop environment on Citrix Cloud gathers a large amount of historical data in the Monitoring Database. Until now, you could access some of this data via the Director Trends and Custom reports. Creating customized queries to directly access the db has been a much sought-after feature.
This month, we decided to bring it to you via the new OData API that enables you, as a XenApp and XenDesktop administrator, to create your own queries for the monitoring data to suit your requirements.
This API can be used to:
- Build custom analysis tools on historical trends for future planning.
- Create custom user interface on top of the data that the API provides.
- Aggregate the data to generate a consolidated report.
- Get customized reports in Excel PowerQuery and other tools.
The following example shows how you can use Postman REST client to query this endpoint.
Let’s walk through some sample use cases:
- Consider a situation where an administrator wants to retrieve the list of all the users taking more than 10 minutes to login. An OData query to get this data would look like this, where {ApiGatewayEndpoint} is Citrix API gateway URL for monitoring data. Please refer to the link in notes section for more details.
https://{ApiGatewayEndpoint}/Users?$filter=Sessions/any(session: session/LogOnDuration gt 600000)
A report generated using this query in Excel looks like:
- The highlight of this API is that it supports most of the data aggregation functions like min, max, and count; so you can get a consolidated result. Let’s say you want to build a simple UI that shows the number of sessions in connected, disconnected, and reconnecting states. Without the support of aggregate functions, you would have to query for each state for data. The new API facilitates grouping and aggregation based on required fields and reduces the number of requests to be made to get the result. An OData query for such request can be written as:
https://{ApiGatewayEndpoint}/Sessions?$apply=groupby((ConnectionState), aggregate(SessionKey with countdistinct as NumberOfSessions))
If queried using Excel’s PowerQuery, here is how the report looks.
Notes:
- As of November 2019, Monitor OData APIs support pagination. A maximum of 100 records per page is returned with a link to the next page. Refer to pagination doc for more info and examples on how to use this link to query the next batch.
- Details regarding authentication required to access this API and steps to access the data from clients such as MS Excel’s PowerQuery, C# client library, PowerShell are available at Access Monitor Service data using the OData v4 endpoint in Citrix Cloud.
- To get the most out of Monitor data, it’s a good idea to be familiar with the Database schema of Monitor Service. The database diagram can be found at Monitor Service Schema.
- OData’s release notes for version 4 can be found at http://docs.oasis-open.org/odata/new-in-odata/v4.0/cn01/new-in-odata-v4.0-cn01.html
- For more details on the supported API gateway endpoints, please refer to https://developer-docs.citrix.com/projects/access-monitor-service-data-citrix-cloud/en/latest/#supported-api-gateway-endpoints.
