I like the concept of EdgeSight but it is a beast to maintain and I rarely use the Performance Metrics. What I use most often is Application Usage reports.
I loved using the old Resource Manager for creating Application and/or User Usage reports. I miss that basic function since I have been using XenApp 6.x for a few years now. EdgeSight was supposed to give me this but it does not since I am at the Enterprise Licensing tier for XenApp.
There are a few commercially avaliable apps out there that are similar to the old Resource Manager, but being on a tight budget, I could never afford them. I could have upgraded my XenApp licensing to Platinum, but again, budgeting constraints prevented this.
I sat down and looked through the XenApp 6.x SDK to see what I could capture and dump into a Database. I created a very basic Database and wrote the attached script and reports as a substitute. I have been running this solution for a couple of months now and I think it provides the basics of what I need. While not as nice and robust of a solution as EdgeSight or other solutions, it fits the budget. The only thing I need to work on is when Applications are deleted from the Farm they are still retained in the Database.
I schedule the Powershell Script, "XenAppStatsCollect.ps1", to run every five minutes, so there are some sessions I may miss. I run the script on at least two XenApp servers. I just stagger when they run by 3 minutes.
Attached is a ZIP file that contains the Query to create the Database and the Tables, the Powershell script to populate the Database and some SQL Server Report Definition Files.
Getting it to work
First step, create the Database. Open the SQL Server Management Console and logon to the instance you want to host the Database. I run mine in an Instance that allows for Mixed Mode Authentication, both Windows and SQL. You want an Instance that at least allows for Windows Authentication.
Click on "New Query" and paste the contents of "CreateDatabase.sql.txt" into the New Query and then click Execute. This will create a new Database named "XenAppResourceManager". Add the account to the Database Security that will run the script to populate the Database. This account will need to be a XenApp Administrator and an owner of the Database.
Next, install the XenApp 6 SDK on the XenApp Servers you want to run the Powershell Script from. You can find the Citrix XenApp PowerShell SDK here: http://community.citrix.com/display/xa/XenApp+6+PowerShell+SDK
Next, copy the script to each XenApp Server you want to run it from and schedule it to run every five minutes. Use the account you defined in the Database Security to run the Task. You will need to set the Execution Policy to unrestricted (set-executionpolicy -executionpolicy unrestricted).
You will need to edit the script and set your SQL Server name in the $SQLServer variable ($SQLServer = "SQLServerName").
Go ahead and test running the script so it populates some data into the Database. Application Inventory will be populated regardless if there are any sessions.
Assuming you have a SQL Reporting Server instance running, you can now create a SQL Server Report Server Project for the Report Definition Files. Open SQL Server Business Intelligence Development Studio, click on File -> New -> Project.
Select the "Report Server Project" template.
In the "Name" field, type "Citrix XenApp 6.x Reports".
In the "Location" field, set it where you want to save the Project.
In the Solution Name Field should auto-populate "Citrix XenApp 6.x Reports".
Click OK.
Copy the Report Definition Files in the ZIP to the location you created. As in the example in the screenhot, copy them to the "\\<Servername>\SSRS\Citrix XenApp 6.x Reports\Citrix XenApp 6.x Reports\Citrix XenApp 6.x Reports" folder.
In the "Solution Explorer" pane in SQL Server Business Intelligence Development Studio, right click on "Reports" and then click on Add -> "Existing Item".
You should see the RDLs, select them all and then click "Add.
Next, you will need to change the Data Source of each report to point to your SQL Server. Open the first report, Application Inventory, expand "Data Source".
Right click on "XenAppRM" and then click on "Properties".
In the connection string, replace "czerno-sql" with your SQL Server name and click OK.
If you have already ran the Powershell script you should have Data to work with. Click on "Preview" to confirm the report works.
Repeat the process for each report.
Once you have made the Data Source changes you now need to configure the Report Project to upload the reports into the Reports Server. Right click on Citrix XenApp 6.x Reports in the solution explorer, then click on "Properties".
In the "TargetServerURL" enter in your Report Server like this: http(s)://<servername>/reportserver and click on OK.
Now you should be able to Deploy the reports. Right click on Citrix XenApp 6.x Reports in the solution explorer, then click on "Deploy".
If the deployment was successful you can now browse to the report server and run the reports.
Open a browser and enter the URL for your report server; http(s)://<servername>/reports.
Here is a screenshot from the "Top N Apps"report.
I am not a very developer so I am sure the script could be written better. Let me know what you think about the solution and if you have a better way of doing this.
Link to download Zip file: http://www.czerno.com/default.asp?inc=/html/Citrix/Scripts/CreateDBandReportsAppusage.asp
EDIT 06-03-2014: I made a couple of changes. I noticed the script will bomb if the Folder Name was longer than 50 characters. I also had to change the sessions query to use the MAX() on the Duration when multiple applications were in use in one session. I updated the create DB SQL Script to change the fields from 50 to 255.
UPDATE 12-04-2014: Brad Payne had an issue with a PK conflict. He changed the Primary Keys to compensate. The Zip file has been updated to include his changes. Big thanks to Brad!
XenAppResourceManager.zip (105.9KB)
Blog Home
Archive
Contact Me
RSS Feed
Czerno.com Home