• Citrix XenDesktop Personal vDisk Stats using Microsoft Excel and PowerShell

    August 9, 2013

    PowerShell, XenDesktop

    Recently a friend asked me for help in creating a way to see which of her Citrix XenDesktop Personal vDisk (PvD) users were using more than 90% of their allocated space. Citrix includes a PvD usage script with XenDesktop but at my level of PowerShell skills, I found the script confusing and convoluted. I decided to use a little of the Citrix script and roll my own that would create an Excel file. This article documents my PvD Stats to Excel script.

    Citrix provides a personal-vdisk-poolstats.ps1 script in the Support\Tools\Scripts folder. I used the core functionality of the script to use WMI to gather information on PvD usage but threw away the rest of the script. I wanted my script to do several things:

    • Create an Excel file
    • Create a Summary Worksheet with information on only the users with more than 90% PvD usage
    • Create a Worksheet for the XenDesktop Catalog or Catalogs processed
    • If PvD usage was between 50% and 90%, use a Yellow cell with bold black text
    • If PvD usage was more than 90%, use a Red cell with bold black text
    • Process each user on the Summary Worksheet and send an email
    • Before saving the Excel file, make the Summary Worksheet active so it has focus when the file is opened

    Update: Why Yellow and Red cells and not Yellow and Red text?  Personally I find Yellow text in a White cell difficult to read.  Plus, if you are color blind, Red text may not even be seen!  Using a Yellow or Red cell with Bold Black text allows those of us who are color blind to see that “something” is different.  My friend Michael B. Smith sent me this link a while back – http://li129-107.members.linode.com/accessibility/design/accessible-print-design/effective-color-contrast/ .  Take a look at that article and see what we color blind people have to deal with and why color choices are extremely important.

    I learned at least six new things in PowerShell creating this script:

    1. Conditional Formatting
    2. How to create and work with an Excel file
    3. How to send email
    4. Parameter Sets
    5. Splatting
    6. Try/Catch

    Parameter Sets

    There are five parameters that may be used in the script . If a specific Catalog should be processed, that can be specified. If the script should be processed against a remote Controller that can be specified To send an email three things are needed:

    1. Email server name – this is the fully qualified domain name of the SMTP server that will be used to send email; e.g., mail.example.com
    2. From address – this is the full email address used as the sender of the email; e.g., PvDScript@example.com. Please note that if your SMTP server is a Microsoft Exchange server, this must be a valid email address.
    3. Input file – this file contains all of the text for the body of the email.

    If any of those three are entered, all three need to be specified. It would not make sense to enter any of them alone.

    This required the use of Parameter Sets and a Default Parameter Set. I read several articles on Parameter Sets and just could not get it to work. My friend, and Exchange MVP, Michael B. Smith had to help me on this. What he showed me I never found in any article.

    Update: Someone asked if instead of hard-coding 50% and 90% if parameters could have been used.  Yes, looking back on it, that would have been a better choice to make.  Unfortunately, I no longer have access to a XenDesktop 5.x environment with PvD to make the change and test it.  I really don’t want to make a change to the script without first testing and validating the change works.  I could add $Min and $Max parameters to the P1 parameter set.  If someone can provide access to an environment for testing, I will gladly make the change.  Or I could provide you with a private updated script for someone to test adding the two parameters.  Email me at Webster@carlwebster.com if you are interested.

    [CmdletBinding( SupportsShouldProcess = $False, ConfirmImpact = "None", DefaultParameterSetName = "P1" ) ]
    
    Param(
    	[parameter(ParameterSetName="P1",
    	Position = 0,
    	Mandatory=$false )
    	]
    	[parameter(ParameterSetName="P2",
    	Position = 0,
    	Mandatory=$false )
    	]
    	[String]$XDCatalogName="",
    
    	[parameter(ParameterSetName="P1",
    	Position = 1,
    	Mandatory=$false )
    	]
    	[parameter(ParameterSetName="P2",
    	Position = 1,
    	Mandatory=$false )
    	]
    	[string]$AdminAddress="",
    
    	[parameter(ParameterSetName="P2",
    	Position = 2,
    	Mandatory=$true )
    	]
    	[string]$SmtpServer="",
    
    	[parameter(ParameterSetName="P2",
    	Position = 3,
    	Mandatory=$true )
    	]
    	[string]$From="",
    
    	[parameter(ParameterSetName="P2",
    	Position = 4,
    	Mandatory=$true )
    	]
    	[string]$InputFile="")
    

    Example:

    PS F:\> .\get-pvdstatsexcel.ps1 -XDCatalogName win7 -adminaddress DDC01 -from x@x.com -inputfile f:\emailbodytext.txt
    
    cmdlet Get-PvDStatsExcel.ps1 at command pipeline position 1
    Supply values for the following parameters:
    SmtpServer: mail.domain.tld
    
    

    Splatting

    I found as I was creating this script that I was using cmdlets with mostly the same parameters but maybe one difference between them. Michael showed me that splatting was a way around having to type in all the parameters every time. For example, what about the $AdminAddress parameter? If it is not passed, I need to do:

    Get-BrokerCatalog  -Filter "{ Name -eq $XDCatalogName }" –MaxRecordCOunt 65536 –EA 0
    

    But if the $AdminAddress parameter is passed, I need to do:

    Get-BrokerCatalog  -Filter "{ Name -eq $XDCatalogName }" –AdminAddress DDC01 –MaxRecordCount 65536 –EA 0
    

    Splatting works by using a hashtable and then you use the hashtable as the parameter to the cmdlet.

    Using the Get-BrokerCatalog example from above, I can do either:

    If(![System.String]::IsNullOrEmpty( $AdminAddress ))
    {
         Get-BrokerCatalog  -Filter "{ Name -eq $XDCatalogName }" –AdminAddress DDC01 –MaxRecordCount 65536 –EA 0
    }
    Else
    {
         Get-BrokerCatalog  -Filter "{ Name -eq $XDCatalogName }"  –MaxRecordCount 65536 –EA 0
    }
    

    Or I could do:

    $Params = @{
    Filter = "{ Name -eq $XDCatalogName }";
    MaxRecordCount = 65536;
    EA = 0;
    }
    
    If(![System.String]::IsNullOrEmpty( $AdminAddress ))
    {
    	$Params.AdminAddress = $AdminAddress
    }
    Get-BrokerCatalog @Params
    

    While method #2 has two more lines, I think it looks cleaner. It also simplifies maintenance of the script.

    Try/Catch

    One of the (many) things that confused me about the XenDesktop PowerShell cmdlets was that even with using –EA 0 (-ErrorAction SilentlyContinue) the cmdlets would sometimes still give me big ugly red errors. The way I found around this was by using Try/Catch.

    Here is an example using the validation of the $XDCatalogName parameter.

    If( ![System.String]::IsNullOrEmpty( $XDCatalogName ) )
    {
    	$status = $(try {Get-BrokerCatalog @Params} catch {$null})
    	if ($status -ne $null)
    	{
    	 	write-host "$XDCatalogName is a valid XenDesktop Catalog and is being retrieved"
    		$Params.CatalogName = $XDCatalogName
    	}
    	else
    	{
    	 	write-warning "Catalog $XDCatalogName was not found.  Script cannot continue."
    	 	Exit
    	}
    }
    Else
    {
    	Write-Host "Retrieving all XenDesktop Catalog names"
    }
    

    Even though “-EA 0” is one of the parameters, it was like the cmdlet refused to honor it. By using “catch{$null}”, I could actually “catch” the cmdlets refusal to honor –EA 0 and set the $status variable to $null. Then I could test $status and return an invalid Catalog name and stop the script.

    SHEEZ!, Citrix please fix your PPOC™ PowerShell!!!

    Update: PPOC = Pathetic Piece of Crap which is becoming my trademark phrase when I talk about Citrix PowerShell outside of XenApp and XenDesktop 4.

    Conditional Formatting

    This one took a little effort to fully grasp how and when to use. Conditional Formatting allows you to instruct PowerShell to precisely format data the exact way you want it formatted. You can use this while retrieving data, saving data and displaying data (and probably other ways also).

    The Scripting Guy has a good article on Conditional Formatting.

    Here I use it when saving an object so it is formatted exactly the way I want it to appear in Excel.

    Note from Michael: I would note that this is an indication of the strong binding between PowerShell and .NET. The formatting operators are the same formatting operators used in all .NET languages including C#, F#, VB.NET, etc. And the formatting operations are very different than Excel’s – because Excel isn’t based on .NET.

    $PVDObject = New-Object -Type PSObject -Property @{
    	VMName             = $VMName
    	UserName           = $UserName
    	UserADName         = $UserADName
    	PVDServiceStatus   = $PVDServiceStatus
    	PVDStatus          = $PVDStatus
    	AppGB              = "{0:N1}" -f $AppGB
    	AppPercentUsed     = "{0:F2}" -f $AppPercentUsed
    	ProfileGB          = "{0:N1}" -f $ProfileGB
    	ProfilePercentUsed = "{0:F2}" -f $ProfilePercentUsed
    	TotalPercentUsed   = "{0:F2}" -f $TotalPercentUsed
    	UpdateStatus       = $UpdateStatus
    	}
    

    Sending Email

    Sending email was easier than I thought since that functionality is now built-in to PowerShell.

    For each user in the Summary Worksheet, they receive an email. In order to send the email, the SMTP Server, From Address and message body text are needed for every email sent. That information is gathered outside the processing loop since that data will never change for any email.

    Inside the processing loop, a valid email address is required. Then the users email address is retrieved, a subject line created, email body created and finally the email is sent.

    If(![System.String]::IsNullOrEmpty( $SmtpServer ))
    {
    	write-host "Sending emails"
    	#send an email to each person on the summary worksheet
    	$emailSmtpServer = $SmtpServer
    	$emailFrom = $From
    	$emailBodyText = Get-Content $InputFile
    	ForEach($Item in $SummaryObjects)
    	{
    		If($Item.EmailAddress -ne "Unassigned")
    		{
    			write-host "`tSending email to $($Item.EmailAddress)"
    			$emailTo = $Item.EmailAddress
    			$emailSubject = "$($Item.VMName) - Action needed to continue use of your Virtual Desktop"
    			$emailBody = @"
    				`nHello $($Item.UserName),
    
    				$($emailBodyText)
    				"@
    			Send-MailMessage -To $emailTo -From $emailFrom -Subject $emailSubject -Body $emailBody -SmtpServer $emailSmtpServer -Port 25
    		}
    	}
    }
    

    Working With Excel

    This was so much “fun” learning stuff that is not documented very well. Let me just walk you through the Excel stuff.

    Create the Excel object and hide Excel to speed up processing.

    $Excel = New-Object -comobject "Excel.Application"
    $Excel.Visible = $False
    

    Determine how many Worksheets there are.

    Update: There are a minimum of two Worksheets needed if a specific XenDesktop Catalog is specified as a parameter: Summary and the Catalog.  If all Catalogs are processed then an additional Worksheet is needed for the Summary.

    If($TotalCatalogs -lt 1)
    {
        $Excel.sheetsInNewWorkbook = 2
    }
    Else
    {
        $Excel.sheetsInNewWorkbook = $TotalCatalogs + 1
    }
    

    Add the WorkBooks, get the Active Worksheet, set the Active Worksheet to the first Worksheet.

    $WB = $Excel.WorkBooks.Add()
    #get active worksheet
    $WS = $WB.ActiveSheet
    [int]$i = 1
    
    #process summary CSV file first so it is the first worksheet
    $WS = $WB.WorkSheets.Item("Sheet$i")
    

    Activate the Worksheet, Freeze row 1 where the column headings will be placed and name the Worksheet.

     [void] $WS.Activate()
    $WS.Application.ActiveWindow.SplitRow = 1
    $WS.Application.ActiveWindow.FreezePanes = $true
    $WS.Name = "Summary"
    

    Get the Cells in the Worksheet and build the column headings.

    Update: This is where I found the numbers to use for the ColorIndex – http://dmcritchie.mvps.org/excel/colors.htm

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

    Process the Summary Worksheet first.

    foreach($Stat in $Stats)
    {
    	write-host "`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 = 3
    		$Cells.Item($xRow,5).Font.Bold = $True
    	}
    	$Cells.Item($xRow,5) = [double]$Stat.AppGB
    	Switch ([double]$Stat.AppPercentUsed)
    	{
    		{($_ -ge 50)  -and ($_ -lt 90)}
    			{
    				$Cells.Item($xRow,6).Interior.ColorIndex = 6
    				$Cells.Item($xRow,6).Font.Bold = $True
    			}
    		{($_ -ge 90) -or ($_ -eq 0) }
    			{
    				$Cells.Item($xRow,6).Interior.ColorIndex = 3
    				$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 = 3
    		$Cells.Item($xRow,7).Font.Bold = $True
    	}
    	$Cells.Item($xRow,7) = [double]$Stat.ProfileGB
    	Switch ([double]$Stat.ProfilePercentUsed)
    	{
    		{($_ -ge 50) -and ($_ -lt 90)}
    			{
    				$Cells.Item($xRow,8).Interior.ColorIndex = 6
    				$Cells.Item($xRow,8).Font.Bold = $True
    		}
    		{($_ -ge 90) -or ($_ -eq 0)}
    			{
    				$Cells.Item($xRow,8).Interior.ColorIndex = 3
    				$Cells.Item($xRow,8).Font.Bold = $True
    			}
    	}
    	$Cells.Item($xRow,8) = [double]$Stat.ProfilePercentUsed
    	Switch ([double]$Stat.TotalPercentUsed)
    	{
    		{($_ -ge 50) -and ($_ -lt 90)}
    			{
    				$Cells.Item($xRow,9).Interior.ColorIndex = 6
    				$Cells.Item($xRow,9).Font.Bold = $True
    			}
    		{($_ -ge 90) -or ($_ -eq 0) }
    			{
    				$Cells.Item($xRow,9).Interior.ColorIndex = 3
    				$Cells.Item($xRow,9).Font.Bold = $True
    			}
    	}
    	$Cells.Item($xRow,9) = [double]$Stat.TotalPercentUsed
    
    	$xRow++
    }
    

    Format the columns.

    $ws.columns.item("E:E").EntireColumn.NumberFormat = "#0.0"
    $ws.columns.item("F:F").EntireColumn.NumberFormat = "#0.00"
    $ws.columns.item("G:G").EntireColumn.NumberFormat = "#0.0"
    $ws.columns.item("H:I").EntireColumn.NumberFormat = "#0.00"
    $ws.columns.item("A:I").EntireColumn.AutoFit() | out-null
    

    The other Worksheets are basically the same.

    Activate the Summary worksheet so when the file is opened, the Summary sheet is displayed

    $WS = $WB.WorkSheets.Item("Summary")
    [void] $WS.Activate()
    

    We don’t need any popups or alerts from Excel during the Saving and Closing process. Save the Excel file, quit Excel and cleanup the system.

    Update: Excel SaveAs file format information is found here – http://msdn.microsoft.com/en-us/library/office/ff198017(v=office.14).aspx

    $Excel.DisplayAlerts = $False
    $wb.saveas("$($pwd.path)\PvDStats_$(Get-Date -f yyyy-MM-dd).xlsx",51)
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | out-null
    Remove-Variable -Name excel
    [gc]::collect()
    [gc]::WaitForPendingFinalizers()
    write-host "Excel file $($pwd.path)\PvDStats_$(Get-Date -f yyyy-MM-dd).xlsx is ready for use"
    

    Most of the Excel numbers, such as $wb.saveas 51, were found by looking through various VB scripts that exist on the Internet. On the system I was on, I found out that I had to use the actual numbers and not variables.

    I could not do something like:

    $SaveAsFormat = 51
    $wb.saveas("$($pwd.path)\PvDStats_$(Get-Date -f yyyy-MM-dd).xlsx",$SaveAsFormat)
    

    I would get a weird COMObject error. Using just the numbers always worked for me. It may be different for you, I am just saying what happened on the specific system where I was creating the script .

    Note: Michael says this may be because I did not strongly type my variable by using [int]$SaveAsFormat = 51

    For those of you brave enough to use PvD in a production environment , I hope you find the script useful.

    For those of you who find this article while looking for how to use Excel with PowerShell, I hope there is enough information in my script to help you. I know there is not much useful information out there on using PowerShell to create Excel files.

    I was only able to test this script with XenDesktop 5.6 and Microsoft Excel 2010. I have no way of knowing if it works with Excel 2007 or 2013 or XenDesktop 5, 5.5, 5,6 FP1 or 7.

    Update: If you run this script on XenDesktop 5, 5.5, 5,6 FP1 or 7 or using Excel 2007 or Excel 2013, please let me know and I will update this article so others will know.

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

    You can always find the most current script by going to https://carlwebster.com/where-to-get-copies-of-the-documentation-scripts/

    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

    2 Responses to “Citrix XenDesktop Personal vDisk Stats using Microsoft Excel and PowerShell”

    1. Brett Says:

      Awesome script exactly what I was trying to do. One problem I have picked up is the following.
      I have a catalog called “HVD – Persistent TINTRI” when i run the following .\script.ps1 -XDCatalogName “HVD – Persistent TINTRI” -AdminAddress nbpxend7qa2 it fails. If I manually add it into the script it also fails….BUT….If I run the script with no catalogs it works and runs through them all.

      Reply

    Leave a Reply