by
Ravi Pannem
If you're looking for a way to track application usage that's less complex than Resource Manager, this article is for you! This simple VBScript collects information about farm sessions via MFCOM and exports it to a database table. Then, with the help of Crystal Reports (or the reporting engine of your choice), information can be presented in a nice format.
Please note that this article focuses on tracking application usage only. If you want to track CPU, memory, network and other system related metrics you still have to use Resource Manager.
This script is written on the following environment (although it should work almost anywhere):
- Citrix Metaframe XP Presentation Server with Feature Release 3
- Windows 2000 with SP4
- Citrix SDK 2.3
- SQL Server 2000 with SP3
- Crystal Reports 8.5
First, using SQL Server 2000, create a database called "FarmUsage" and a table called "farmSessions" with following parameters:
| Column Name |
Data Type |
Length |
Allow Nulls |
| Updatetime |
smalldatetime |
4 |
Y |
| Application |
varchar |
50 |
Y |
| ServerName |
varchar |
50 |
Y |
| Username |
varchar |
50 |
Y |
Once you get the database created, you can use run the following code to populate it. Save this code as a .VBS file and run it as a scheduled task on a zone data collector in your farm. (A ZDC is best suited for this purpose since it has up-to-date information about sessions, meaning it will take less time to collect the data.)
Set the scheduled task to run every hour and stop the task if it runs for more than 30 minutes. You can use scheduled task properties to suit need your needs. For example, if you only care about peak usage you can set the task to run between 8:00-5:00.
If you don't want to use ZDC for the scheduled task, you can install the Citrix SDK Version 2.3 on a standalone server (Which doesn't have be be running Metaframe) and point it to the ZDC of the farm you are interested in. To register MFCOM, simply type "mfreg servername" where servername is the ZDC of your farm.
And now, the code:
'==============================================
'Collect Session Information in a Citrix farm using Mfcom
'Export the results to a database table every hour as a scheduled Task
'==============================================
On Error Resume Next
Set objConnection=CreateObject("ADODB.Connection")
Set objRecordSet=CreateObject("ADODB.Recordset")
Const adOpenStatic=3
Const adLockOptimistic=3
Const adUseClient=3
Const Active=1
'**Replace SQLSERVERNAME with the name of your SQL Server**
'**Make sure User ID you use has dbo permission on the database**
connectstring = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=appid;Initial Catalog=farmUsage;Data Source=SQLSERVERNAME;Password=appid"
objConnection.open connectstring
objRecordSet.CursorLocation=adUseClient
objRecordSet.Open "Select * from farmSessions", ObjConnection,adOpenStatic,adLockoptimistic
Set farm=CreateObject("MetaframeCOM.MetaframeFarm")
farm.initialize 1
for each session in farm.sessions
Set Logon_Time=Session.LogonTime(True)
Session_Connected=Logon_Time.Month&"/"&Logon_Time.day&"/"&Logon_Time.Year&" "&Logon_Time.Hour&":"&Logon_Time.Minute
If session.sessionstate=Active Then 'Track Active Sessions only
objRecordSet.Addnew
objRecordSet("UpdateTime")=now
objRecordSet("Application")=session.appname
objRecordSet("ServerName")=session.ServerName
objRecordSet("UserName")=session.UserName
objRecordSet("LogonTime")=Session_Connected
objRecordSet.Update
End If
Next
'Wscript.Echo "DONE"
Once you create your script, you'll need to create a SQL view. To do this, copy the following code into SQL Query Analyzer and execute it by pressing F5.
USE FarmUsage
Go
CREATE VIEW dbo.vw_AppUsage
AS
SELECT UpdateTime, Application, ServerName, UserName, LogonTime
FROM dbo.farmsessions
WHERE (UpdateTime > DATEADD([day], - 30, GETDATE()))
If you want to use Crystal Reports to view this data, click here to download the template. This template includes a chart and reports application usage every hour. You can modify the SQL View or Crystal Report's filters to control the duration of data displayed.
You can extend the same idea to track application usage by global groups which cannot be done with Resource Manager natively. To do this, you have to modify the database table schema to accommodate the group name (add extra column for group name). Please note that a new crystal report must be created if you end up using the script below.
Note: This script might not be suited for concurrent user base exceeding 500 at each hour and collecting data 24/hrs a day. You might want to dump the groups information into a separate table once a day or once a week (depending how frequently users accounts change) and use SQL Queries to figure out the global group.
Here's the code for tracking by global group. This script assumes all your user accounts and global groups are in one domain and global groups have a standard naming convention and start with Citrix
'================================================================
'Collect Session Information Using Mfcom
'Export the results to a database table every hour
'================================================================
On Error Resume Next
Dim strGroupName, UserID
Set objConnection=CreateObject("ADODB.Connection")
Set objRecordSet=CreateObject("ADODB.Recordset")
Set oShell=CreateObject("Wscript.Shell")
Const adOpenStatic=3
Const adLockOptimistic=3
Const adUseClient=3
Const Active=1
'**Replace SQLSERVERNAME with the name of your SQL Server
'**Make sure User ID you use has dbo permission on the database
connectstring = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=appid;Initial Catalog=farmUsage;Data Source=SQLSERVERNAME;Password=appid"
objConnection.open connectstring
objRecordSet.CursorLocation=adUseClient
objRecordSet.Open "Select * from farmSessions", ObjConnection,adOpenStatic,adLockoptimistic
Set farm=CreateObject("MetaframeCOM.MetaframeFarm")
farm.initialize 1
for each session in farm.sessions
userid=session.username
call Find_Group(UserID)
Set Logon_Time=Session.LogonTime(True)
Session_Connected=Logon_Time.Month&"/"&Logon_Time.day&"/"&Logon_Time.Year&" "&Logon_Time.Hour&":"&Logon_Time.Minute
If Session.sessionstate=Active Then
objRecordSet.Addnew
objRecordSet("UpdateTime")=now
objRecordSet("Application")=session.appname
objRecordSet("ServerName")=session.ServerName
objRecordSet("UserName")=session.UserName
objRecordSet("GroupName")=strGroupName
objRecordSet("LogonTime")=Session_Connected
objRecordSet.Update
End If
Next
Function Find_Group(User) 'Return the GroupName for the userid
strUserName = userid
'Replace DomainName with name of your Domain
strDomain="DomainName"
Set objADSIUser = GetObject("WinNT://"&strDomain&"/" & strUserName & ",user")
If objADSIUser is nothing Then
'do nothing
Else
For Each group In objADSIUser.Groups
'If global group's name start with CITRIX, change the following line based on your environment
If Left(UCase(group.name), 6) = "CITRIX" Then
strGroupName= group.name
Exit For 'Exit the Function on first occurrence of GroupName starting with CITRIX
End If
Next
End If
Set objADSIUser = nothing
End Function
'Wscript.Echo "done"
(Note: You must be logged in to post a comment.)
If you log in and nothing happens, delete your cookies from BrianMadden.com and try again. Sorry about that, but we had to make a one-time change to the cookie path when we migrated web servers.