Creating a Green Dashboard – Part 2

In the first part of this series, we covered gathering data on external users and how to parse that data into a database.  To generate the data to show how your Citrix farm positively impacts the environment and quality of life for your teleworkers, you need to get the average statistics for them.  Governments gather this data as it pertains to their own telework programs.

The U.S. government site, telework.gov, provides a one-stop-shop for general information, but no hard data except for number of participants at the various agencies.  We need a site that will give us savings on money, fuel, and pollutants. 

The Telework Exchange has a lot of papers and studies, and it provides statisics for yourself, based on location and miles driven.

The most detailed information I’ve been able to find so far, is the Undress4Success site.  This site has quite of bit of information as well as links to job boards and details of “work at home” scams.  They also have done a lot of the work of analyzing many telework studies and making it available to visitors.

For this example dashboard, I’ll track gas saved and green house gas savings.

For gas saved in my geographic area, this is 2,847,724 gallons saved per year or approximately 177 gallons per teleworker (16,058) or roughly 0.50 gallons saved per person/per day teleworking.

Following the same method for greenhouse gases, 26,991 metric tons are saved annually.  Using Google to convert this to pounds, gives us 59,504,969 pounds saved.  This breaks down to 10 pounds saved per person/per day teleworking.

Using these amounts to start with we can show the amount of gas and greenhouse gas emissions someone is saving per day when they use our Citrix farm.   This is just a starting point, we could expand this to other cost savings for the teleworker and for the company as well which I’ll cover at the end of this series.

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 at %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 = 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.

Thanks,
Alain

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

Thanks,
Alain

Making Citrix Stats Work for You – part 6

customer information has been obscured in the following pictures

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)
ORDER BY Hour

…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.

Thanks,
Alain

Making Citrix Stats Work for You – part 5

customer information has been obscured in the following pictures

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. Following this, we created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]
4. Then, I  demonstrated 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]

Now that we have new data automatically entering our database table every 5 minutes or so, we can construct a web page using MS SQL Reporting Services to present the data in real-time.  For this example, we’ll create a real-time report that shows the currently disconnected users in my Citrix farm.

Open Visual Studio and open up a Report Server Project.

Now we’ll create a Shared Data Source to connect to our database server by right-clicking on Shared Data Sources and Add New Data Source.

We’re connecting to an MS SQL server, click Edit to enter the connection information, and choose the database we’ll query for our data.

Now we’ll add a report by right-clicking on Reports and (amazingly enough) select Add New Report

This will bring up the Report Wizard which we’ll follow for this example.  First, I’ll use the data source we’ve already created.  Since it’s already selected, we’ll click Next and move on to the query builder.  Here, we’ll create an SQL query that will gather the data we want to display in our report.  Click on Query Builder, then click on the Generic Query Designer button to bring up some QBE (query by example) tools.

Click on the Add Table button and select the table we want to query from.

So, for this example, I want query this table to get the number of disconnected users currently in my Citrix farm.  So, I’m looking for a session state of 5, I just want to count each user once, and I only need the last 5 minutes since this is the update interval of the table.  Here’s the query I’m using to get this data.

SELECT     COUNT(DISTINCT CONVERT(varchar, UserName)) AS Disconnected
FROM         UserSessions
WHERE     (SessionState LIKE ‘%5%’) AND (msgdatetime >= GETDATE() – .0031)

This gives us the information we need, so I’ll click OK and go back to the Query builder and click Next.  We’ll choose Tabular for the report type, click Finish, and name the report.  This takes us to the design view, where we can format the report.  Hitting the Preview tab let us see how the finished report will look (I made some minor layout changes and added some text).

For the final post in this series, we’ll create a database view so we can show the currently disconnected users and the maximum disconnected users for the current day and upload the finished report to our Reporting Services server for public viewing.

Thanks,
Alain

Making Citrix Stats Work for You – part 4

customer information has been obscured in the following pictures

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. Following this, we created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]

Now, I will show 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.

With your Visual Studio package open, go to File and select Save Copy of Package As…

Select SQL Server for package location, enter the server name, set the authentication type and credentials for the DB owner. 

Choose and name a location for the Package Path and hit OK.

Change the protection level to “Rely on server storage and roles for acces control” and click OK to save.

Now login the SQL server with Management Studio and open SQL Server Agent and Jobs.

Right-click on jobs and select New Job.  Select the Steps page and click new.

Hit the Type drop down and select SQL Server Integration Services Package.  Enter the server name and then look for the Package.  It will have the same name you gave it when you exported it to the SQL server.  Give the step a name and click OK.

Now click on schedules to run this job every 5 minutes.

Click OK, name the job and open the Job Activity Monitor to confirm the job kicks off.  If you get a Succeeded result, you can query the table to ensure it has got new data.  So now the SQL server is kicking off the Visual Studio data flow job to read a flat text file and enter the data into a table every 5 minutes.  Next post, we’ll pull the data out of the table in some meaningful ways and place it in MS SQL Reporting Services.

Peace,
WagTheReal

Making Citrix Stats Work for You – part 3

Author’s note: I’m renaming this series of articles to fit more with their direction…previous name was “Using PowerShell to gather Citrix stats”

customer information has been obscured in the following pictures

Okay put your propeller hats on …

As I mentioned in my last post, I am going to run the PowerShell script periodically to gather new data.  This is easily accomplished with a batch file and a Windows scheduled task.  Here’s the batch file:

@echo off
powershell.exe -noninteractive w:\qfarm\Count-CitrixSession.ps1
exit

I’m going to run this batch file every 3 minutes with Windows Scheduled Tasks.  It will take a minute to run, so we’ll get updated data about every 4 minutes.   To import this data, we will have to create a database/table to hold it.   For this case, I’m using an MS SQL 2005,  so I’ll open Management Studio.

Next, we’ll create a database and table (in this example, the database is already created).  The table will consist of a time/date stamp field and 4 fields that are from the output of the PoSH script.

The little bit of cleverness in this table lies with the msgdatetime column.  Its default value (which you set when you create the table) is the getdate() function in MS SQL.  That way, it will always get the current date and time that the row was created.  The other columns will be a plain text data type, which we will have to convert when we do certain queries.

Now we have the text file that’s being updated every 4 minutes (approximately) and a table to hold the data.  I’m going to use Visual Studio 2005 to actually do the import.   Run VS and create a new Integration Services Project.

Now drag a Data Flow Task into your design window.

Double-click the new Data Flow Task and drag over a flat file source (to read the text files) and an OLE DB Destination (to communicate with the database).

Double-click the Flat File Source and create a new Flat file connection manager.  Give the connection manager a name and click Browse to point it to the text file that the PoSH script is creating.

Now, we have to set the properties of the connection manager to correctly parse the text file.  This will allow easy import of the data into our database table.   In this case, we can use a semicolon as the column delimiter and {CR}{LF} as the row delimiter.  This gives us 4 columns with the username, the applicationame, the servername, and the session state.

Next, drag the green arrow from Flat File Source to OLE DB Destination.  Double-click on the OLE DB Destination and create a new OLE DB Connection manager.  Enter in the ODBC information to connect to the database and select the database we want to use.

Now we can select the table we created before.

Select Mapping to coorelate the fields in the text file with the fields in the table.

Now to test the data flow, click the green triangle in the tool bar or use the Debug menu and Start Debugging.  You should get confirmation that a number of rows were imported.  A quick query of the table in SQL can confirm that it has data.

Next post will deal with uploading the project to an SQL server and creating a schedule that will automatically run, thus importing new data into the database on a recurring schedule.

Peace,
WagTheReal

Making Citrix Stats Work for You – part 1

Author’s note: I’m renaming this series of articles to fit more with their direction…previous name was “Using PowerShell to gather Citrix stats”

Overview: There’s quite a bit of data that we can gather via queries to MFCom.  With this series I want to demonstrate how to use PowerShell, MS SQL, SQL Reporting Services and Visual Studio to gather real-time stats and present them in a dashboard that is easy to read and even easier to present to management.

I’m presenting today an edited PowerShell script that I grabbed from http://synjunkie.blogspot.com/2008/12/powershell-retrieving-useful-citrix.html.  Syn is also writing a hacking Citrix series of articles that is proving to be very useful.

Here’s the original script:

#Count-CitrixSession.ps1

1. count citrix sessions and other useful info

$farm = new-Object -com “MetaframeCOM.MetaframeFarm”
$farm.Initialize(1)

1. displays a list of published apps and the number of users on each

write-host “Total users on each citrix application” -fore yellow
$farm.sessions | select UserName,AppName | group AppName | Sort Count -desc | select Count,Name | ft -auto
$livesessions = ($farm.Sessions).count
write-host “The number of current citrix sessions is” $livesessions -fore red

write-host ” “

1. list of citrix servers and total number of sessions on each one

write-host “Total sessions on each citrix server” -fore yellow
$farm.sessions | select ServerName,AppName | group ServerName | sort name | select Count,Name | ft -auto

write-host ” “

1. To see which users have more than one session open

write-host “First 15 Users with more than one citrix session” -fore yellow
$farm.sessions | select UserName,AppName | group UserName | Sort Count -desc | select Count,Name -first 15 | ft -auto

This script is very useful, but I found that it took several minutes to run in our environment due to iterating through all our Citrix sessions 3 times.  To speed the script and ensure the data did not change as the script was running I loaded everything into an array and then replicated the data output.

$livesessions = 0
$disconnected = 0
$farm = New-Object -com “MetaframeCOM.MetaframeFarm”
$farm.Initialize(1)

1. Load Up Array for a snapshot of current sessions in CITGO

$sessionAry = @($farm.Sessions | select UserName,AppName,ServerName,SessionState)

foreach ($sess in $sessionAry) {
if ($sess.SessionState -eq “5″) {$disconnected = $disconnected + 1}
else {$liveessions = $livesessions++}
}

Write-Host “The number of active citrix sessions is” $livesessions -fore red
Write-Host “The numbrer of disconnected citrix sessions is” $disconnected -fore red

Write-Host ” “

1. displays a list of published apps and the number of users on each

Write-Host “Total users on top 20 citrix applications” -fore yellow
$sessionAry | group AppName | sort Count -desc | select Count,name -first 20 | ft -auto

Write-Host ” “

1. list of citrix servers and total number of sessions on each one

write-host “Total sessions on each citrix server” -fore yellow
$sessionAry | group ServerName | sort name | select Count,Name | ft -auto

write-host ” “

1. To see which users have more than one session open

write-host “First 20 Users with more than one citrix session” -fore yellow
$sessionAry | group UserName | Sort Count -desc | select Count,Name -first 20 | ft -auto

Now the script runs 3 times as fast.

Peace,
WagTheReal