Application and User Usage Database and Reports for XenApp 6.5

by Stan Czerno March 5, 2014 10:37 CST

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)

Tags: , ,

Catergories: Citrix | XenApp | Powershell

Comments (61) -

Lenoard
May 20, 2014 04:28 CST

Hi ! I'm  a citrix engineer. Yesterday, I saw the blog "Application and User Usage Database and Reports for XenApp 6.x". It's very useful. So I tryed it. Every steps gone well  except  executing "XenAppStatsCollect.ps1". The PS windows show error:
"PS C:\Users\Administrator> C:\Users\Administrator\Desktop\XenAppResourceManager\XenAppStatsCollect.ps1
Loading Citrix XenApp Commands
Connect to SQL Server Database
Succesfully Connected to SQL Server...
Clear the Temp Tables
Write Farm Info to FarmTemp Table
Update Farm Table from FarmTemp Table
Write Farm Session Count to SQL


Exception             : System.Management.Automation.MethodInvocationException: 使用“0”个参数调用“ExecuteNonQuery”
                        时发生异常:“从字符串转换日期和/或时间时,转换失败。” ---> System.Data.SqlClient.SqlException:
                         从字符串转换日期和/或时间时,转换失败。
                           在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnecti
                        on)
                           在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                           在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDa
                        taReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                           在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                           在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String met
                        hodName, Boolean sendToPipe)
                           在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           在 ExecuteNonQuery(Object , Object[] )
                           在 System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           --- 内部异常堆栈跟踪的结尾 ---
                           在 System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           在 System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String meth
                        odName, Object[] paramArray, Boolean callStatic, Object valueToSet)
                           在 System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] argument
                        s, Object value)
                           在 System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, Executi
                        onContext context)
                           在 System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, A
                        rray inputToProcess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :



Succesfully Closed connection to SQL Server...
PS C:\Users\Administrator>"

Reply

Lenoard
May 20, 2014 04:42 CST

Then, I used  'test01' to login webinterface and opened a session. After that, I login the website 'http://localhost/reports'  and checked the report of 'XenApp 6.5 Top N Users' , there is nothing.
Would you please help me with the problem ! Thank you!

Have a nice day!

Reply

Stan Czerno
May 20, 2014 10:17 CST

According to google translate the error has something to do with the date / time. "the string conversion date and / or time from the conversion failed"

So it probably has something to do with: $DatePolled = Get-Date -Format "MMM dd yyyy HH:mm"

You will need to mess with the format of the date/time in powershell or change the DB to match US standards.

Reply

Lenoard
May 22, 2014 03:54 CST

Hi Stan,
          I replaced Get-Date -Format "MMM dd yyyy HH:mm"  with Get-Date -Format "yyyy/M/d HH:mm".
Time format error was fixed, But in line 98 and 237, the command of "get-XAServer -Full" went wrong. It showed that

PS C:\Users\Administrator\Desktop\XenAppResourceManager> Get-XAServer -full
Get-XAServer : 索引超出了数组界限。
所在位置 行:1 字符: 13
+ Get-XAServer <<<<  -full
    + CategoryInfo          : NotSpecified: (Smile [Get-XAServer], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException,Citrix.XenApp.Commands.GetServerCmdlet

Reply

Lenoard
May 22, 2014 04:25 CST

Above all, I use "get-XAServer", it's OK. so I cut the parameter of '-full'. The PS went well.
Finally, All test was successful, But I have not figure it out yet, why can't i use 'get-XAServer' with '-Full'.
I appreciate that you made the ideas come true and shared with us all !

Reply

Stan Czerno
May 22, 2014 07:38 CST

I am not sure why it would not work for you.

If you just type the command "get-XAServer -full", what happens?

You could test it with something like the code below. This should echo each server's information.

$ServerInfo=get-XAServer -full
$ServerInfo | foreach {
$ServerName=$_.ServerName
$ServerIPAddress=$_.IPAddresses
$CitrixVersion=$_.CitrixVersion
$CitrixEdition=$_.CitrixEdition
$CitrixServicePack=$_.CitrixServicePack
$CitrixInstallDate=$_.CitrixInstallDate.ToString("MMM dd yyyy HH:mm")
echo " "
echo "Server Name: $ServerName"
echo "IP Address: $ServerIPAddress"
echo "Citrix Version: $CitrixVersion"
echo "Citrix Edition: $CitrixEdition"
echo "Citrix Service Pack: $CitrixServicePack"
echo "Citrix Install Date: $CitrixInstallDate"
echo " "
}

Reply

Lenoard
May 25, 2014 22:00 CST

I'm appreciate that you reply to the question. Compare the wrong to right, it echo below that

Sorry about some chinese string, The OS can not echo english noramlly. Please use google translate.

PS C:\Users\Administrator> Get-XAServer


ServerName              : WIN-1PGCI1R26C0
ServerFqdn              :
ServerId                : 00C2-000C-000000F6
FolderPath              : Servers
ZoneName                : 默认区域
ElectionPreference      : MostPreferred
IPAddresses             : {10.10.1.112}
OSVersion               : 6.1.7601
OSServicePack           : Service Pack 1
Is64Bit                 : True
CitrixProductName       : Citrix Presentation Server
CitrixVersion           : 6.5.6682
CitrixEdition           : Advanced
CitrixEditionString     : ADV
CitrixServicePack       : 0
CitrixInstallDate       : 2014/5/21 22:26:12
CitrixInstallPath       : C:\Program Files (x86)\Citrix\
LicenseServerName       : 10.10.1.207
LicenseServerPortNumber : 27000
LogOnsEnabled           : True
IsSpoolerHealthy        :
LogOnMode               : AllowLogOns
PcmMode                 : Normal
IcaPortNumber           : 1494
RdpPortNumber           :
SessionCount            :
MachineName             : WIN-1PGCI1R26C0



PS C:\Users\Administrator> Get-XAServer -full
Get-XAServer : 索引超出了数组界限。
所在位置 行:1 字符: 13
+ Get-XAServer <<<<  -full
    + CategoryInfo          : NotSpecified: (Smile [Get-XAServer], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException,Citrix.XenApp.Commands.GetServerCmdlet

Reply

Stan Czerno
May 26, 2014 07:33 CST

It looks like the Citrix cmdlet itself is not working correctly.

Is this server a member of a Domain or is it a stand-alone server? I did a quick google search and found where the same problem was observed with a non-domain server.

Reply

Lenoard
May 27, 2014 03:58 CST

yes ,the server is a non-domain server.    I really want to learn powershell scripts,would you mind give me some advice.

Reply

Stan Czerno
May 27, 2014 06:54 CST

It seems that the "get-XAServer -full" only works on Domain Joined machines, not stand-alone.

My only advice with learning Powershell is to either read the TechNet documentation, blogs, etc. or purchase a Powershell book.

Reply

Pete
September 30, 2014 14:38 CST

Nice script - works perfectly and the data is appearing in the database. However, when running the ps script, we get the following:

Get-XASession : An error occurred getting session details for session 3 (0x80040004)
At C:\IBM_Support\XenAppStatsCollect.ps1:179 char:26
+ $Sessions = Get-XASession <<<<  -full  | where-object {
    + CategoryInfo          : InvalidResult: (3:String) [Get-XASession], CitrixException
    + FullyQualifiedErrorId : GetDetailedSessionInformation,Citrix.XenApp.Commands.GetSessionCmdlet

Running on XA6.5 RUP03 and is a non-session only server and the Get-XASession - full command works outside of the script.

Any pointers greatfully receieved Smile - thanks Pete

Reply

Stan Czerno
October 1, 2014 11:24 CST

Not sure what to make of that error. What are the details of the session?

Reply

Plamen
October 15, 2014 02:24 CST

One big thank you for the great work you've done Stan !!!!

Just a small note from me - our farm names are more than 10 character long, so if any one sees a message about Truncating just check if your farm name is longer than that, and if so edit the sql that creates the database and replace the value 10 to whatever number is suitable for you ( replace all occurrences )

Thank you Stan again !

BR,
Plamen

Reply

Stan Czerno
October 15, 2014 06:59 CST

Thanks! Good to know about the Farm Name being more than 10 characters, I will have to update the script.

Reply

Plamen
October 31, 2014 12:11 CST

Hello Stan,

I've been using the script for some time now and I am impressed of the results! Two days ago I had to move it to another server for some reasons, and decided to check the log file, for my surprise here is what I've found :

  
.....
......

Write Application info from Sessions to ApplicationUsageTemp Table
Update ApplicationUsage Table from ApplicationUsageTemp Table
Update Process Table from ProcessTemp Table
[ERROR]

Exception             : System.Management.Automation.MethodInvocationException:
                         Exception calling "ExecuteNonQuery" with "0" argument(
                        s): "The MERGE statement conflicted with the FOREIGN KE
                        Y constraint "fk_Process". The conflict occurred in dat
                        abase "XenAppResourceManager", table "dbo.SessionData".
                        The statement has been terminated." ---> System.Data.Sq
                        lClient.SqlException: The MERGE statement conflicted wi
                        th the FOREIGN KEY constraint "fk_Process". The conflic
                        t occurred in database "XenAppResourceManager", table "
                        dbo.SessionData".

-----
---

Skipped some lines that to me seem to be out of interest. Then I started digging around the error as it was present in each run for different table. Here is what I found :

stackoverflow.com/.../is-this-a-bug-in-merge-failing-to-implement-foreign-key-properly

In two words - Yes it is a bug in MERGE  and it is not fixed even in the 2012 SQL

As I am not very good with the queries may be there is a way of doing it with some workaround.  Any ideas are welcomed !

Stan thank you again !

BR,
Plamen

Reply

Stan Czerno
October 31, 2014 12:27 CST

I will have to mess around with it and see if I can reproduce to find a workaround.

Reply

Stan Czerno
October 31, 2014 12:39 CST

When you say you moved it to another machine, did you disable the task on the original machine?

Reply

Stan Czerno
October 31, 2014 15:10 CST

OK, so I think the issue is my script and not MERGE. I need to do a better job of catching exceptions before writing data. There is data that doesn't exist in SessionData that is trying to be written into the Process table

What I would do is this:

First, backup the Database.

Second, delete any records in each table whose timestamps or logontime is after you changed machines.

Now run the script again and see if it completes successfully.



I need to add better logging (to a file) and send an alert via email when errors occur.

Reply

Plamen
November 3, 2014 02:12 CST

Hello Stan,

I had to clear out that the error log was from the original machine.

Regarding the task - yes I did stop it Smile.

And when I run the script on the new server again the error is present. I think in the beginning when tables were empty there was no such error but probably after some time when data is accumulated it is reaching to some kind of exception.

The thing is that the error is not persistent for same table but rather would come for different tables each time.

I can send you the complete error log if you thing that would help you. Even I will send you some of those with the error present for different table.

This is for now.

Thank you again !!!

BR,

Plamen



Reply

Stan Czerno
November 3, 2014 09:00 CST

Go ahead and send me the log file.

Can you restore the DB prior to moving servers?

Reply

Thiago Paula
November 25, 2014 06:36 CST

Hello guys,

Could send me the updated scripts? I made the implementation in an environment but I'm getting the date wrong. I made several changes to the system date and still is not writing any data in the database. Could you help me?

Reply

Stan Czerno
November 25, 2014 08:03 CST

You will need to set the Date Time format to whatever your localized setting is. If you run DBCC USEROPTIONS in a query in the SQL Management Studio it will return what your "dateformat" is set to.

To see the "dateformat" for all languages use "select * from sys.syslanguages".

The part of the script you need to change is: $DatePolled = Get-Date -Format "MMM dd yyyy HH:mm".

I do not what your SQL Server is set to but you could try one of these:

$DatePolled = Get-Date -Format "yyyy MMM dd HH:mm

$DatePolled = Get-Date -Format "yyyy/M/d HH:mm".



Reply

Thiago Paula
November 25, 2014 09:40 CST

The database language is us_english and made the change to the two options that you sent and keep getting the same error. I'll enter it below for details.

This Server is part of a Domain
Connect to SQL Server Database
Succesfully Connected to SQL Server ...
Clear the Temp Tables
Write Farm Info to FarmTemp Table
Update Farm Table from FarmTemp Table
Write Farm Session Count to SQL
Write Server Info to ServerInfoTemp Table
[ERROR]

Exception: System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" wit
                         h "0" argument (s): "Conversion failed When converting date and / or team from character string."
                         ---> System.Data.SqlClient.SqlException: Conversion failed When converting date and / or team fro
                         m character string.
                            at System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnecti
                         on)
                            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (TdsParserStateObject stateObj)
                            at System.Data.SqlClient.TdsParser.Run (RunBehavior runBehavior, SqlCommand cmdHandler, SQLDA
                         taReader Datastream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                            at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds (String methodName, Boolean async)
                            at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (DbAsyncResult result, String met
                         hodName, Boolean sendToPipe)
                            at System.Data.SqlClient.SqlCommand.ExecuteNonQuery ()
                            at ExecuteNonQuery (Object, Object [])
                            at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke (Object target, Object []
                         arguments, MethodInformation methodInformation, Object [] originalArguments)
                            --- End of inner exception stack trace ---
                            at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke (Object target, Object []
                         arguments, MethodInformation methodInformation, Object [] originalArguments)
                            at System.Management.Automation.ParserOps.CallMethod (Token token, Object target, String meth
                         odName, Object [] Paramarray, Boolean callStatic, Object valueToSet)
                            at System.Management.Automation.MethodCallNode.InvokeMethod (Object target, Object [] argument
                         s, Object value)
                            at System.Management.Automation.MethodCallNode.Execute (Array input, Pipe outputPipe, Executi
                         onContext context)
                            at System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch (ParseTreeNode ptn, The
                         rray inputToProcess)
TargetObject:
CategoryInfo: NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId: DotNetMethodException
ErrorDetails:
InvocationInfo: System.Management.Automation.InvocationInfo
PipelineIterationInfo: {}
PSMessageDetails:

Reply

Stan Czerno
November 25, 2014 12:31 CST

Remark the following line and run the script again:

$CitrixInstallDate=$_.CitrixInstallDate.ToString("MMM dd yyyy HH:mm")

Reply

Thiago Paula
December 1, 2014 12:21 CST

Thank you. The report is running and write correctly in the database.

Reply

Plamen
November 28, 2014 05:10 CST

Hello Stan,

I have played a bit more with the script because I needed reporting on per server usage. What I have found is that the column Logon Duration in the Sessiondata table is in this format 00:00:00 - but actually a user's session can last more than 24 hours, and the maximum reported in that coloumn now is 23:59:59. But we are having users with session time of several days Smile ( not good but these are our users we cannot change that ). Just decided to share this !

Best Regard,

Plamen

Reply

Brad Payne
December 4, 2014 19:15 CST

Hi Stan -
Great utility! Really, really nice work, and I really appreciate the fact that you took the time to share and create this tutorial page. One note from my first go with it is that one of my larger 6.5 farms (must be a bug) has sessions launched simultaneously from the same client machine spawning a session on two XenApp servers at the same second. You get primary key infractions on dumping data from the SessionDataTemp table to the SessionData table, because the three column primary key SessionName,ClientName,LogonTime are the same for both of these sessions (ICA#1 for the first session on the box, and obviously ClientName, and LogonTime).

I found this really early in my testing, so I dropped the DB, and update the sql script for the db creation to make the primary key on the SessionData table include the ServerName field, because this actually is a unique instance of a session. Added ServerName field to the Process and ProcessTemp tables, updated the foreign key relationships, and then just updated the Powershell script to populate the servername fields in the temp tables, and in the dump from temp to permanent tables, and I'm off and running.

I just thought I should share this small tweak since you took the time to share.

Reply

Stan Czerno
December 4, 2014 19:21 CST

Thanks Brad. Good catch and solution. Again, I am not a very good developer or DBA. Smile

I was actually thinking about this a few weeks ago, though, when trying to troubleshoot an error above. I do not use this in a high traffic farm so I may have never seen this error myself.

Can you share your updated scripts? I will be sure to give you full credit for the changes.

Reply

Brad Payne
December 4, 2014 20:18 CST

Don't under estimate yourself Smile  Going to try posting in this comment:

SQL:

USE master;
GO
CREATE DATABASE XenAppResourceManager;
GO
USE XenAppResourceManager;
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Farm
(
FarmName nvarchar(10) NOT NULL
CONSTRAINT pk_Farm PRIMARY KEY(FarmName)
)
GO
ALTER TABLE dbo.Farm SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.FarmTemp
(
FarmName nvarchar(10) NOT NULL
  )
GO
ALTER TABLE dbo.FarmTemp SET (LOCK_ESCALATION = TABLE)
GO


CREATE TABLE dbo.FarmSessionCount
(
DatePolled datetime NULL,
FarmSessionCount smallint NULL,
FarmName nvarchar(10) NOT NULL
CONSTRAINT fk_FarmSessionCount FOREIGN KEY(FarmName) REFERENCES Farm(FarmName)
)
GO
ALTER TABLE dbo.FarmSessionCount SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ServerInfo
(
ServerName nvarchar(50) NOT NULL,
ServerIPAddress nvarchar(50) NULL,
CitrixVersion nvarchar(50) NULL,
CitrixEdition nvarchar(50) NULL,
CitrixServicePack nvarchar(50) NULL,
CitrixInstallDate datetime NULL,
CONSTRAINT pk_ServerInfo PRIMARY KEY(ServerName)
)
GO
ALTER TABLE dbo.ServerInfo SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ServerInfoTemp
(
ServerName nvarchar(50) NOT NULL,
ServerIPAddress nvarchar(50) NULL,
CitrixVersion nvarchar(50) NULL,
CitrixEdition nvarchar(50) NULL,
CitrixServicePack nvarchar(50) NULL,
CitrixInstallDate datetime NULL,
)
GO
ALTER TABLE dbo.ServerInfoTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.Users
(
AccountName nvarchar(50) NOT NULL,
CONSTRAINT pk_Users PRIMARY KEY(AccountName)
)
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.UsersTemp
(
AccountName nvarchar(50) NOT NULL,
)
GO
ALTER TABLE dbo.UsersTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.SessionData
(
AccountName nvarchar(50) NOT NULL,
FarmName nvarchar(10) NOT NULL,
ServerName nvarchar(50) NOT NULL,
SessionName nvarchar(10) NOT NULL,
Clientname nvarchar(50) NOT NULL,
ClientVersion nvarchar(50) NOT NULL,
ClientAddress nvarchar(50) NOT NULL,
LogonTime datetime NOT NULL,
LogonDuration nvarchar(10) NOT NULL
CONSTRAINT pk_SessionData PRIMARY KEY(ServerName,SessionName,Clientname,LogonTime)
CONSTRAINT fk_SessionData FOREIGN KEY(FarmName) REFERENCES Farm(FarmName), FOREIGN KEY(ServerName) REFERENCES ServerInfo

(Servername), FOREIGN KEY(AccountName) REFERENCES Users(AccountName)
)
GO
ALTER TABLE dbo.SessionData SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.SessionDataTemp
(
AccountName nvarchar(50) NOT NULL,
FarmName nvarchar(10) NOT NULL,
ServerName nvarchar(50) NOT NULL,
SessionName nvarchar(10) NOT NULL,
Clientname nvarchar(50) NOT NULL,
ClientVersion nvarchar(50) NOT NULL,
ClientAddress nvarchar(50) NOT NULL,
LogonTime datetime NOT NULL,
LogonDuration nvarchar(10) NOT NULL
)
GO
ALTER TABLE dbo.SessionDataTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.Applications
(
ApplicationName nvarchar(50) NOT NULL,
ApplicationType nvarchar(50) NULL,
FolderPath nvarchar(255) NULL,
Enabled nvarchar(50) NULL,
HideWhenDisabled  nvarchar(50) NULL,
ContentAddress nvarchar(50) NULL,
CommandLineExecutable nvarchar(MAX) NULL,
WorkingDirectory nvarchar(MAX) NULL,
AnonymousConnectionsAllowed nvarchar(50) NULL,
AddToClientStartMenu nvarchar(50) NULL,
ClientFolder nvarchar(255) NULL,
StartMenuFolder nvarchar(50) NULL,
AddToClientDesktop nvarchar(50) NULL,
ConnectionsThroughAccessGatewayAllowed nvarchar(50) NULL,
OtherConnectionsAllowed nvarchar(50) NULL,
AccessSessionConditionsEnabled nvarchar(50) NULL,
AccessSessionConditions nvarchar(50) NULL,
InstanceLimit smallint NULL,
MultipleInstancesPerUserAllowed nvarchar(50) NULL,
CpuPriorityLevel nvarchar(50) NULL,
AudioType nvarchar(50) NULL,
AudioRequired nvarchar(50) NULL,
SslConnectionEnabled nvarchar(50) NULL,
EncryptionLevel nvarchar(50) NULL,
EncryptionRequired nvarchar(50) NULL,
WaitOnPrinterCreation nvarchar(50) NULL,
WindowType nvarchar(50) NULL,
ColorDepth nvarchar(50) NULL,
TitleBarHidden nvarchar(50) NULL,
MaximizedOnStartup nvarchar(50) NULL,
OfflineAccessAllowed nvarchar(50) NULL,
CachingOption nvarchar(50) NULL,
AlternateProfiles nvarchar(50) NULL,
RunAsLeastPrivilegedUser nvarchar(50) NULL,
Servers nvarchar(MAX) NULL,
WorkerGroups nvarchar(MAX) NULL,
Users nvarchar(MAX) NULL,
CONSTRAINT pk_Applications PRIMARY KEY(ApplicationName)
  )
GO
ALTER TABLE dbo.Applications SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ApplicationsTemp
(
ApplicationName nvarchar(50) NOT NULL,
ApplicationType nvarchar(50) NULL,
FolderPath nvarchar(255) NULL,
Enabled nvarchar(50) NULL,
HideWhenDisabled  nvarchar(50) NULL,
ContentAddress nvarchar(50) NULL,
CommandLineExecutable nvarchar(MAX) NULL,
WorkingDirectory nvarchar(MAX) NULL,
AnonymousConnectionsAllowed nvarchar(50) NULL,
AddToClientStartMenu nvarchar(50) NULL,
ClientFolder nvarchar(255) NULL,
StartMenuFolder nvarchar(50) NULL,
AddToClientDesktop nvarchar(50) NULL,
ConnectionsThroughAccessGatewayAllowed nvarchar(50) NULL,
OtherConnectionsAllowed nvarchar(50) NULL,
AccessSessionConditionsEnabled nvarchar(50) NULL,
AccessSessionConditions nvarchar(50) NULL,
InstanceLimit smallint NULL,
MultipleInstancesPerUserAllowed nvarchar(50) NULL,
CpuPriorityLevel nvarchar(50) NULL,
AudioType nvarchar(50) NULL,
AudioRequired nvarchar(50) NULL,
SslConnectionEnabled nvarchar(50) NULL,
EncryptionLevel nvarchar(50) NULL,
EncryptionRequired nvarchar(50) NULL,
WaitOnPrinterCreation nvarchar(50) NULL,
WindowType nvarchar(50) NULL,
ColorDepth nvarchar(50) NULL,
TitleBarHidden nvarchar(50) NULL,
MaximizedOnStartup nvarchar(50) NULL,
OfflineAccessAllowed nvarchar(50) NULL,
CachingOption nvarchar(50) NULL,
AlternateProfiles nvarchar(50) NULL,
RunAsLeastPrivilegedUser nvarchar(50) NULL,
Servers nvarchar(MAX) NULL,
WorkerGroups nvarchar(MAX) NULL,
Users nvarchar(MAX) NULL,
   )
GO
ALTER TABLE dbo.ApplicationsTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ApplicationUsageTemp
(
ApplicationName nvarchar(50) NOT NULL,
AccountName nvarchar(50) NOT NULL,
LogonTime datetime NOT NULL,
ServerName nvarchar(50) NOT NULL,
SessionName nvarchar(10) NOT NULL,
Clientname nvarchar(50) NOT NULL,
  )
GO
ALTER TABLE dbo.ApplicationUsageTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ApplicationUsage
(
ApplicationName nvarchar(50) NOT NULL,
AccountName nvarchar(50) NOT NULL,
LogonTime datetime NOT NULL,
ServerName nvarchar(50) NOT NULL,
SessionName nvarchar(10) NOT NULL,
Clientname nvarchar(50) NOT NULL,
CONSTRAINT fk_ApplicationUsage FOREIGN KEY (ServerName,SessionName,Clientname,LogonTime) REFERENCES SessionData

(ServerName,SessionName,Clientname,LogonTime), FOREIGN KEY(ApplicationName) REFERENCES Applications(ApplicationName), FOREIGN KEY

(ServerName) REFERENCES ServerInfo(ServerName), FOREIGN KEY(AccountName) REFERENCES Users(AccountName)
  )
GO
ALTER TABLE dbo.ApplicationUsage SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ProcessTemp
(
Processname nvarchar(50) NULL,
CreationTime datetime NULL,
ProcessId int NULL,
ServerName nvarchar(50) NOT NULL,
LogonTime datetime NOT NULL,
SessionName nvarchar(10) NOT NULL,
ClientName nvarchar(50) NOT NULL,
)
GO
ALTER TABLE dbo.ProcessTemp SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.Process
(
  Processname nvarchar(50) NULL,
  CreationTime datetime NULL,
  ProcessId int NULL,
  LogonTime datetime NOT NULL,
  ServerName nvarchar(50) NOT NULL,
  SessionName nvarchar(10) NOT NULL,
  ClientName nvarchar(50) NOT NULL,
CONSTRAINT fk_Process FOREIGN KEY (ServerName,SessionName,Clientname,LogonTime) REFERENCES SessionData

(ServerName,SessionName,Clientname,LogonTime)
)
GO
ALTER TABLE dbo.Process SET (LOCK_ESCALATION = TABLE)
GO

CREATE TABLE dbo.ServerSessionCount
(
ServerName nvarchar(50) NOT NULL,
ServerSessionCount int NOT NULL,
DatePolled datetime NOT NULL
CONSTRAINT fk_ServerSessionCount FOREIGN KEY(ServerName) REFERENCES ServerInfo(ServerName)
)
GO
ALTER TABLE dbo.ServerSessionCount SET (LOCK_ESCALATION = TABLE)
GO


COMMIT

Reply

Brad Payne
December 4, 2014 20:24 CST

PowerShell Code:

## This script uses PowerShell and the Citrix XenApp PowerShell SDK
## community.citrix.com/.../XenApp+6+PowerShell+SDK

cls
####=====================================####
#### Define your SQL Server and Database #####
####=====================================####

$SQLServer = "<SERVERNAME>"
$DatabaseName = "XenAppResourceManager
\"

"Loading Citrix XenApp Commands"
if ((Get-PSSnapin "Citrix.*" -EA silentlycontinue) -eq $null) {
  try { Add-PSSnapin Citrix.* -ErrorAction Stop }
  catch { write-error "Error loading XenApp Powershell snapin"; Return } }

$DatePolled = Get-Date -Format "MMM dd yyyy HH:mm"
$time1 = Get-Date
$tminus = $time1.addminutes(-1440)

# Reads the current directory path from the location of this file
$currentDir = Split-Path $MyInvocation.MyCommand.Path

# Log File
$logfile = Join-Path $currentDir ("XenAppStatsCollect.log")

#==============================================================================================
# This function will log the data / result into a logfile as well as in the powershell window.
# The data is sent to function as parameter or a piped command output.
#==============================================================================================
Function LogMe()
{
    Param( [parameter(Mandatory = $true, ValueFromPipeline = $true)] $logEntry,
     [switch]$display,
     [switch]$error,
     [switch]$warning
     #[switch]$progress
     )
    if($error) { Write-Host "$logEntry" -Foregroundcolor Red; $logEntry = "[ERROR] $logEntry" }
  elseif($warning) { Write-Host "$logEntry" -Foregroundcolor Yellow; $logEntry = "[WARNING] $logEntry"}
  #elseif ($progress) { Write-Host "$logEntry" -Foregroundcolor Blue; $logEntry = "$logEntry" }
  elseif($display) { Write-Host "$logEntry" -Foregroundcolor Green; $logEntry = "$logEntry" }
    else { Write-Host "$logEntry"; $logEntry = "$logEntry" }

  $logEntry | Out-File $logFile -Append
}

#==============================================================================================
# This function will Run the SQL Query.
#==============================================================================================
function RunQuery
{
    param ($CommandText)
        try
        {
        $Write2DB = $SQLConnection.CreateCommand()
        $Write2DB.CommandText = $CommandText
        $Write2DB.ExecuteNonQuery()>$null
        }
        catch [System.Data.SqlClient.SqlException]
            {
            $error[0]|format-list -force | out-string | LogMe -error
            $SQLConnection.Close()
                if ($SQLConnection.State -eq "Closed") { "Succesfully Closed connection to SQL Server..."; EXIT }
                else  { "Failed to close Connection to SQL Server..." | LogMe -error; EXIT }
            }
        catch
            { "An error occurred while attempting to open the database connection and execute a command." | LogMe -error
            $SQLConnection.Close()
                if ($SQLConnection.State -eq "Closed") { "Succesfully Closed connection to SQL Server..."; EXIT }
                else { "Failed to close Connection to SQL Server..." | LogMe -error; EXIT }
            }
    $CommandText = $null
}

#==============================================================================================
# Remove Log File
#==============================================================================================
rm $logfile -force -EA SilentlyContinue

#==============================================================================================
# Check to see if machine is part of a domain
# Parts of the Citrix Powershell CMDLETS will not work on stand-alone machines
#==============================================================================================

if ((gwmi win32_computersystem).partofdomain -eq $true) { "This Server is part of a Domain" | LogMe -display }
else { "This Server is NOT part of a Domain!" | LogMe -error; "This script will not work unless the Server is joined to a Domain" | LogMe -error; EXIT }

#==============================================================================================
"Connect to SQL Server Database" | LogMe -display
#==============================================================================================
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLServer; Initial Catalog=$DatabaseName; Integrated Security=SSPI")
$SQLConnection.Open()

if ($SQLConnection.State -eq "Open")
            { "Succesfully Connected to SQL Server..." | LogMe -display }
            else
            {  "Connection to SQL Server Failed...." | LogMe -error; EXIT }

#==============================================================================================
"Clear the Temp Tables" | LogMe -display
#==============================================================================================
$CommandText = @"
TRUNCATE TABLE dbo.SessionDataTemp
TRUNCATE TABLE dbo.ServerInfoTemp
TRUNCATE TABLE dbo.ApplicationsTemp
TRUNCATE TABLE dbo.ApplicationUsageTemp
TRUNCATE TABLE dbo.ProcessTemp
TRUNCATE TABLE dbo.UsersTemp
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Farm Info to FarmTemp Table" | LogMe -display
#==============================================================================================
$FarmInfo=Get-xafarm
$FarmName=$Farminfo.FarmName
$CommandText = @"
INSERT INTO dbo.FarmTemp (FarmName)
VALUES ('$FarmName')
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Update Farm Table from FarmTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE Farm Data USING FarmTemp Temp
ON Data.FarmName = Temp.FarmName
WHEN NOT MATCHED THEN INSERT (FarmName)
VALUES (Temp.FarmName);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Farm Session Count to SQL" | LogMe -display
#==============================================================================================
$FarmName=$Farminfo.FarmName
$SessionCount=$FarmInfo.SessionCount
$CommandText = @"
INSERT INTO dbo.FarmSessionCount (DatePolled,FarmSessionCount,FarmName)
VALUES ('$DatePolled', '$SessionCount','$FarmName')
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Server Info to ServerInfoTemp Table" | LogMe -display
#==============================================================================================
$ServerInfo=get-XAServer -full
$ServerInfo | foreach {
$ServerName =$_.ServerName
$ServerIPAddress=$_.IPAddresses
$CitrixVersion=$_.CitrixVersion
$CitrixEdition=$_.CitrixEdition
$CitrixServicePack=$_.CitrixServicePack
$CitrixInstallDate=$_.CitrixInstallDate.ToString("MMM dd yyyy HH:mm")
$CommandText = @"
INSERT INTO dbo.ServerInfoTemp (ServerName,ServerIPAddress,CitrixVersion,CitrixEdition,CitrixServicePack,CitrixInstallDate)
VALUES ('$ServerName','$ServerIPAddress','$CitrixVersion','$CitrixEdition','$CitrixServicePack','$CitrixInstallDate')
"@
RunQuery -CommandText $CommandText
}

#==============================================================================================
"Update ServerInfo Table from ServerInfoTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE ServerInfo Data USING ServerInfoTemp Temp
ON Data.ServerName = Temp.ServerName
WHEN MATCHED THEN UPDATE SET Data.ServerName = Temp.ServerName,Data.ServerIPAddress = Temp.ServerIPAddress,Data.CitrixVersion = Temp.CitrixVersion,Data.CitrixEdition = Temp.CitrixEdition,Data.CitrixServicePack = Temp.CitrixServicePack,Data.CitrixInstallDate = Temp.CitrixInstallDate
WHEN NOT MATCHED THEN INSERT (ServerName,ServerIPAddress,CitrixVersion,CitrixEdition,CitrixServicePack,CitrixInstallDate)
VALUES (Temp.ServerName,Temp.ServerIPAddress,Temp.CitrixVersion,Temp.CitrixEdition,Temp.CitrixServicePack,Temp.CitrixInstallDate);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Get XenApp Sessions" | LogMe -display
#==============================================================================================
$Sessions = Get-XASession -full  | where-object {
($_.ApplicationState -eq 'Active') -and ($_.state -eq 'Active') -and ($_.SessionName -match 'ICA-TCP') -and ($_.LogOnTime -gt $tminus) -or ($_.state -eq 'Disconnected') -and ($_.LogOnTime -gt $tminus) -or ($_.ApplicationState -eq 'Active') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP') -or ($_.ApplicationState -eq 'NotApplicable') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP')
}

#==============================================================================================
"Write Users info to UsersTemp Table" | LogMe -display
#==============================================================================================
$Sessions | foreach {
$AccountName=$_.accountname
$CommandText = @"
INSERT INTO dbo.UsersTemp (AccountName)
VALUES ('$AccountName')
"@
RunQuery -CommandText $CommandText
}

#==============================================================================================
"Update Users Table from UsersTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE Users Data USING (SELECT * FROM UsersTemp GROUP BY AccountName) Temp
ON Data.AccountName = Temp.AccountName
WHEN NOT MATCHED THEN INSERT (AccountName)
VALUES (Temp.AccountName);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Get Applications and write to the ApplicationsTemp Table" | LogMe -display
#==============================================================================================
$GetApplications = Get-XAApplicationReport * | select ApplicationType, BrowserName, FolderPath, Enabled, HideWhenDisabled, ContentAddress, CommandLineExecutable, WorkingDirectory, AnonymousConnectionsAllowed, AddToClientStartMenu, ClientFolder, StartMenuFolder, AddToClientDesktop,  ConnectionsThroughAccessGatewayAllowed, OtherConnectionsAllowed, AccessSessionConditionsEnabled, @{n="AccessSessionConditions";e={[string]::join(" ; ", $_.AccessSessionConditions)}}, InstanceLimit, MultipleInstancesPerUserAllowed, CpuPriorityLevel, AudioType, AudioRequired, SslConnectionEnabled, EncryptionLevel, EncryptionRequired, WaitOnPrinterCreation, WindowType, ColorDepth, TitleBarHidden, MaximizedOnStartup, OfflineAccessAllowed, CachingOption, AlternateProfiles, RunAsLeastPrivilegedUser, @{n="Servers";e={[string]::join(" ; ", $_.ServerNames)}}, @{n="WorkerGroups";e={[string]::join(" ; ", $_.WorkerGroupNames)}}, @{n="Users";e={[string]::join(" ; ", $_.Accounts)}}
$GetApplications | foreach {
$ApplicationName=$_.BrowserName
$ApplicationType=$_.ApplicationType
$FolderPath=$_.FolderPath
$Enabled=$_.Enabled
$HideWhenDisabled=$_.HideWhenDisabled
$ContentAddress=$_.ContentAddress
$CommandLineExecutable=$_.CommandLineExecutable
$WorkingDirectory=$_.WorkingDirectory
$AnonymousConnectionsAllowed=$_.AnonymousConnectionsAllowed
$AddToClientStartMenu=$_.AddToClientStartMenu
$ClientFolder=$_.ClientFolder
$StartMenuFolder=$_.StartMenuFolder
$AddToClientDesktop=$_.AddToClientDesktop
$ConnectionsThroughAccessGatewayAllowed=$_.ConnectionsThroughAccessGatewayAllowed
$OtherConnectionsAllowed=$_.OtherConnectionsAllowed
$AccessSessionConditionsEnabled=$_.AccessSessionConditionsEnabled
$AccessSessionConditions=$_.AccessSessionConditions
$InstanceLimit=$_.InstanceLimit
$MultipleInstancesPerUserAllowed=$_.MultipleInstancesPerUserAllowed
$CpuPriorityLevel=$_.CpuPriorityLevel
$AudioType=$_.AudioType
$AudioRequired=$_.AudioRequired
$SslConnectionEnabled=$_.SslConnectionEnabled
$EncryptionLevel=$_.EncryptionLevel
$EncryptionRequired=$_.EncryptionRequired
$WaitOnPrinterCreation=$_.WaitOnPrinterCreation
$WindowType=$_.WindowType
$ColorDepth=$_.ColorDepth
$TitleBarHidden=$_.TitleBarHidden
$MaximizedOnStartup=$_.MaximizedOnStartup
$OfflineAccessAllowed=$_.OfflineAccessAllowed
$CachingOption=$_.CachingOption
$AlternateProfiles=$_.AlternateProfiles
$RunAsLeastPrivilegedUser=$_.RunAsLeastPrivilegedUser
$Servers=$_.Servers
$WorkerGroups=$_.WorkerGroups
$Users=$_.Users
$CommandText = @"
INSERT INTO dbo.ApplicationsTemp (ApplicationName,ApplicationType,FolderPath,Enabled,HideWhenDisabled,ContentAddress,CommandLineExecutable,WorkingDirectory,AnonymousConnectionsAllowed,AddToClientStartMenu,[ClientFolder],StartMenuFolder,AddToClientDesktop,ConnectionsThroughAccessGatewayAllowed,OtherConnectionsAllowed,AccessSessionConditionsEnabled,AccessSessionConditions,InstanceLimit,MultipleInstancesPerUserAllowed,CpuPriorityLevel,AudioType,AudioRequired,SslConnectionEnabled,EncryptionLevel,WaitOnPrinterCreation,WindowType,ColorDepth,TitleBarHidden,MaximizedOnStartup,OfflineAccessAllowed,CachingOption,AlternateProfiles,RunAsLeastPrivilegedUser,Servers,WorkerGroups,Users)
VALUES ('$ApplicationName','$ApplicationType','$FolderPath','$Enabled','$HideWhenDisabled','$ContentAddress','$CommandLineExecutable','$WorkingDirectory','$AnonymousConnectionsAllowed','$AddToClientStartMenu','$ClientFolder','$StartMenuFolder','$AddToClientDesktop','$ConnectionsThroughAccessGatewayAllowed','$OtherConnectionsAllowed','$AccessSessionConditionsEnabled','$AccessSessionConditions','$InstanceLimit','$MultipleInstancesPerUserAllowed','$CpuPriorityLevel','$AudioType','$AudioRequired','$SslConnectionEnabled','$EncryptionLevel','$WaitOnPrinterCreation','$WindowType','$ColorDepth','$TitleBarHidden','$MaximizedOnStartup','$OfflineAccessAllowed','$CachingOption','$AlternateProfiles','$RunAsLeastPrivilegedUser','$Servers','$WorkerGroups','$Users')
"@
RunQuery -CommandText $CommandText
}

#==============================================================================================
"Update Applications Table from ApplicationsTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE Applications Data USING ApplicationsTemp Temp
ON Data.ApplicationName = Temp.ApplicationName
WHEN MATCHED THEN UPDATE SET Data.ApplicationType = Temp.ApplicationType,Data.FolderPath = Temp.FolderPath,Data.Enabled = Temp.Enabled,Data.HideWhenDisabled = Temp.HideWhenDisabled,Data.ContentAddress = Temp.ContentAddress,Data.CommandLineExecutable = Temp.CommandLineExecutable,Data.WorkingDirectory = Temp.WorkingDirectory,Data.AnonymousConnectionsAllowed = Temp.AnonymousConnectionsAllowed,Data.AddToClientStartMenu = Temp.AddToClientStartMenu,Data.ClientFolder = Temp.ClientFolder,Data.StartMenuFolder = Temp.StartMenuFolder,Data.AddToClientDesktop = Temp.AddToClientDesktop,Data.ConnectionsThroughAccessGatewayAllowed = Temp.ConnectionsThroughAccessGatewayAllowed,Data.OtherConnectionsAllowed = Temp.OtherConnectionsAllowed,Data.AccessSessionConditionsEnabled = Temp.AccessSessionConditionsEnabled,Data.AccessSessionConditions = Temp.AccessSessionConditions,Data.InstanceLimit = Temp.InstanceLimit,Data.MultipleInstancesPerUserAllowed = Temp.MultipleInstancesPerUserAllowed,Data.CpuPriorityLevel = Temp.CpuPriorityLevel,Data.AudioType = Temp.AudioType,Data.AudioRequired = Temp.AudioRequired,Data.SslConnectionEnabled = Temp.SslConnectionEnabled,Data.EncryptionLevel = Temp.EncryptionLevel,Data.EncryptionRequired = Temp.EncryptionRequired,Data.WaitOnPrinterCreation = Temp.WaitOnPrinterCreation,Data.WindowType = Temp.WindowType,Data.ColorDepth = Temp.ColorDepth,Data.TitleBarHidden = Temp.TitleBarHidden,Data.MaximizedOnStartup = Temp.MaximizedOnStartup,Data.OfflineAccessAllowed = Temp.OfflineAccessAllowed,Data.CachingOption = Temp.CachingOption, Data.AlternateProfiles = Temp.AlternateProfiles,Data.RunAsLeastPrivilegedUser = Temp.RunAsLeastPrivilegedUser,Data.Servers = Temp.Servers,Data.WorkerGroups = Temp.WorkerGroups,Data.Users = Temp.Users `
WHEN NOT MATCHED THEN INSERT (ApplicationName,ApplicationType,FolderPath,Enabled,HideWhenDisabled,ContentAddress,CommandLineExecutable,WorkingDirectory,AnonymousConnectionsAllowed,AddToClientStartMenu,ClientFolder,StartMenuFolder,AddToClientDesktop,ConnectionsThroughAccessGatewayAllowed,OtherConnectionsAllowed,AccessSessionConditionsEnabled,AccessSessionConditions,InstanceLimit,MultipleInstancesPerUserAllowed,CpuPriorityLevel,AudioType,AudioRequired,SslConnectionEnabled,EncryptionLevel,WaitOnPrinterCreation,WindowType,ColorDepth,TitleBarHidden,MaximizedOnStartup,OfflineAccessAllowed,CachingOption,AlternateProfiles,RunAsLeastPrivilegedUser,Servers,WorkerGroups,Users)
VALUES (Temp.ApplicationName,Temp.ApplicationType,Temp.FolderPath,Temp.Enabled,Temp.HideWhenDisabled,Temp.ContentAddress,Temp.CommandLineExecutable,Temp.WorkingDirectory,Temp.AnonymousConnectionsAllowed,Temp.AddToClientStartMenu,Temp.ClientFolder,Temp.StartMenuFolder,Temp.AddToClientDesktop,Temp.ConnectionsThroughAccessGatewayAllowed,Temp.OtherConnectionsAllowed,Temp.AccessSessionConditionsEnabled,Temp.AccessSessionConditions,Temp.InstanceLimit,Temp.MultipleInstancesPerUserAllowed,Temp.CpuPriorityLevel,Temp.AudioType,Temp.AudioRequired,Temp.SslConnectionEnabled,Temp.EncryptionLevel,Temp.WaitOnPrinterCreation,Temp.WindowType,Temp.ColorDepth,Temp.TitleBarHidden,Temp.MaximizedOnStartup,Temp.OfflineAccessAllowed,Temp.CachingOption,Temp.AlternateProfiles,Temp.RunAsLeastPrivilegedUser,Temp.Servers,Temp.WorkerGroups,Temp.Users);
SELECT * FROM Applications;
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Session info to SessionTemp Table and Write Processes from each session to ProcessesTemp" | LogMe -display
#==============================================================================================
$Sessions | foreach {
$AccountName=$_.accountname
$ServerName=$_.ServerName
$SessionName=$_.SessionName
$SessionID=$_.SessionId
$ClientName=$_.ClientName
$ClientVersion=$_.ClientVersion
$ClientAddress=$_.ClientAddress
$SessionState=$_.State
$LogonTimeSQL=$_.LogonTime.ToString("MMM dd yyyy HH:mm")
$LogonTime = (Get-Date) - $_.LogOnTime
$ConnectTime = (Get-Date) - $_.ConnectTime
$LogonDuration = "{0:00}:{1:00}:{2:00}" -f $LogonTime.Hours,$LogonTime.Minutes,$LogonTime.Seconds
$CommandText = @"
INSERT INTO dbo.SessionDataTemp (AccountName,FarmName,ServerName,SessionName,ClientName,ClientVersion,ClientAddress,LogonTime,LogonDuration)
VALUES ('$AccountName','$Farmname','$ServerName','$SessionName','$ClientName','$ClientVersion','$ClientAddress','$LogonTimeSQL','$LogonDuration')
"@
RunQuery -CommandText $CommandText

Get-XASessionProcess -SessionID "$SessionID" -ServerName "$ServerName" | foreach-object {
$Processname=$_.ProcessName
$CreationTimeSQL=$_.CreationTime.ToString("MMM dd yyyy HH:mm")
$ProcessId=$_.ProcessId
$CommandText = @"
INSERT INTO dbo.ProcessTemp (ServerName,Processname,CreationTime,ProcessId,LogonTime,SessionName,ClientName)
VALUES ('$ServerName','$Processname','$CreationTimeSQL','$ProcessId','$LogonTimeSQL','$SessionName','$ClientName')
"@
RunQuery -CommandText $CommandText
}
}

#==============================================================================================
"Update SessionData Table from SessionDataTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE SessionData Data USING (SELECT AccountName,FarmName,ServerName,SessionName,ClientName,ClientVersion,ClientAddress,LogonTime,MAX(LogonDuration) AS MaxLDur
FROM SessionDataTemp GROUP BY AccountName,FarmName,ServerName,SessionName,ClientName,ClientVersion,ClientAddress,LogonTime) Temp
ON Data.AccountName = Temp.AccountName AND Data.LogonTime = Temp.LogonTime AND Data.ServerName = Temp.ServerName AND Data.SessionName = Temp.SessionName
WHEN MATCHED THEN UPDATE SET Data.LogonDuration = Temp.MaxLDur
WHEN NOT MATCHED THEN INSERT (AccountName,FarmName,ServerName,SessionName,ClientName,ClientVersion,ClientAddress,LogonTime,LogonDuration)
VALUES (Temp.AccountName,Temp.FarmName,Temp.ServerName,Temp.SessionName,Temp.ClientName,Temp.ClientVersion,Temp.ClientAddress,Temp.LogonTime,Temp.MaxLDur);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Application info from Sessions to ApplicationUsageTemp Table" | LogMe -display
#==============================================================================================
$Sessions | foreach {
$ApplicationName=$_.browsername
$AccountName=$_.accountname
$LogonTimeSQL=$_.LogonTime.ToString("MMM dd yyyy HH:mm")
$ServerName=$_.ServerName
$SessionName=$_.SessionName
$ClientName=$_.ClientName
$CommandText = @"
INSERT INTO dbo.ApplicationUsageTemp (ApplicationName,AccountName,LogonTime,ServerName,SessionName,ClientName)
VALUES ('$ApplicationName','$AccountName','$LogonTimeSQL','$ServerName','$SessionName','$ClientName')
"@
RunQuery -CommandText $CommandText
}

#==============================================================================================
"Update ApplicationUsage Table from ApplicationUsageTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE ApplicationUsage Data USING ApplicationUsageTemp Temp
ON Data.ApplicationName = Temp.ApplicationName AND Data.AccountName = Temp.AccountName AND Data.LogonTime = Temp.LogonTime AND Data.ServerName = Temp.ServerName
WHEN NOT MATCHED THEN INSERT (ApplicationName,AccountName,LogonTime,ServerName,SessionName,ClientName)
VALUES (Temp.ApplicationName,Temp.AccountName,Temp.LogonTime,Temp.ServerName,Temp.SessionName,Temp.ClientName);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Update Process Table from ProcessTemp Table" | LogMe -display
#==============================================================================================
$CommandText = @"
MERGE Process Data USING (SELECT * FROM ProcessTemp
GROUP BY ServerName,Processname,CreationTime,ProcessId,LogonTime,SessionName,ClientName) Temp
ON Data.ServerName = Temp.ServerName AND Data.Processname = Temp.Processname AND Data.CreationTime = Temp.CreationTime AND Data.LogonTime = Temp.LogonTime
WHEN NOT MATCHED THEN INSERT (ServerName,Processname,CreationTime,ProcessId,LogonTime,SessionName,ClientName)
VALUES (Temp.ServerName,Temp.Processname,Temp.CreationTime,Temp.ProcessId,Temp.LogonTime,Temp.SessionName,Temp.ClientName);
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Write Server Session Info in ServerSessionCount Table" | LogMe -display
#==============================================================================================
$ServerInfo=get-XAServer -full
$ServerInfo | foreach {
$ServerName=$_.ServerName
$ServerSessionCount=$_.SessionCount
$CommandText = @"
INSERT INTO dbo.ServerSessionCount (ServerName,ServerSessionCount,DatePolled)
VALUES ('$ServerName','$ServerSessionCount','$DatePolled')
"@
RunQuery -CommandText $CommandText
}

#==============================================================================================
"Truncate Temp Tables " | LogMe -display
#==============================================================================================
$CommandText = @"
TRUNCATE TABLE dbo.SessionDataTemp
TRUNCATE TABLE dbo.ServerInfoTemp
TRUNCATE TABLE dbo.ApplicationsTemp
TRUNCATE TABLE dbo.ApplicationUsageTemp
TRUNCATE TABLE dbo.ProcessTemp
TRUNCATE TABLE dbo.UsersTemp
"@
RunQuery -CommandText $CommandText

#==============================================================================================
"Close SQL DB Connection" | LogMe -display
#==============================================================================================
$SQLConnection.Close()
if ($SQLConnection.State -eq "Closed")
    { "Succesfully Closed connection to SQL Server..."  | LogMe -display; EXIT }
else
    { "Failed to close Connection to SQL Server..." | LogMe -display; EXIT }

Reply

Stan Czerno
December 4, 2014 20:44 CST

Changes have been added to the Zip file and the post has been updated. Thanks again Brad!

I still need to update the SQL Script so it accepts sessions more than 24 hours.

Reply

TE
February 16, 2015 14:17 CST

Hi i am trying to use the script i have an error message an error occured while attempting to open DataBase  connection ....

Reply

Ganesh Devendiran
February 16, 2015 06:15 CST

Hi,

Its a very good article. But couldn't find the attached zip file for powershell script for xenapp server and SQL reports file.

Please share the donwload link for those files or send it to my mail ID.

Really appreciated your help Smile

Thanks,
D Ganesh

Reply

Stan Czerno
February 16, 2015 13:41 CST

Link to ZIP: www.czerno.com/.../XenAppResourceManager.zip

Reply

te
February 16, 2015 16:23 CST

Hi Stan i have an error message with An error occurred while attempting to open the database connection and execute a command any idea ?

Reply

Fredrik
March 2, 2015 01:48 CST

Hi,

I just want to share a problem I got.
I got this fault message and it took some time to figure out.
My farm name is more then 10 characters and the table in the script is only 10 characters for that table. I resolved it by creating a new databas with more characters to farmname Smile


Write Farm Info to FarmTemp Table


Exception             : System.Management.Automation.MethodInvocationException:
                         Exception calling "ExecuteNonQuery" with "0" argument(
                        s): "String or binary data would be truncated.
                        The statement has been terminated." ---> System.Data.Sq
                        lClient.SqlException: String or binary data would be tr
                        uncated.

Reply

npatel
June 7, 2016 11:48 CST

I also get same error...

Reply

Manachinu
April 10, 2015 09:20 CST

Thanks for the awesome work and sharing with us. On running the PS script i get the following error.

ERROR]

Exception             : System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" wit
                        h "0" argument(s): "The MERGE statement conflicted with the FOREIGN KEY constraint "FK__Session
                        Da__Serve__0AD2A005". The conflict occurred in database "XenAppResourceManager", table "dbo.Ser
                        verInfo", column 'ServerName'.
                        The statement has been terminated." ---> System.Data.SqlClient.SqlException: The MERGE statemen
                        t conflicted with the FOREIGN KEY constraint "FK__SessionDa__Serve__0AD2A005". The conflict occ
                        urred in database "XenAppResourceManager", table "dbo.ServerInfo", column 'ServerName'.
                        The statement has been terminated.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnecti
                        on)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDa
                        taReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String met
                        hodName, Boolean sendToPipe)
                           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           at ExecuteNonQuery(Object , Object[] )
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String meth
                        odName, Object[] paramArray, Boolean callStatic, Object valueToSet)
                           at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] argument
                        s, Object value)
                           at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, Executi
                        onContext context)
                           at System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, A
                        rray inputToProcess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :

Reply

Manachinu
April 10, 2015 09:42 CST

Just before the above error i also see this error
You cannot call a method on a null-valued expression.
Script.ps1:280 char:36
+ $LogonTimeSQL=$_.LogonTime.ToString <<<< ("MMM dd yyyy HH:mm")
    + CategoryInfo          : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Cannot find an overload for "op_Subtraction" and the argument count: "2".
Script.ps1:281 char:26
+ $LogonTime = (Get-Date) - <<<<  $_.LogOnTime
    + CategoryInfo          : NotSpecified: (Smile [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Cannot find an overload for "op_Subtraction" and the argument count: "2".
Script.ps1:282 char:28
+ $ConnectTime = (Get-Date) - <<<<  $_.ConnectTime
    + CategoryInfo          : NotSpecified: (Smile [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Get-XASessionProcess : Cannot validate argument on parameter 'ServerName'. The argument is null or empty. Supply an arg
ument that is not null or empty and then try the command again.
Script.ps1:290 char:57
+ Get-XASessionProcess -SessionID "$SessionID" -ServerName <<<<  "$ServerName" | foreach-object {
    + CategoryInfo          : InvalidData: (Smile [Get-XASessionProcess], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Citrix.XenApp.Commands.GetSessionProcessCmdlet

Reply

Stan Czerno
April 10, 2015 10:08 CST

You'll need to pick a part some of the variables and see if you can figure out why the values are coming up as Null.

For example, the section you are having an issue with, you need to pull parts of it out and make sure they work. I quickly created this to test just the one section:

cls
"Loading Citrix XenApp Commands"
if ((Get-PSSnapin "Citrix.*" -EA silentlycontinue) -eq $null) {
  try { Add-PSSnapin Citrix.* -ErrorAction Stop }
  catch { write-error "Error loading XenApp Powershell snapin"; Return } }

$time1 = Get-Date
$tminus = $time1.addminutes(-1440)

$Sessions = Get-XASession -full  | where-object {
($_.ApplicationState -eq 'Active') -and ($_.state -eq 'Active') -and ($_.SessionName -match 'ICA-TCP') -and ($_.LogOnTime -gt $tminus) -or ($_.state -eq 'Disconnected') -and ($_.LogOnTime -gt $tminus) -or ($_.ApplicationState -eq 'Active') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP') -or ($_.ApplicationState -eq 'NotApplicable') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP')
}

$item = 1

$Sessions | foreach {
$AccountName=$_.accountname
$ServerName=$_.ServerName
$SessionName=$_.SessionName
$SessionID=$_.SessionId
$ClientName=$_.ClientName
$ClientVersion=$_.ClientVersion
$ClientAddress=$_.ClientAddress
$SessionState=$_.State
$LogonTimeSQL=$_.LogonTime.ToString("MMM dd yyyy HH:mm")
$LogonTime = (Get-Date) - $_.LogOnTime
$ConnectTime = (Get-Date) - $_.ConnectTime
$LogonDuration = "{0:00}:{1:00}:{2:00}" -f $LogonTime.Hours,$LogonTime.Minutes,$LogonTime.Seconds

"==================================================================="
"Item in foreach: $item"
"Account name: $AccountName"
"Server name: $ServerName"
"Session name: $SessionName"
"Session ID: $SessionID"
"Client name: $ClientName"
"Client Version: $ClientVersion"
"Session State: $SessionState"
"Logon Time for SQL: $LogonTimeSQL"
"Logon Time: $LogonTime"
"Connect Time: $ConnectTime"
"Logon Duration: $LogonDuration"
"==================================================================="
""
""
""
$item ++
}

Reply

manachinu
April 10, 2015 10:37 CST

Script is having issue with

$LogonTimeSQL=$_.LogonTime.ToString("MMM dd yyyy HH:mm")

Reply

Stan Czerno
April 10, 2015 17:27 CST

You'll probably have to adjust this based on your region. My DB is set to EN-US.

Reply

Manachinu
April 10, 2015 17:19 CST

$Sessions = Get-XASession -full  | where-object {
($_.ApplicationState -eq 'Active') -and ($_.state -eq 'Active') -and ($_.SessionName -match 'ICA-TCP') -and ($_.LogOnTime -gt $tminus) -or ($_.state -eq 'Disconnected') -and ($_.LogOnTime -gt $tminus) -or ($_.ApplicationState -eq 'Active') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP') -or ($_.ApplicationState -eq 'NotApplicable') -and ($_.LogOnTime -gt $tminus) -and ($_.SessionName -match 'ICA-TCP')
}

In the above statement ApplicationState seems to be a invalid field for my environment. When i run Get-XASession -full i dont see ApplicationState as a option.

I am on citrix version 6.0.6410

Reply

Manachinu
April 10, 2015 17:22 CST

These are the fields i get as output for Get-XASession -full

SessionId                :
SessionName              :
ServerName               :
AccountName              :
BrowserName              :
State                    :
ClientName               :
LogOnTime                :
Protocol                 :
VirtualIP                :
EncryptionLevel          :
ServerBuffers            :
ClientIPV4               :
ClientBuffers            :
ClientBuildNumber        :
ColorDepth               :
ClientDirectory          :
ClientProductId          :
HorizontalResolution     :
VerticalResolution       :
ConnectTime              :
DisconnectTime           :
LastInputTime            :
CurrentTime              :
ClientCacheLow           :
ClientCacheTiny          :
ClientCacheXms           :
ClientCacheDisk          :
ClientCacheSize          :
ClientCacheMinBitmapSize :

Reply

Stan Czerno
April 10, 2015 17:35 CST

Wow, my bad. I haven't used XenApp 6 in a while so I never tested it. But I did look it up and you are correct, not an option in XenApp 6.

Reply

Manachinu
April 13, 2015 09:55 CST

Any idea what i field i can substitute for the applicationstate in your scripts.

Reply

Stan Czerno
April 13, 2015 09:57 CST

There is nothing to substitute, you would have to remove it, which means a lot of editing and testing.

Reply

Julien
October 22, 2015 08:11 CST

Hi,

Thanks a lot for your work.
Just a remark, the FarmName is a nvarchar(10) which could be too small.
I updated it to nvarchar(15) to be able to use is without exceptions.

Thanks again.

Julien.

Reply

Anthelme
October 29, 2015 02:43 CST

Hello,

I have somme issues with the script and have this:
Loading Citrix XenApp Commands
This Server is part of a Domain
Connect to SQL Server Database
Succesfully Connected to SQL Server...
Clear the Temp Tables
Write Farm Info to FarmTemp Table


Exception             : System.Management.Automation.MethodInvocationException: Exception lors de l'appel de « ExecuteNonQuery » avec « 0 »
                         argument(s) : « Les données de chaîne ou binaires seront tronquées.
                        L'instruction a été arrêtée. » ---> System.Data.SqlClient.SqlException: Les données de chaîne ou binaires seront tr
                        onquées.
                        L'instruction a été arrêtée.
                           à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                           à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                           à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
                        BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                           à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                           à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean send
                        ToPipe)
                           à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           à ExecuteNonQuery(Object , Object[] )
                           à System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInfo
                        rmation methodInformation, Object[] originalArguments)
                           --- Fin de la trace de la pile d'exception interne ---
                           à System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInfo
                        rmation methodInformation, Object[] originalArguments)
                           à System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] para
                        mArray, Boolean callStatic, Object valueToSet)
                           à System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
                           à System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
                           à System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, Array inputToProcess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :




Succesfully Closed connection to SQL Server...

Reply

Anthelme
October 29, 2015 09:38 CST

Hello,

Now i have this error:
Exception             : System.Management.Automation.MethodInvocationException: Exception lors de l'appel de « ExecuteNonQuery » avec « 0 » argument(s) : « Syntaxe incorrecte vers 'admin'.
                        Ouvrez les guillemets après la chaîne de caractères ')'. » ---> System.Data.SqlClient.SqlException: Syntaxe incorrecte vers 'admin'.
                        Ouvrez les guillemets après la chaîne de caractères ')'.
                           à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                           à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                           à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                           à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                           à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
                           à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           à ExecuteNonQuery(Object , Object[] )
                           à System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
                           --- Fin de la trace de la pile d'exception interne ---
                           à System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
                           à System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
                           à System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
                           à System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
                           à System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, Array inputToProcess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :

Reply

Majeed Attar
April 21, 2016 04:05 CST

Hello Stan Czerno,

I have read the blog and script you wrote for application report. I would like to know if you have create this kind of report for CitrixXenapp 7.x version if yes please provide the same.

Thank you very much.

Regards,
Majeed

Reply

Nicklas Friberg
May 17, 2016 06:56 CST

Hi

Also interested in a similar function for XenApp 7.6

Regards
Nicklas

Reply

npatel
June 7, 2016 11:53 CST

Getting below error while running the script... Please assist

Exception             : System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" wit
                        h "0" argument(s): "String or binary data would be truncated.
                        The statement has been terminated." ---> System.Data.SqlClient.SqlException: String or binary d
                        ata would be truncated.
                        The statement has been terminated.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnecti
                        on)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDa
                        taReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String met
                        hodName, Boolean sendToPipe)
                           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           at ExecuteNonQuery(Object , Object[] )
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[]
                        arguments, MethodInformation methodInformation, Object[] originalArguments)
                           at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String meth
                        odName, Object[] paramArray, Boolean callStatic, Object valueToSet)
                           at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] argument
                        s, Object value)
                           at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, Executi
                        onContext context)
                           at System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, A
                        rray inputToProcess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :




Succesfully Closed connection to SQL Server...

Reply

np
June 8, 2016 09:51 CST

The error was for writing information on db.farm info.. it was resolved by extending the table length from 10 to 15 while creating the database
Farm name was more then 10 character so it was giving above error.

Reply

Suman Chhetri
June 13, 2016 15:44 CST

That's such a valuable research. By the way, do you have something like this for Citrix XenApp 7.x?

Reply

Stan Czerno
July 13, 2016 09:08 CST

I have not worked on duplicating this for XenApp 7.x yet.

Reply

User
July 18, 2016 12:17 CST

I am running SQL 2005 and am attempting to run the script and connect with Administrator and have the Administrator user mapped to the XenAppResourceManager database with db_owner rights.  When attempting to run the Powershell script manually from the XenApp server, I get the error Exception calling "Open" with "0" argument(s): "Cannot open database "XenAppResourceManager \" requested by the login.  The login failed.  Login failed for user "domain\Administrator."
At\\filepath\XenAppStatsCollect.ps1:95 char:20
+ $SQLConnection.Open <<<< ()
+ CategoryInfo   :NotSpecified: (Smile [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

Connection to SQL Server Failed....

Any suggestions?

Reply

Stan Czerno
July 18, 2016 12:23 CST

Looks like a typo in my script. Remove the carriage return and the \ from the Database Variable, it should just be:

$DatabaseName = "XenAppResourceManager"

Reply

User
July 18, 2016 12:35 CST

Thanks, that allowed me to connect successfully.  However, now I get a lengthy different error.  Is this compatible with SQL 2005?  Could it be because my farm name is 15 characters long?

Loading Citrix XenApp Commands
This Server is part of a Domain
Connect to SQL Server Database
Succesfully Connected to SQL Server...
Clear the Temp Tables
Write Farm Info to FarmTemp Table


Exception             : System.Management.Automation.MethodInvocationException:
                         Exception calling "ExecuteNonQuery" with "0" argument(
                        s): "String or binary data would be truncated.
                        The statement has been terminated." ---> System.Data.Sq
                        lClient.SqlException: String or binary data would be tr
                        uncated.
                        The statement has been terminated.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlEx
                        ception exception, Boolean breakConnection)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAnd
                        Warning(TdsParserStateObject stateObj)
                           at System.Data.SqlClient.TdsParser.Run(RunBehavior r
                        unBehavior, SqlCommand cmdHandler, SqlDataReader dataSt
                        ream, BulkCopySimpleResultSet bulkCopyHandler, TdsParse
                        rStateObject stateObj)
                           at System.Data.SqlClient.SqlCommand.RunExecuteNonQue
                        ryTds(String methodName, Boolean async)
                           at System.Data.SqlClient.SqlCommand.InternalExecuteN
                        onQuery(DbAsyncResult result, String methodName, Boolea
                        n sendToPipe)
                           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery(
                        )
                           at ExecuteNonQuery(Object , Object[] )
                           at System.Management.Automation.DotNetAdapter.Auxili
                        aryMethodInvoke(Object target, Object[] arguments, Meth
                        odInformation methodInformation, Object[] originalArgum
                        ents)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.Auxili
                        aryMethodInvoke(Object target, Object[] arguments, Meth
                        odInformation methodInformation, Object[] originalArgum
                        ents)
                           at System.Management.Automation.ParserOps.CallMethod
                        (Token token, Object target, String methodName, Object[
                        ] paramArray, Boolean callStatic, Object valueToSet)
                           at System.Management.Automation.MethodCallNode.Invok
                        eMethod(Object target, Object[] arguments, Object value
                        )
                           at System.Management.Automation.MethodCallNode.Execu
                        te(Array input, Pipe outputPipe, ExecutionContext conte
                        xt)
                           at System.Management.Automation.ScriptCommandProcess
                        or.ExecuteWithCatch(ParseTreeNode ptn, Array inputToPro
                        cess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :




Succesfully Closed connection to SQL Server...
PS Microsoft.PowerShell.Core\FileSystem::************filepath********

Reply

User
July 18, 2016 12:43 CST

Increased farmlength to 15, and still receive an error upon executing the script:

Loading Citrix XenApp Commands
This Server is part of a Domain
Connect to SQL Server Database
Succesfully Connected to SQL Server...
Clear the Temp Tables
Write Farm Info to FarmTemp Table
Update Farm Table from FarmTemp Table


Exception             : System.Management.Automation.MethodInvocationException:
                         Exception calling "ExecuteNonQuery" with "0" argument(
                        s): "Incorrect syntax near 'Data'." ---> System.Data.Sq
                        lClient.SqlException: Incorrect syntax near 'Data'.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlEx
                        ception exception, Boolean breakConnection)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAnd
                        Warning(TdsParserStateObject stateObj)
                           at System.Data.SqlClient.TdsParser.Run(RunBehavior r
                        unBehavior, SqlCommand cmdHandler, SqlDataReader dataSt
                        ream, BulkCopySimpleResultSet bulkCopyHandler, TdsParse
                        rStateObject stateObj)
                           at System.Data.SqlClient.SqlCommand.RunExecuteNonQue
                        ryTds(String methodName, Boolean async)
                           at System.Data.SqlClient.SqlCommand.InternalExecuteN
                        onQuery(DbAsyncResult result, String methodName, Boolea
                        n sendToPipe)
                           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery(
                        )
                           at ExecuteNonQuery(Object , Object[] )
                           at System.Management.Automation.DotNetAdapter.Auxili
                        aryMethodInvoke(Object target, Object[] arguments, Meth
                        odInformation methodInformation, Object[] originalArgum
                        ents)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.Auxili
                        aryMethodInvoke(Object target, Object[] arguments, Meth
                        odInformation methodInformation, Object[] originalArgum
                        ents)
                           at System.Management.Automation.ParserOps.CallMethod
                        (Token token, Object target, String methodName, Object[
                        ] paramArray, Boolean callStatic, Object valueToSet)
                           at System.Management.Automation.MethodCallNode.Invok
                        eMethod(Object target, Object[] arguments, Object value
                        )
                           at System.Management.Automation.MethodCallNode.Execu
                        te(Array input, Pipe outputPipe, ExecutionContext conte
                        xt)
                           at System.Management.Automation.ScriptCommandProcess
                        or.ExecuteWithCatch(ParseTreeNode ptn, Array inputToPro
                        cess)
TargetObject          :
CategoryInfo          : NotSpecified: (Smile [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {}
PSMessageDetails      :




Succesfully Closed connection to SQL Server...
PS Microsoft.PowerShell.Core\FileSystem::**************filepath**************

Reply

Stan Czerno
July 18, 2016 12:43 CST

I could not say if it works with SQL 2005 for sure, but it should. Not sure about the reports either.

If your Farm Name is greater than 10 then you need to modify the FarmName field in each table.

Or you can edit the SQL query, change the character length for all FarmName entries and re-create the DB.

Reply

Add comment