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.

Update DNS Server setting on Multiple Servers with powershell

Had to update the DNS server settings on a few servers (80+).
Because I had a mix 23/28/28r2/12 servers decided wmi was the path forward + powershell.

issues with when doing contains, until I performed a convert to string [String]

Stumped by the String setting for a little while was trying .tostring()

Script in 3 parts:

1. check all the servers to see if hard code to old DC
2. Update the DHCP server options on all authorised DHCP servers
3. Purge any scope with setting for old DC to use the server option

Btw: some servers dont reply correctly to WMI so... prepared to check manually (and maybe fix wmi)

#StartHere :)
$OUName= 'OU=Servers,DC=KoolKids'
$TheComputers = Get-ADComputer -filter * -searchbase $OUName
$results = @()

Foreach ($server in $TheComputers) {
if(Test-Connection $server.name -Count 1 -quiet){
            $NICs = Get-WMIObject Win32_NetworkAdapterConfiguration -computername $server.name| where{$_.IPEnabled -eq “TRUE”} 
                Foreach($NIC in $NICs) {
                $FTW=$NIC.DNSServerSearchOrder
                $FTW =[String]$FTW
               # write-host $FTW
                If ($FTW.contains("10.10.3.1")) {
                                       $results += New-Object PSObject -Property @{
                                       Server = $server.name
                                       DNS = $FTW
                                          }
                            #update the DNS Server for this NIC
                            $DNSServers = "172.18.0.10","172.18.0.11"
                            $NIC.SetDNSServerSearchOrder($DNSServers)
                    }
                }
        }
    }
#set the default server scope options to correct setting
 foreach ($dhcpserver in Get-DhcpServerInDC){
 if(Test-Connection $dhcpserver.DNSName  -Count 1 -Quiet){
            #Remarked out so that all active DHCP servers get updated!
            #If ($FTW.contains("10.10.3.1")) { 
             $FixScope=[System.Net.Dns]::GetHostAddresses($dhcpserver.DNSName).IPAddressToString, "172.18.0.10", "172.18.0.11"
             Set-DhcpServerv4OptionValue -ComputerName $dhcpserver.DNSName -OptionId 6 -Value $FixScope
            #}
        }
 }

 #clear out the scope options
 # WARNING SERVER OPTIONS MUST HAVE a SETTING OR BAD THINGS HAPPEN
  foreach ($dhcpserver in Get-DhcpServerInDC){
             foreach ($TheScope in (Get-dhcpserverv4scope -computername $dhcpserver.DnsName)){
                     $target=$null
                     $Target=Get-DhcpServerv4OptionValue -ComputerName $dhcpserver.DNSName -OptionId 6 -ScopeId $TheScope.ScopeId -ErrorAction SilentlyContinue
                     $Target=[String]$target.Value
                      If ($Target.contains("10.10.3.1")) {
                      Remove-DhcpServerv4OptionValue -ComputerName $dhcpserver.DNSName -OptionId 6 -ScopeId $TheScope.ScopeId 
                      }
        }
 }

Powershell Strings

$a = "This", "Is", "a", "cat"

# Operator join

# This Is a cat
$a -join ' '

# ThisIsacat
-join $a

# using conversion to [string] (and optionally use the separator $ofs)

# This Is a cat
[string]$a

# This-Is-a-cat
$ofs = '-' # ! after that all casts work in this way until $ofs changes !
[string]$a