Reporting Citrix user session into SQL - Alternative to edgesight

Updated 27/09/2013
Since we moved to  XenAPP 6.5 we noticed that was unable to get handy reports about usage what application users were loading. Not a bit fan of edgesight ! :(

After many months we have arrived at this solution, every 15mins a powershell script will query the XenApp farm and write the content up to SQL DB.

Assume you will know how to create table\permissions in the SQL database.
Pretty lean on my SQL leave it up to you to workout.
Created a database 'xenuserinfo' manually and then made sure it was selected when executing the SQL scripts

SQL Script:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
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.XData
 (
 UsageDate datetime NULL,
 AccountName nvarchar(50) NULL,
 Application nvarchar(50) NULL,
 FarmName nvarchar(10) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.XData SET (LOCK_ESCALATION = TABLE)
GO


CREATE TABLE dbo.XCount
 (
 UsageDate datetime NULL,
 SessionCount smallint NULL,
 FarmName nvarchar(10) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.XCount SET (LOCK_ESCALATION = TABLE)

GO

COMMIT

Then I used the console to add permissions etc.

Now the powershell script that will run every 15mins, notice there are 2 entries to save me time when making graphs. At the 15 minute mark it looks backwards to see if there are any logons, if so then upload to database. Doubt it would get secondary launches (ie loading another app off same server).

Performing the session count as we are working in shared license environment = trust no-one!.

 Don't forget to install the SDK

#Add-PSSnapin citrix.xenapp.commands
#CPS Version
$time2 = Get-Date -Format "MMM dd yyyy HH:mm"
$time1 = Get-Date
$tminus15 = $time1.addminutes(-15)

# Check logons in the last 15 minutes
$allSessions = Get-XASession  | where-object -filterscript { ($_.state -eq 'Active') -or ($_.state -eq 'Disconnected') -and ($_.LogOnTime -gt $tminus15)} 
$FarmInfo=Get-xafarm 
$FarmName=$Farminfo.FarmName

 ## Hello SQL
$dbconn = New-Object System.Data.SqlClient.SqlConnection("Data Source=TheSQLServer; Initial Catalog=XenUserInfo; Integrated Security=SSPI")
$dbconn.Open()

 
 ## Write User App info to SQL
$allSessions | foreach {
$ACC=$_.accountname
$APP=$_.browsername
$LOT=$_.LogonTime.ToString("MMM dd yyyy HH:mm")
$CLN=$_.ClientName
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = "INSERT INTO dbo.XLCPLData (UsageDate,AccountName,Application,FarmName,LogonTime,ClientName) VALUES ('$time2','$ACC','$APP','$Farmname','$LOT','$CLN')"
$dbwrite.ExecuteNonQuery()
$ACC=$null
$APP=$Null
$LOT=$Null
$CLN=$null
} 

 ## Write Session Count to SQL 
$dacount=$FarmInfo.SessionCount
$dbwrite1 = $dbconn.CreateCommand()
$dbwrite1.CommandText = "INSERT INTO dbo.XLCPLCount (UsageDate,SessionCount,FarmName) VALUES ('$time2', '$dacount','$Farmname')"
$dbwrite1.ExecuteNonQuery()

 ## Finished with SQL --- GoodBye
$dbconn.Close()
$allSession=$null

now off to create pretty graphs in excel

Update -

or.
Muck around with data in powershell ie generate csv etc.

## Hello SQL Grab my data from the last 30days
    $query = "select * FROM dbo.XLCPLData where LogonTime > GETDATE()-30 and Application != ''"
    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=LCLABXENWIDS; Initial Catalog=XenUserInfo; Integrated Security=SSPI")
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $applist=$table | sort-object Application -Unique | select -Property application
    
## Go generate pretty graphs or whatever.
## Use $table to see the list connections
## Use $applist to see the list of unique apps
##    Then you can use a foreach loop to generate content or csv.