VBScript to Report Application Usage via MFCOM

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.

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"

Join the conversation

13 comments

Send me notifications when other members comment.

Please create a username to comment.

This message was originally posted by agressiv on September 17, 2004
Don't forget to create the LogonTime Column. I'm assuming its another smalldatetime. I might come up with another version of this where you can get some more data out of it - but this gets me going :)

Thanks -

agressiv
Cancel
This message was originally posted by Ravi Pannem on September 20, 2004
SQL Server Version is 2000 not SQL2003
Like agressiv suggested, LogonTime Column (smalldatetime-data type)should be created when creating the database table.
Cancel
This message was originally posted by an anonymous visitor on September 20, 2004
Thank you!
Cancel
This message was originally posted by an anonymous visitor on September 28, 2004
are citrix going to release an SDK for MPS3.0, or will the existing versions work.
Cancel
This message was originally posted by Ctna on September 30, 2004
Despite I had to see that logontime wasn't in your first text this script / solution is quite nice. Thanks
Cancel
This message was originally posted by DHarding on December 2, 2004
This is a full 'long winded' version for SQL 2000 which might help some problematic scripts.

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=SQLSERVERNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WOEKSTATIONID;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=farmUsage"

But V.Nice Job
Cancel
It's a great script.
But we are running all applications in one session. In the output of this script i see only the application name that was started first. Is there a solution for this?

Cancel
If you are using a published application, which in fact is a batch file or a VBscript that launches applications on the server, this script will not work.
Key is you must see the session information in CMC for this script to grab that info from mfcom.
-Ravi Pannem
Cancel
Is there any way I can use this script to track usage for a certain application rather than all of them?
 
Thanks
 
JG
Cancel
What script are you talking about?  The forum doesn't show what article this was in reference to.

Shawn
Cancel
Simplest way would be to limit your results to that application by adding a criteria to the view that is created.  Thanks.
 
Chris
Cancel
If the script is run once an hour will it capture short sessions that have occured in the last hour?
Cancel

Is there a way to get everyone who opened the app during the day or within a certain time periode, without running the script every hour? Something like running it once a week and get a report for usage of an app for the whole week.


Cancel

-ADS BY GOOGLE

SearchVirtualDesktop

SearchEnterpriseDesktop

SearchServerVirtualization

SearchVMware

Close