Data Access methods

The following examples show how to export Monitor Service data using the OData API. This topic also provides a list of URLs for available data sets.

Access using Raw XML

1. Place the URL for each data set into a web browser that is running with the appropriate administrative permissions for the XenApp and XenDesktop Site. Citrix recommends using the Chrome browser with the Advanced Rest Client add-in.

2. View the source.

Access using PowerPivot with Excel

1. Install Microsoft Excel.

2. Follow the instructions here to install PowerPivot (depending on whether or not you are using 2010 or 2013): https://support.office.com/en-us/article/Start-Power-Pivot-in-Microsoft-Excel-2013-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8.
3. Open Excel (running with the appropriate administrative permissions for the XenApp and XenDesktop Site).

Access using Excel 2010

1. Click the PowerPivot tab.

2. Click PowerPivot Window.

3. Click From Data Feeds in the ribbon.

4. Choose a Friendly Connection Name (for example: XenDesktop Monitoring Data) and enter the data feed URL: http://{ApiGatewayEndpoint} (or https: if you are using SSL).

   Note: For your region specific API gateway endpoint (for example, US region: https://api.cloud.com/monitorodata), see Supported API Gateway endpoints.

5. Click Next.

6. Select the tables you want to import into Excel and click Finish. The data is retrieved.

Access using Excel 2013

1. Click the Data tab.

2. Choose From Other Sources > From OData Data Feed.

3. Enter the data feed URL: http://{ApiGatewayEndpoint} (or https: if you are using SSL) and click Next .

4. Select the tables you want to import into Excel and click Next.

5. Accept name defaults or customize names and click Finish.

6. Choose Connection Only or Pivot Report. The data is retrieved.

You can now use PowerPivot to view and analyze the data with PivotTables and PivotCharts. For more information, see the Learning Center: http://www.microsoft.com/en-us/bi/LearningCenter.aspx.

Access using MS Excel PowerQuery

Power Query is a data transformation and data preparation engine.

Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, such as PowerBI and Excel.

Learn from the following example to access the Citrix Monitor Service data using MS Excel PowerQuery.

1. Open Excel (Excel 2016 has PowerQuery inbuilt. If you are using earlier versions of Excel, install PowerQuery. For more information, see Microsoft documentation).

2. In Excel 2016, go to Data menu > Get & Transform group > New Query > From Other Sources > click Blank Query

   

3. In the Query Editor window, go to Home menu > Query group > click Advanced Editor.

   

4. In the Advanced Editor window, type the following query:

    let
        // please replace the CUSTOMER_ID_PLACE_HOLDER with your own
        customerId = "CUSTOMER_ID_PLACE_HOLDER",
        // get citrix cloud API credential, please replace the tokenUrl based on your region
        tokenUrl = "https://api.cloud.com/cctrustoauth2/root/tokens/clients",
        headers = [
            #"customerid" = customerId,
            #"Content-Type" = "application/x-www-form-urlencoded",
            #"Accept" = "*/*"
        ],
   
        // please replace the CLIENT_ID_PLACE_HOLDER and CLIENT_SECRET_PLACE_HOLDER with your own
        postData = [
            grant_type = "client_credentials",
            client_id = "CLIENT_ID_PLACE_HOLDER",
            client_secret = "CLIENT_SECRET_PLACE_HOLDER"
        ],
   
        response = Json.Document(Web.Contents(tokenUrl, [Headers = headers, Content = Text.ToBinary(Uri.BuildQueryString(postData))])),
        // get the CC bearer toekn from the response
        token = "CwsAuth bearer=" & response[access_token],
    
        apiHeaders = [
            #"Authorization" = token,
            #"Citrix-CustomerId" = customerId
        ],
    
        currentDateTime = DateTime.LocalNow(),
        targetDate = Date.AddDays(currentDateTime, -1),
        formattedTargetDate = DateTime.ToText(targetDate, "yyyy-MM-ddThh:mm:ssZ"),
   
        // please replace the API_URL_PLACE_HOLDER with the actual API URL, for example https://api.cloud.com/monitorodata/Sessions
        Source = OData.Feed(
            "API_URL_PLACE_HOLDER?$filter=ModifiedDate gt @yesterday",
            null,
            [
                Query=[#"@yesterday" = formattedTargetDate],
                Headers=apiHeaders])
    in
        Source  
    <!--NeedCopy-->
   

   

   Replace CUSTOMER_ID_PLACE_HOLDER, CLIENT_ID_PLACE_HOLDER, CLIENT_SECRET_PLACE_HOLDER and API_URL_PLACE_HOLDER with your actual values. Once the No Syntax errors have been detected is displayed in the bottom of the window, click Done.

   If you do not need the filter operation, remove them.

        Source = OData.Feed(
        "API_URL_PLACE_HOLDER",
        null,
        [Headers=apiHeaders])
    <!--NeedCopy-->
   

5. In case of first login, a specify how to connect message is displayed. Click Edit Credentials, the Access an OData feed window appears. Make sure that the Anonymous tab and the base URL are selected and click Connect.

6. Upon successful authentication, the results are displayed as a table. In this example, the table lists all the Sessions of the CustomerId modified after yesterday. If the authentication fails, either the client_id, client_secret or the CustomerId might be invalid. Verify the same and try again.

Access using LinqPad

1. Download and install the latest version of LinqPad from http://www.linqpad.net.

2. Run LinqPad with the appropriate administrative permissions for the XenApp and XenDesktop Site.

Tip: the easiest way is to download, install and run on the Delivery Controller

1. Click the Add connection link.

   a. To use the OData v3 endpoint, choose WCF Data Services 5.1 (OData 3) and click Next .

   b. To use the OData v4 endpoint the first time, click View More Drivers, choose the OData V4 Driver, click the Download and Enable driver link. This adds the Odata 4 driver to the list of available drivers. Subsequently, you can select OData 4 and click Next.

2. Enter the data feed URL: http://{ApiGatewayEndpoint} (or https: if you are using SSL). If necessary, enter the username and password to access the Delivery Controller. Click OK.

3. You can now run LINQ queries against the data feed and export the data as needed. For example, right-click Catalogs and choose Catalogs.Take(100). This returns the first 100 Catalogs in the database. Choose Export>Export to Excel with formatting.

Access using Client Library

Currently Citrix Monitor Service supports OData protocol V3 and V4. So, when implement the OData consumers with various programming platforms, please select correct client libraries.

Access using C#/.NET

Calling an OData Service From a .NET Client (C#)

https://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v3/calling-an-odata-service-from-a-net-client

Code Fragment:

    /* GET http://{ApiGatewayEndpoint}/Catalogs */
    private static string ListAllCatalgs(MonitorService.DatabaseContext context)
    {
    StringBuilder sb = new StringBuilder();
    Foreach (var c in context.Catalogs)
    {
        sb.Append(DisplayCatalog(c));
    }
    return sb.ToString();
    }
<!--NeedCopy-->

    /* GET http://Url/Machines()?$select=Name, IPAddress */
    private static void ListMachineNames(MonitorService.DatabaseContext context)
    {
        var machines = from m in context.Machines select new { Name = m.Name, IP = m.IPAddress };
        foreach (var m in machines)
        {
            if (m.Name != null && m.IP != null)
            {
                Console.WriteLine("{0} : {1}", m.Name, m.IP);
            }
        }
    }
<!--NeedCopy-->

    /* use the LINQ Skip and Take methods to skips the first 40 results and takes the next 10 */
    /* GET http://{ApiGatewayEndpoint}/Machines()?$orderby=Id desc&$skip=40&$top=10 */
    private static void ListMachinesPaged(MonitorService.DatabaseContext context)
    {
        var machines =
            (from m in context.Machines
             orderby m.Id descending
             select m).Skip(40).Take(10);

        foreach (var m in machines)
        {
            Console.WriteLine("{0}, {1}", m.Name, m.IPAddress);
        }
    }

<!--NeedCopy-->

    /* GET http://Url/Catalogs()?$filter=Name eq '$Name'*/
    private static void ListCatalogByName(MonitorService.DatabaseContext context, string name)
    {
        var catalog = context.Catalogs.Where(c => c.Name == name).SingleOrDefault();
        if (catalog != null)
        {
            DisplayCatalog(catalog);
        }
    }
<!--NeedCopy-->

Access using Java

Calling an OData Service from a Java Client based on Odata4j v0.3 library:

http://www.odata.org/libraries/

http://odata4j.org/v/0.3/javadoc/

Code Fragment:

// create consumer instance
String serviceUrl = "http://{ApiGatewayEndpoint}";
ODataConsumer consumer = ODataConsumer.create(serviceUrl);
<!--NeedCopy-->

// ================General Query================================
Enumerable<String> qEntitySets = consumer.getEntitySets();
System.out.println(qEntitySets.first().toString());

Enumerable<OEntity> qList = consumer.getEntities(qEntitySets.first()).execute();
System.out.println(qList.first().toString());

OEntity qEntity = qList.first();
System.out.println(qEntity.getProperties().get(0));

OProperty<?> qProperty = qEntity.getProperties().get(0);
System.out.println(qProperty.getName());
System.out.println(qProperty.getType());
System.out.println(qProperty.getValue());
<!--NeedCopy-->

// =================Filter Query===========================================
/* GET http://{ApiGatewayEndpoint}/Machines */
String entitySetName = "Machines";
qList = consumer.getEntities(entitySetName).execute();
System.out.println(qList.first().toString());

/* GET http://{ApiGatewayEndpoint}/Machines()?$select=Name, IPAddress */
qList = consumer.getEntities(entitySetName).select("Name,IPAddress").execute();
System.out.println(qList.first().toString());

/* GET http://{ApiGatewayEndpoint}/Machines()?$orderby=Id desc&$skip=40&$top=10 */
qList = consumer.getEntities(entitySetName).orderBy("Id desc").skip(2).top(10).execute();
System.out.println(qList.first().toString());

/* GET http://{ApiGatewayEndpoint}/Machines()?$filter=Name eq '$Name'*/
qList = consumer.getEntities(entitySetName).filter("Name eq 'DOMAIN\\HOSTNAME'").execute();
System.out.println(qList.first().toString());
<!--NeedCopy-->

Access using PowerShell

Learn from the following example to access the Citrix Monitor Service data using PowerShell. This is a sample OData query triggered from PowerShell with the headers initialized and the Raw XML output redirected to a file:

```powershell

PS C:\> $headers = @{"Authorization" = "<BearerToken>”; "Citrix-CustomerId" = "<Your Customer Id>"}

PS C:\> $url = https://{ApiGatewayEndpoint}/Users

PS C:\> $result = Invoke-WebRequest -Uri $url -Headers $headers

PS C:\> $result.Content > <Path-to-Output-File>

<!--NeedCopy--> ```

Access using any REST API tool

Learn from the following example to access the Citrix Monitor Service data using any REST API tool, for example Postman Chrome Ext.

Postman is an API platform for building and using APIs. Postman simplifies each step of the API lifecycle and streamlines collaboration so you can create better APIs—faster.

1. Install Postman from Chrome extensions. See https://chrome.google.com/webstore.
2. Launch the application.
3. Select GET in the dropdown and type in the URL to request.

1. Insert headers with your token and customerId. Click Send.
2. If authentication is successful, response data is seen in the Response section of Postman.

Please read Monitor Service OData Postman Collection.

Access using Python script

Learn from the following example to access the Citrix Monitor Service data using Python script with Requests library.

import requests

url = "https://{ApiGatewayEndpoint}/Machines"

payload = {}
headers = {
  'Authorization': '{YourToken}',
  'Citrix-CustomerId': '{YourCustomerId}'
}
response = requests.request("GET", url, headers=headers, data=payload)
print(response.text)
<!--NeedCopy-->

Replace {YourCustomerId} and {YourToken} with the values of CustomerID and bearer token you. Make sure that the {YourToken} is in the format, CWSAuth bearer= {bearer token}.