Citrix XenDesktop Personal vDisk Stats using Microsoft Excel and PowerShell V2.0

January 29, 2014

PowerShell, XenDesktop

In August 2013, I released the original version of this script and noted it had only been tested with XenDesktop 5.6.  I have now tested the script with XenDesktop 7.1 and boy did I have some cleanup work to do.  This article will highlight the changes made to clean the script up and get it working in XenDesktop 7.1.

The first thing I noticed when I ran the script was that XenDesktop 5.x and XenDesktop 7.x use a different PowerShell Snapin.  XenDesktop 5.x uses Citrix.Broker.Admin.V1 and XenDesktop 7.x uses Citrix.Broker.Admin.V2.  That was it, that was the ONLY thing that had to be changed in the script to get it working for XenDesktop 7.1.  All I had to do was add a new parameter, XDVersion, to specify if the script is running for XenDesktop 5.x or 7.x.  That was easy.

-XDVersion 5 means the script will be run against a XenDesktop 5.x Site

-XDVersion 7 means the script will be run against a XenDesktop 7.x Site

The real problems came because of a bunch of stupid assumptions I made in the original script.  I assumed there would always be some user’s PvD that crossed the 90% usage threshold and therefore there would always be a Summary worksheet.  OOPS!  That bit me in the butt.  I had several code changes to make to handle that.  Now I check and if there are no PvDs that meet that condition, I set a variable and check it in several places in the script.  If there are no Summary items, then there is no need to create a Summary worksheet, no need to process a Summary.csv file that doesn’t exist and no need to make the non-existent Summary worksheet the active Worksheet before the spreadsheet is saved.  One bad assumption and the script crumbled!  Hey, like I always say (regardless of what Joe Nord thinks) I am NOT a programmer or developer.

The next series of changes I made had more to do with making Remko Weijnen and Michael B. Smith happy.  I didn’t want Remko whacking me upside the head the next time he saw me at a conference. 🙂

I changed all Write-Host statements to Write-Verbose.  That should please Don Jones and Jeff Wouters also.

In the original script, I used the numbers 3, 6 and 15 for colors.  Quick what is colorindex code 3?  Crap, I had to go look it up so I had to fix that.

#color values are from msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx
[int]$XLRed = 3
[int]$XLYellow = 6
[int]$XLGrey = 15

The next thing Remko would not have liked is that I used hard coded values for the Warning and Error levels: 50 and 90. That changed.

#warning and error values
[int]$WarningLimit = 50
[int]$ErrorLimit = 90

Now the code should be more readable and Remko will not have to whack my head at E2E or Synergy. 🙂

$Cells.Item($xRow,1).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,1).Font.Bold = $True
$Cells.Item($xRow,1) = "VMName"
$Cells.Item($xRow,2).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,2).Font.Bold = $True
$Cells.Item($xRow,2) = "User Name"
$Cells.Item($xRow,3).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,3).Font.Bold = $True
$Cells.Item($xRow,3) = "User AD Name"
$Cells.Item($xRow,4).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,4).Font.Bold = $True
$Cells.Item($xRow,4) = "Email Address"
$Cells.Item($xRow,5).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,5).Font.Bold = $True
$Cells.Item($xRow,5) = "App GB"
$Cells.Item($xRow,6).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,6).Font.Bold = $True
$Cells.Item($xRow,6) = "App % Used"
$Cells.Item($xRow,7).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,7).Font.Bold = $True
$Cells.Item($xRow,7) = "Profile GB"
$Cells.Item($xRow,8).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,8).Font.Bold = $True
$Cells.Item($xRow,8) = "Profile % Used"
$Cells.Item($xRow,9).Interior.ColorIndex = $XLGrey
$Cells.Item($xRow,9).Font.Bold = $True
$Cells.Item($xRow,9) = "Total % Used"

and also

ForEach($Stat in $Stats)
{
    Write-Verbose  "$(Get-Date): `tAdding summary row for $($Stat.VMName)"

    $Cells.Item($xRow,1) = $stat.VMName
    $Cells.Item($xRow,2) = $Stat.UserName
    $Cells.Item($xRow,3) = $Stat.UserADName
    $Cells.Item($xRow,4) = $Stat.EmailAddress
    If([double]$Stat.AppGB -eq 0)
    {
        $Cells.Item($xRow,5).Interior.ColorIndex = $XLRed
        $Cells.Item($xRow,5).Font.Bold = $True
    }
    $Cells.Item($xRow,5) = [double]$Stat.AppGB
    Switch ([double]$Stat.AppPercentUsed)
    {
        {($_ -ge $WarningLimit)  -and ($_ -lt $ErrorLimit)}
            {
                $Cells.Item($xRow,6).Interior.ColorIndex = $XLYellow
                $Cells.Item($xRow,6).Font.Bold = $True
            }
        {($_ -ge $ErrorLimit) -or ($_ -eq 0) }
            {
                $Cells.Item($xRow,6).Interior.ColorIndex = $XLRed
                $Cells.Item($xRow,6).Font.Bold = $True
            }
    }

    $Cells.Item($xRow,6) = [double]$Stat.AppPercentUsed
    If([double]$Stat.ProfileGB -eq 0)
    {
        $Cells.Item($xRow,7).Interior.ColorIndex = $XLRed
        $Cells.Item($xRow,7).Font.Bold = $True
    }
    $Cells.Item($xRow,7) = [double]$Stat.ProfileGB
    Switch ([double]$Stat.ProfilePercentUsed)
    {
        {($_ -ge $WarningLimit) -and ($_ -lt $ErrorLimit)}
            {
                $Cells.Item($xRow,8).Interior.ColorIndex = $XLYellow
                $Cells.Item($xRow,8).Font.Bold = $True
            }
        {($_ -ge $ErrorLimit) -or ($_ -eq 0)}
            {
                $Cells.Item($xRow,8).Interior.ColorIndex = $XLRed
                $Cells.Item($xRow,8).Font.Bold = $True
            }
    }
    $Cells.Item($xRow,8) = [double]$Stat.ProfilePercentUsed
    Switch ([double]$Stat.TotalPercentUsed)
    {
        {($_ -ge $WarningLimit) -and ($_ -lt $ErrorLimit)}
            {
                $Cells.Item($xRow,9).Interior.ColorIndex = $XLYellow
                $Cells.Item($xRow,9).Font.Bold = $True
            }
        {($_ -ge $ErrorLimit) -or ($_ -eq 0) }
            {
                $Cells.Item($xRow,9).Interior.ColorIndex = $XLRed
                $Cells.Item($xRow,9).Font.Bold = $True
            }
        }
    $Cells.Item($xRow,9) = [double]$Stat.TotalPercentUsed

    $xRow++
}

One thing I noticed is that if there are a lot of Machine Catalogs to be processed, I create a CSV file for each catalog. And like a bad neighbor, I left all the CSV files in the folder when the script was done. Fixed.

#no longer need CSV file so delete it
Write-Verbose "$(Get-Date): Deleting $($csvFile)"
Remove-Item $csvFile -EA 0

Next I changed from using .\ for the folder where the script is run from to using $pwd.

Write-Verbose  "$(Get-Date): Creating CSV file $($pwd.path)\$($FileName)_PvD_Stats.csv"
$PVDObjects | `
select-object VMName, UserName, UserADName, PVDServiceStatus, PVDStatus,
AppGB, AppPercentUsed, ProfileGB, ProfilePercentUsed, TotalPercentUsed, UpdateStatus | `
Sort-Object VMname | `
Export-CSV "$($pwd.path)\$($FileName)_PvD_Stats.csv" -NoTypeInformation
$CSVFiles += "$($pwd.path)\$($FileName)_PvD_Stats.csv"

There is the option to send an email to each person listed in the Summary Worksheet. I was using the -Port parameter for the Send-MailMessage cmdlet and found out that the -Port parameter is not supported in PowerShell 2.0. I had to remove the -Port parameter. That means, there is no way to configure the script to use anything but SMTP port 25 so no Authenticated SMTP mail.

I had to change the way I quit Excel so the Excel.exe process will terminate properly. My problem is that the original method and the new method do not always result in the Excel.exe process terminating. So I do what any self-respecting southern boy would do, I find the process running in the user’s session and kill it!

Write-Verbose  "$(Get-Date): Processing worksheets is complete"
Write-Verbose  "$(Get-Date): Saving Excel file"
$Excel.DisplayAlerts = $False
#xlsx
$xlOpenXMLWorkbook = 51
$wb.saveas("$($pwd.path)\PvDStats_$(Get-Date -f yyyy-MM-dd).xlsx", $xlOpenXMLWorkbook)
$Excel.Quit()
While( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Cells)){}
While( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WS)){}
While( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WB)){}
While( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){}
Remove-Variable Excel
Write-Verbose  "$(Get-Date): Excel file $($pwd.path)\PvDStats_$(Get-Date -f yyyy-MM-dd).xlsx is ready for use"
#If the Excel.exe process is still running for the user's sessionID, kill it
(Get-Process 'Excel' -ea 0 | ?{$_.sessionid -eq $Sessionid}) | stop-process

I also added an elapsed time to the script just like I did for the XenApp and PVS documentation scripts.

Here is a the script running for my XenDesktop 7.1 lab with three desktops using PvD. The script is being run from a Windows 7 PC. The script does not exist on the Controller and the Controller does not have Excel installed on it.

<span style="font-family: Consolas;">PS C:\webster> .\get-pvdstatsexcel.ps1 -adminaddress xd71studio1 -verbose
VERBOSE: 01/29/2014 23:39:23: Retrieving all XenDesktop Catalog names
VERBOSE: 01/29/2014 23:39:23: Get VMs for Catalog Win7 WC PvD Test
VERBOSE: 01/29/2014 23:39:23:  Processing VM Win7-PvD-01
VERBOSE: 01/29/2014 23:39:24:  Processing VM Win7-PvD-02
VERBOSE: 01/29/2014 23:39:24:  Processing VM Win7-PvD-03
VERBOSE: 01/29/2014 23:39:24: Creating CSV file C:\webster\Win7 WC PvD Test_PvD_Stats.csv
VERBOSE: 01/29/2014 23:39:24: Creating Summary CSV file C:\webster\Summary_PvD_Stats.csv
VERBOSE: 01/29/2014 23:39:24: CSV Processing complete
VERBOSE: 01/29/2014 23:39:24: Start creating Excel file and worksheets
VERBOSE: 01/29/2014 23:39:24: Setup Excel
VERBOSE: The object written to the pipeline is an instance of the type
"Microsoft.Office.Interop.Excel.ApplicationClass" from the component's primary interop assembly. If this type exposes
different members than the IDispatch members, scripts written to work with this object might not work if the primary
interop assembly is not installed.
VERBOSE: 01/29/2014 23:39:24: Get Summary CSV file
VERBOSE: 01/29/2014 23:39:25:  Adding summary row for Win7-PvD-02
VERBOSE: 01/29/2014 23:39:26: Deleting C:\webster\Summary_PvD_Stats.csv
VERBOSE: 01/29/2014 23:39:26: Summary sheet completed
VERBOSE: 01/29/2014 23:39:26: Start processing catalogs
VERBOSE: 01/29/2014 23:39:26: Get CSV file for Catalog Win7 WC PvD Test
VERBOSE: 01/29/2014 23:39:27:  Adding row for Win7-PvD-01
VERBOSE: 01/29/2014 23:39:27:  Adding row for Win7-PvD-02
VERBOSE: 01/29/2014 23:39:28:  Adding row for Win7-PvD-03
VERBOSE: 01/29/2014 23:39:28: Deleting C:\webster\Win7 WC PvD Test_PvD_Stats.csv
VERBOSE: 01/29/2014 23:39:28: Processing worksheets is complete
VERBOSE: 01/29/2014 23:39:28: Saving Excel file
VERBOSE: 01/29/2014 23:39:28: Excel file C:\webster\PvDStats_2014-01-29.xlsx is ready for use
VERBOSE: 01/29/2014 23:39:28: Script started: 01/29/2014 23:39:23
VERBOSE: 01/29/2014 23:39:28: Script ended: 01/29/2014 23:39:28
VERBOSE: 01/29/2014 23:39:28: Elapsed time: 0 days, 0 hours, 0 minutes, 5.23 seconds
PS C:\webster></span>

Here is the Summary Worksheet.

Summary Worksheet

Summary Worksheet

And the other Worksheet.

Second Worksheet

Second Worksheet

I hope you find this script useful.

If you find any issues, please let me know and I will do my best to get them fixed.

I have placed an unsigned copy of this script at https://dl.dropboxusercontent.com/u/43555945/Get-PvDStatsExcel.ps1

I have also placed a copy of the script with a TXT extension at https://dl.dropboxusercontent.com/u/43555945/Get-PvDStatsExcel.txt

I have placed a digitally signed copy of this script at https://dl.dropboxusercontent.com/u/43555945/Get-PvDStatsExcel_Signed.ps1

I have also placed a copy of the digitally signed script with a TXT extension at https://dl.dropboxusercontent.com/u/43555945/Get-PvDStatsExcel_Signed.txt

Thanks

Webster

, , , , ,

About Carl Webster

Webster is a Sr. Solutions Architect for Choice Solutions, LLC and specializes in Citrix, Active Directory and Technical Documentation. Webster has been working with Citrix products for many years starting with Multi-User OS/2 in 1990.

View all posts by Carl Webster

No comments yet.

Leave a Reply