How Dump Citrix XenApp 7.6 User Session Information every hour to AACB database

Hi scripting guys again.

For today I have additional example using powershell with XenApp 7.6

This script get all citrix Site information and dump this user sessions in a Access 2013 database.

If you have installed Office 2013 64bits, you will need install engine DB for 32bits.

Feel you free to contact me to send me some comments or suggestions about this script.

<# =======================================================================

File Name: GetCurrentCitrixSessions_v01.ps1
Author: citrixpedia@gmail.com

Comments: Script to get all sessions active in the Site and dump this info into Access DB (2013).

======================================================================= #>

#-------------------------------------
# Get Location of script
#-------------------------------------

Get-pssnapin -registered | add-pssnapin -passthru | out-null

$myPath = "D:\FarmUsage.DB"

#------------------------------------------------------------------------
# Function to invoke DB
#------------------------------------------------------------------------

Function Invoke-ADOCommand($db, $command) {
$connection = New-Object -ComObject ADODB.Connection
$connection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$db")
$connection.Execute($command) | Out-Null 
$connection.Close() 
}


#-------------------------------------------------------------------
# Build date string to add to each filename: "dd-mm-yyyy and hh:mm"
#-------------------------------------------------------------------

$myDate = Get-Date -UFormat "%d-%m-%Y"
$myHour = Get-Date -UFormat "%H:%M"
$myDB = $myPath + "\SiteXA76scb.accdb"


#------------------------------------------------------------------------
# Get Current Size of DB and is Greater Than 250MB move to backup folder
# and take the empty file (db structure only)
#------------------------------------------------------------------------

Get-Item $myDB | ForEach-Object { $mySize = $_.Length/1024/1024 }
if ( $mySize -gt 250 ) {
$myTarget = $myPath + "\back_template\SiteXA76scb_BAK_" + $myDate + ".accdb"
$myTemplate = $myPath + "\back_template\Template_Sitexa76scb.accdb"

Move-Item -path $myDB -destination $myTarget
Copy-Item $myTemplate -destination $myDB
}


#--------------------------------------------------------------------------------------------------
# Count Number of sessions (distinct HW ID)
#--------------------------------------------------------------------------------------------------

Get-BrokerSession | where { $_.ConnectionMode -eq "Brokered" -and $_.SessionState -eq "Active" -and $_.SessionType -eq "Application" } | Select HardwareId -unique | Measure | ForEach-Object { $Licenses = $_.Count }

Get-BrokerSession | where { $_.ConnectionMode -eq "Brokered" -and $_.SessionState -eq "Active" -and $_.SessionType -eq "Application" } | ForEach-Object { 
$myAppsArray = $_.ApplicationsInUse
$myApps = ""
ForEach ($app in $myAppsArray) {
if ($app -and $myApps) {$myApps = $myApps + "," + $app}
elseif ($app -and -not $myApps) {$myApps = $app}
}

#--------------------------------------
# Insert into DB Sessions & Licenses
#--------------------------------------

$myString = "Insert into Sessions (Tag, Uhr, AgentVersion, ApplicationsInUse, CatalogName, DesktopGroupName, ClientAddress, ClientName, ClientVersion, ControllerDNSName, StoreFrontHostName, ServerName, Protocol, UserName) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}' )" -f $myDate, $myHour, $_.AgentVersion, $myApps, $_.CatalogName, $_.DesktopGroupName, $_.ClientAddress, $_.ClientName, $_.ClientVersion, $_.ControllerDNSName, $_.LaunchedViaHostName, $_.MachineName, $_.Protocol, $_.UserName
Invoke-ADOCommand -db $myDB -command $myString

$myString = "Insert into Licenses (Tag, Uhr, UserConnections) VALUES ('{0}','{1}','{2}')" -f $myDate, $myHour, $Licenses
Invoke-ADOCommand -db $myDB -command $myString
}