Some of our clients have in excess of 100 published applications. It's no wonder, then, that they may wish to remove unused applications. So, let's use a PowerShell script to create a report of unused applications.
The script will accept a parameter specifying the number of days (i.e. List applications that haven't been used in X days). We will also need the name or IP address of the SQL Server hosting the monitoring database, as well as the database name.
param ([int] $daysCovered = 30)
$SQLServer = "<SQL Server with Citrix Monitor DB>"
$SQLDBName = "CitrixMonitoring"
We then create a SQL query to retrieve the name and last used date of all applications in the monitoring database. If an application has never been used (its LastUsed date is null), we set the date to 1/1/1900).
# retrieve the data
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = `
"Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; `
MultipleActiveResultSets = True"
[System.Collections.ArrayList]$sessions = @()
$strQuery = `
"SELECT Name AS AppName, LastUsed =
ISNULL((select Max(StartDate)
FROM monitordata.applicationinstance inst
JOIN monitordata.application app on app.id = inst.ApplicationId
where Name = application.Name), '01/01/1900')
FROM monitordata.application application"
We'll need a function to make the actual call to SQL and to fill a dataset with the results.
Function sqlquery ($q) {
$SqlQuery = $q
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
return $DataSet.tables[0]
}
We'll call the function, passing the SQL query that we created above.
[System.Collections.ArrayList]$apps = sqlquery -q $strQuery | ?{$_ -notlike "*[0-9]*"}
Finally, we calculate the cutoff date, and list the applications not used since then.
Write-Host "Applications not used in the past $($daysCovered) days:"
$cutoff = (Get-Date).AddDays(-$DaysCovered)
$apps | ? LastUsed -lt $cutoff |
Select AppName, @{Name="Last Used";Expression=`
{if($_.LastUsed -eq [DateTime]'1/1/1900') {'... never used...'} `
else {$_.LastUsed}} }
You can download the complete script here: Get-StaleApplications.ps1. Save the file on a machine with access to the monitoring database. Open a PowerShell command prompt or the PowerShell ISE, and execute:
<path to script>\Get-StaleApplications.ps1 nn
where nn is the cutoff number of days to report on. If you omit nn, the script defaults to 30 days.
Happy scripting!
Sam Jacobs is the Director of Technology at Newtek Technology Solutions (formerly IPM, the longest standing Citrix Platinum Partner on the East Coast). With more than 40 years of IT consulting, Sam is a Citrix NetScaler, StoreFront, and Web Interface customization and integration expert. He holds Microsoft Azure Developer and Citrix CCP-N certifications, and was a frequent contributor to the CUGC CTP blog. He has presented advanced breakout sessions at Citrix Synergy for 6 years on ADC (NetScaler) and StoreFront customizations and integration. He is one of the top Citrix Support Forum contributors, and has earned industry praise for the tools he has developed to make NetScaler, StoreFront, and Web Interface easier to manage for administrators and more intuitive for end users. Sam became a Citrix Technology Professional (CTP) in 2015, and can be reached at sjacobsCTP@gmail.com.
