Creating a Green Dashboard – Part 1

Using tools to gather external logins to the Citrix Farm

There are a variety of ways to distinguish internal and external users of your Citrix farm.  The method we employ is to utilize the logging that’s part of any Citrix Secure Ticket Authority (STA) in your Citrix Farm.  You can turn logging on your designated STA’s by following the information in this Citrix article: CTX101997.  Turning this on gives us the following data in logs (located in %PROGRAMFILES%\Citrix\Logs\)

INFORMATION 2009/05/20:00:13:22 CSG1305 Request Ticket - Successful. A995AD36B87524A208BB23A804AC3110 V1 CSGTestData Thisistheextendeddata
INFORMATION 2009/05/20:00:13:22 CSG1303 Ticket timed out. A8478127C7971E4CD95C28FFD2B85BBE
INFORMATION 2009/05/20:00:13:23 CSG1305 Request Ticket - Successful. FF985D4B11DA3AE7B6CBBAA9CA833415 V1 CSGTestData Thisistheextendeddata
INFORMATION 2009/05/20:00:13:23 CSG1303 Ticket timed out. CDE1751C367B45506481C26727C3E6C1
INFORMATION 2009/05/20:00:13:23 CSG1305 Request Ticket - Successful. 19078A551F501BCC0F77E7361EE76CAD V1 CSGTestData Thisistheextendeddata
INFORMATION 2009/05/20:00:13:23 CSG1303 Ticket timed out. 414CB490647B8A2FCE023D66E7D0850E
and so on.
You will need to parse for a line like the following:
INFORMATION 2009/05/20:00:13:24 CSG1305 Request Ticket - Successful. 5C6C67EB127CFDB0821DC88CA1C10972 V4 CGPAddress = XXX.XXX.XX.XXX:2598:localhost:1494 Refreshable = false XData = <!--DOCTYPE CtxConnInfoProtocol SYSTEM "CtxConnInfo.dtd"-->XXX.XXX.XX.XXX:1494USER@DOM.COMRemote Desktop AccessICA ICAAddress = XXX.XXX

From the above line we can get the ticket status, the username, the published application, and the target server that hosts the application. When this is parsed and placed in a database, we can associate a time and date with the ticket creation and determine how long the user is logged in and what applications they are running.

To accomplish the data gathering, we use tools from InterSect Alliance like Epilog Agent for Windows to tail the stalog files.  This raw data is then sent to a server running Kiwi SysLog.  Kiwi parses the data (using a script) and then inserts it into a database table.  We’ve found these tools to be inexpensive and have a low resource utilization.

So, to sum up we have external users connecting to our Citrix farm and STA logs generating when they connect, what they run, and what server they connect to.  We parse the logs into a database and that gives us a real-time/historical record of the user’s use of our Citrix farm.    Next post will cover gathering average telecommuting statistics from the Internet.


Creating a Green Dashboard – Introduction

Green, the Green Economy, Green-collar jobs…

Green is in the news a lot lately.  Its importance has risen as the economy has slowed and the promise of various virtualization technologies to be “green” or allow a company to be greener has fired up the marketing wings of the companies that sell these technologies.  There are also a lot of ROI and savings reports that come along touting the economic advantages of virtualization.  A recent article by Virtualization Rock Star Brian Madden notes the hidden costs of VDI, which can be applied to any virtualization endeavor. 

So, you run a Citrix farm and provide access to remote users.  Did you know that you’re contributing to the green economy and saving your users and company money?  I intend on showing you how to mesh your user login information along with average costs and savings for telecommuting and displaying the results in a dashboard in real-time.

I’m planning on covering this series in 5 posts:

  • Part 1 – Using scripts to gather external logins to the Citrix Farm
  • Part 2 – Gathering average telecomuting statistics
  • Part 3 – Calculating savings and costs
  • Part 4- Displaying how Green your Citrix farm is in real-time.
  • Part 5 – Results and Conclusion


Making Citrix Stats Work for You – part 6

This post is part of a 6 part series. Jump to [part 1] [part 2] [part 3] [part 4] [part 5].

To sum up how we got here:

  1. We used PowerShell to gather some specific session stats from Citrix MFCom and output them to a text file. [part 1] [part 2]
  2. We then created a database and table to hold this data. [part 3]
  3. We created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]
  4. We showed how to save the Visual Studio job directly to an MS SQL server and have that server run a job to insert new data on a periodic basis. [part 4]
  5. We constructed a web page using MS SQL Reporting Services to present the data in real-time. [part 5]

For this final post, I want to add another piece of information to my report (which I’ll refer to as a dashboard from here on out).  I also will upload the report to the reporting services server so others can view the information.

We currently already show the currently disconnected users in the farm (last 5 minutes or so).  I also want to show the highest number of disconnected users for the day.  One quick way is to create a view based on our original data.  If we use the following query…

SELECT TOP (100) PERCENT CONVERT(varchar(5), msgdatetime, 108) AS Hour, COUNT(DISTINCT CONVERT(varchar, UserName))  AS Count
FROM dbo.UserSessions
WHERE (msgdatetime >= (datediff(d,0,getdate()))) AND (CONVERT(varchar, SessionState) LIKE '%5%')
GROUP BY CONVERT(varchar(5), msgdatetime, 108)

…we get a listing of total, unique disconnected users ordered by every 5 minutes for the current day.  To create a view in MS SQL,  I’ll open MS SQL Server Management Studio, login, open up our Syslog database and right-click on Views  and select “New View…”

A view is just a persistent query.  I’ll select the table we’re going to use and plug in the above query.

Saving it will name the view and make it available to future queries.  Now we’ll add a new field to our report that will show the largest number of disconnected users for the current day and base the information on our new view.  First, I’ll add a new text box…

and a new table object by clicking on Toolbox and dragging a table into the report.

Remove the footer and a couple of columns and do some editing…

Now we’ll add a new dataset by clicking on Data and selecting new Dataset on the drop down list.

I’ll name the dataset MaxDisconnected and use the following query which just selects the maximum value (of disconnected sessions) from the view.

select max(count) as MaxDisconnected
from VW_UserSessState_5


Now we have the new dataset. I’ll click on Datasets and Layout and simply drag the MaxDisconnected dataset into the table I created earlier.

So, the report is complete.  I want this report to automatically update every 5 minutes.  Do to this, I’ll click on the Report Menu and select Report Properties.  I’ll simply click on Autorefresh and set it to 300 seconds…

Now to import it to the reporting services server, we simply open our browser to the reporting services site:

Click on Upload file and browse to the .RDL file.

Name the report and click OK.  Now the report will show up on the web page.  Clicking on it will result in a failure, because we still have to associate a data source.  Click on Properties and Data Sources.  Set the data source, click OK, hit apply, and then view.  The report will now render and will autoupdate every 5 minutes so will show the most recent data.

Thanks for viewing.  I hope this gives you a basic introduction to using several tools to present data in a simple way (even though the method may be complicated).  As a system administrator/engineer  you (hopefully) typically have a reporting tool of some sort, but giving superiors or other teams access to it may prove to be more difficult or confusing than intended.  Using SQL Reporting Services allows you to show data in a simple form to a wider audience.

Please feel free to ask questions/comment. The methods described in this series have worked well in our environment, but I’d love to hear what others are doing to place metrics, stats, and such front and center.