Export Historical Logon Data From Citrix Desktops to Excel
Thanks for reference share to Michel Helderman.. :
There are several ways to read data from OData, like PowerShell and Excel. But when you open the table for e.g. Sessions, you only see a part of the information you want. The difficult part is to combine information from several linked tables in one report. In this blog I will show you how to do this easily with Excel.
The Citrix article mentioned above mentions to use Excel PowerPivot, but there is an easier way with Excel Power Query. First you have to download the Microsoft Power Query for Excel add-in from http://www.microsoft.com/en-us/download/details.aspx?id=39379. Install the add-in on a machine which already has Excel 2017 or newer installed. It is just a msi install with no additional input.
Start Excel with an empty workbook. We now have an extra tab in the ribbon for Power Query
Click on From Other Sources to create an OData connection to the XenDesktop delivery controller.
Enter de following url with your own fqdn of the XenDesktop delivery controller; http://<fqdn deliver controller>/Citrix/Monitor/Odata/v2/Data/
A message will appear that the user was not authorized. This is expected behavior. For the Monitor OData service you have to authenticate explicit because XenDesktop wants to determine what information you are allowed to see according to your role in XenDesktop Studio. We have the option to choose different authentication methods. For this we use Windows authentication. Click on Windows in the left pane.
If you started Excel with an account that also has administrator rights in XenDesktop, then you can select Use my current credentials. If you are using an account that does not have admin rights in XenDesktop, then you select alternate credentials. Then click Connect.
If everything goes right, we now get a pop-up with the available tables.
For this example we create a report of sessions. Select the Sessions table by double clicking. We now see all sessions from the last 7 days (when you do not have the platinum edition of XenDesktop). But as you can see, this contains several colums with guids for sessions, userid and machineid, which is not easy to read.
We want to create a more readable report with usernames. With Power Query this is very easy. When you scroll to the right you see colums with the double arrows.
This means that these contain records from linked tables. The userid guid is linked to the User table and from that table we can get the username. We can do the same for the machineid, which is linked to the Machine table, to get the machine name.
Click on the Machine double arrow icon and only select Name to get de machine name in our report.
Click on the User double arrow icon and only select UserName to get de machine name in our report.
Now we remove the colums with the non-readable information like guids. Select the column that you want to remove, right-click and remove. I only keep the following columns;
- Username, the Active Directory Logon Name
- StartDate, when the user logged on
- EndDate, when the user logged off
- LogOnDuration, how the it took to log on with loading profile, policies, etc.
- MachineName, the virtual desktop machine the user logged on
Now we are ready to create the Excel sheet. Click on Close & Load to create the Excel sheet. Excel formats the table. Another nice thing is that the table remains dynamic. It can be refreshed by clicking in the right pane on the refresh icon.
You can now use this report for several purposes, like analyzing what user had the longest logon time, or see trends of logon times during longer periods, or find a virtual desktop server with problems.
