By Benjamin Ruoff
I'm often asked by customers, if there is a simple way to get a farm usage report which displays the amount of active and disconnected users over a week, month etc.
In the past I said, "yes" I have a little PowerShell Script which will query the farm, calculate the effective active and disconnected users and write the output into a SQL table (Timestamp, Active Users, and Disconnected Users). With e.g. Excel you can connect to the SQL database and generate very easy a table and chart about the user sessions.
A few months later, an enhancement request arrived me. A customer told me, that he wants a farm report like the first PowerShell script but the user Sessions should be displayed per server and per published application.
I decided to write a new script with the ability to report the active and disconnected sessions per server and per published application in the console per default. In addition I added the ability for SQL logging by script argument.
Script runtime requirements:
• the name of the XenApp Zone Data Collector
• Installed XenApp PowerShell Commands.
• The User which executes the Script needs the Rights to query the Farm.
• The Zone Data Collector must have WMI remoting enabled.
If the requirements are met, the script runs and generate a console output. Per default, the display will refreshed every 5 minutes. Only servers or published applications with either active and/or disconnected users sessions appear in the command window.
To keep the data for historical reporting, the script can write the values into a SQL database.
SQL logging requirements are:
• one database on a Microsoft SQL Server
• two tables with the name “Applications” and “Servers”
• The Script have to run with the “-SQL” Switch (ActiveUsers_v2.1.ps1 -SQL)
• The variables “$DBServer” and “$DBDatabase” have to be specified
• The user which executes the Script need the permission to write into the database
The Table Applications need the following Columns:
The Table Servers need the following Columns:
In my example, I used the “Pivot Table and Graph Feature” of Microsoft Excel for reporting. I connected the SQL database and selected the fields as in the screenshot. I grouped the DateTime field so that I can easily expand an collapse months or days (very useful if its running for months).