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

February 1, 2014

Blog, PowerShell, XenDesktop

Yeah, I know.  I just updated the script a couple of days ago and here is another update.  Well, I just couldn’t stand it that the script couldn’t support sending email over anything but the default SMTP port 25.  I just had to fix that but doing so required basing the script on PowerShell version 3.  The script now requires at least PowerShell version 3 and there are a couple of more parameters.


The script will not work for sending emails if you are using PowerShell version 2 so the first line in the script is now:

#Requires -Version 3

The two new parameters are SmtpPort and UseSSL. SmtpPort sets the SMTP port and UseSSL tells whether to use SSL for making the connection to the SmtpServer.

In order to send authenticated SMTP emails, a username and password are required.

$emailCredentials = Get-Credential -Message "Enter the email account and password to send emails"
Email credentials

Email credentials

The credentials are the same used when configuring an email client.  i.e. [email protected] and the account’s password. I tested this using my Office365 account and it worked with no issues.

The email body is read in from a file.  I use the -BodyAsHtml parameter for Send-MailMessage.  To get line breaks just use <br /> at the end of each line and for spacing lines.  The file I used for my testing looks like this:

<br />
<br />
Your Personal vDisk space has reached over 90% of its capacity.   <br />
You should resolve this as soon as possible before your Personal <br />
vDisk fills up and needs to be reset.  When that happens, you <br />
will need to reinstall your applications.<br />
<br />
You should move your 'User Data' from your C and or PvD drive <br />
and it should be kept on your home drive.  The free space on your <br />
workstation is to be used for application installs, temp files, <br />
and other general system and application configurations.<br />
<br />
Let us know if you need any help.<br />
<br />
Thanks<br />
<br />
<br />
The XenDesktop Help Desk Team at CarlWebster.com<br />

Which looks like this in Outlook 2010.

Email received by user

Email received by user

Example of running the script:

.\get-pvdstatsexcel.ps1 -AdminAddress xd71studio1 -XDVersion 7 -SmtpServer smtp.office365.com -SmtpPort 587 -UseSSL -From [email protected] -InputFile c:\webster\emailbody.txt -verbose

Example screen output:

PS C:\webster> .\get-pvdstatsexcel.ps1 -AdminAddress xd71studio1 -XDVersion 7 -SmtpServer smtp.office365.com -SmtpPort 5
87 -UseSSL -From [email protected] -InputFile c:\webster\emailbody.txt -verbose
VERBOSE: 02/01/2014 09:45:00: Retrieving all XenDesktop Catalog names
VERBOSE: 02/01/2014 09:45:00:
VERBOSE: 02/01/2014 09:45:00:
VERBOSE: 02/01/2014 09:45:00: Using Controller   : xd71studio1
VERBOSE: 02/01/2014 09:45:00: XenDesktop version : 7
VERBOSE: 02/01/2014 09:45:00: Smtp Server        : smtp.office365.com
VERBOSE: 02/01/2014 09:45:00: Smtp Port          : 587
VERBOSE: 02/01/2014 09:45:00: Use SSL            : True
VERBOSE: 02/01/2014 09:45:00: From               : [email protected]
VERBOSE: 02/01/2014 09:45:00: Input File         : c:\webster\emailbody.txt
VERBOSE: 02/01/2014 09:45:00: PoSH version       : 3.0
VERBOSE: 02/01/2014 09:45:00:
VERBOSE: 02/01/2014 09:45:00: Script start       : 02/01/2014 09:45:00
VERBOSE: 02/01/2014 09:45:00:
VERBOSE: 02/01/2014 09:45:00:
VERBOSE: 02/01/2014 09:45:00: Get VMs for Catalog Win7 WC PvD Test
VERBOSE: 02/01/2014 09:45:00:  Processing VM Win7-PvD-01
VERBOSE: 02/01/2014 09:45:01:  Processing VM Win7-PvD-02
VERBOSE: 02/01/2014 09:45:01:  Processing VM Win7-PvD-03
VERBOSE: 02/01/2014 09:45:01: Creating CSV file C:\webster\Win7 WC PvD Test_PvD_Stats.csv
VERBOSE: Performing operation "Export-Csv" on Target "C:\webster\Win7 WC PvD Test_PvD_Stats.csv".
VERBOSE: 02/01/2014 09:45:01: Creating Summary CSV file C:\webster\Summary_PvD_Stats.csv
VERBOSE: Performing operation "Export-Csv" on Target "C:\webster\Summary_PvD_Stats.csv".
VERBOSE: 02/01/2014 09:45:01: Sending emails
VERBOSE: 02/01/2014 09:45:14:  Sending email to [email protected]
VERBOSE: 02/01/2014 09:45:19: CSV Processing complete
VERBOSE: 02/01/2014 09:45:19: Start creating Excel file and worksheets
VERBOSE: 02/01/2014 09:45:19: 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: 02/01/2014 09:45:19: Get Summary CSV file
VERBOSE: 02/01/2014 09:45:21:  Adding summary row for Win7-PvD-02
VERBOSE: 02/01/2014 09:45:22: Deleting C:\webster\Summary_PvD_Stats.csv
VERBOSE: Performing operation "Remove file" on Target "C:\webster\Summary_PvD_Stats.csv".
VERBOSE: 02/01/2014 09:45:22: Summary sheet completed
VERBOSE: 02/01/2014 09:45:22: Start processing catalogs
VERBOSE: 02/01/2014 09:45:23: Get CSV file for Catalog Win7 WC PvD Test
VERBOSE: 02/01/2014 09:45:24:  Adding row for Win7-PvD-01
VERBOSE: 02/01/2014 09:45:25:  Adding row for Win7-PvD-02
VERBOSE: 02/01/2014 09:45:25:  Adding row for Win7-PvD-03
VERBOSE: 02/01/2014 09:45:26: Deleting C:\webster\Win7 WC PvD Test_PvD_Stats.csv
VERBOSE: Performing operation "Remove file" on Target "C:\webster\Win7 WC PvD Test_PvD_Stats.csv".
VERBOSE: 02/01/2014 09:45:26: Processing worksheets is complete
VERBOSE: 02/01/2014 09:45:26: Saving Excel file
VERBOSE: Performing operation "Remove variable" on Target "Name: Excel".
VERBOSE: 02/01/2014 09:45:26: Excel file C:\webster\PvDStats_2014-02-01.xlsx is ready for use
VERBOSE: Performing operation "Stop-Process" on Target "EXCEL (3324)".
VERBOSE: 02/01/2014 09:45:26: Script started: 02/01/2014 09:45:00
VERBOSE: 02/01/2014 09:45:26: Script ended: 02/01/2014 09:45:26
VERBOSE: 02/01/2014 09:45:26: Elapsed time: 0 days, 0 hours, 0 minutes, 26.5 seconds
PS C:\webster>

What happens if you are supposed to use SSL and you don’t?

VERBOSE: 02/01/2014 09:47:43: Sending emails
VERBOSE: 02/01/2014 09:47:52:  Sending email to [email protected]
Send-MailMessage : The SMTP server requires a secure connection or the client was not authenticated. The server
response was: 5.7.1 Client was not authenticated
At C:\webster\Get-PvDStatsExcel.ps1:746 char:6
+                     Send-MailMessage -To $emailTo -Subject $emailSubject -Body $emailBody -Smtp ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (System.Net.Mail.SmtpClient:SmtpClient) [Send-MailMessage], SmtpExcept
ion
+ FullyQualifiedErrorId : SmtpException,Microsoft.PowerShell.Commands.SendMailMessage

The rest of the script will continue to run and the Excel file is created and saved.  Only the emails will not be sent.

I believe I have included lots of great help text.

PS C:\webster> get-help .\get-pvdstatsexcel.ps1 -full
NAME
    C:\webster\Get-PvDStatsExcel.ps1

SYNOPSIS
    Creates an Excel spreadsheet containing Citrix XenDesktop Personal vDisk (PvD) usage statistics.

SYNTAX
    C:\webster\Get-PvDStatsExcel.ps1 [[-XDCatalogName] <String>] [[-AdminAddress] <String>] [[-XDVersion] <Int32>] [<CommonParameters>]

    C:\webster\Get-PvDStatsExcel.ps1 [[-XDCatalogName] <String>] [[-AdminAddress] <String>] [[-XDVersion] <Int32>] [-SmtpServer] <String> [[-SmtpPort] <Int32>] [[-UseSSL]] [-From] <String> [-InputFile] <String> [<CommonParameters>]

DESCRIPTION
    Take a XenDesktop Catalog name (or all Catalogs) and gathers PvD usage stats.
    Creates a Summary worksheet with users who have any PvD with > 90% usage.
    Summary worksheet has the user's Active Directory name and email address.

PARAMETERS
    -XDCatalogName <String>
        XenDesktop Catalog name.  If not entered, process all Catalogs.

        Required?                    false
        Position?                    1
        Default value
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -AdminAddress <String>
        Specifies the address of a XenDesktop controller that the PowerShell script will connect to.  This can be provided as a host name or an IP address.

        Required?                    false
        Position?                    2
        Default value
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -XDVersion <Int32>
        Specifies the XenDesktop version.
        5 is for all versions of XenDesktop 5.
        7 is for all versions of XenDesktop 7.
        Default is XenDesktop 7.

        Required?                    false
        Position?                    3
        Default value                7
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -SmtpServer <String>
        Specifies the optional email server to send emails to users on the Summary worksheet.

        Required?                    true
        Position?                    4
        Default value
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -SmtpPort <Int32>
        Specifies the SMTP port.

        Required?                    false
        Position?                    5
        Default value                25
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -UseSSL [<SwitchParameter>]
        Specifies whether to use SSL for the SmtpServer.
        Default is False.

        Required?                    false
        Position?                    6
        Default value                False
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -From <String>
        Specifies the username for the From email address.
        If SmtpServer is used, this is a required parameter.

        Required?                    true
        Position?                    7
        Default value
        Accept pipeline input?       false
        Accept wildcard characters?  false

    -InputFile <String>
        The file that contains the text for the body of the email.
        If SmtpServer is used, this is a required parameter.
        Text is treated as HTML.

        Required?                    true
        Position?                    8
        Default value
        Accept pipeline input?       false
        Accept wildcard characters?  false

    <CommonParameters>
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

    INPUTS
        None.  You cannot pipe objects to this script.

    OUTPUTS
        Creates a Microsoft Excel spreadsheet.
        This has only been tested with Microsoft Excel 2010.
        Optional, creates and sends an email for every user on the Summary Worksheet.

    NOTES

        NAME: Get-PvDStatsExcel.ps1
        VERSION: 2.1
        AUTHOR: Carl Webster with help, as usual, from Michael B. Smith
        LASTEDIT: February 1, 2014

    -------------------------- EXAMPLE 1 --------------------------
    PS C:\PSScript >.\Get-PvDStatsExcel.ps1

    Will process all XenDesktop Catalogs.
    AdminAddress is the computer running the script for the AdminAddress.
    Will load Citrix SnapIn for XenDesktop 7.

    -------------------------- EXAMPLE 2 --------------------------
    PS C:\PSScript >.\Get-PvDStatsExcel.ps1 -XDVersion 5

    Will process all XenDesktop Catalogs.
    AdminAddress is the computer running the script for the AdminAddress.
    Will load Citrix SnapIn for XenDesktop 5.

    -------------------------- EXAMPLE 3 --------------------------
    C:\PS>PS C:\PSScript .\Get-PvDStatsExcel.ps1 -XDCatalogName Win7Image1 -AdminAddress DDC01

    Will gather PvD usage stats for the Win7Image1 Catalog
    using a Controller named DDC01 for the AdminAddress.
    Will load Citrix SnapIn for XenDesktop 7.

    -------------------------- EXAMPLE 4 --------------------------
    PS C:\PSScript >.\Get-PvDStatsExcel.ps1 -SmtpServer mail.domain.tld -From [email protected] -InputFile C:\Scripts\EmailBodyText.txt

    Will process all XenDesktop Catalogs.
    AdminAddress is the computer running the script for the AdminAddress.
    Will load Citrix SnapIn for XenDesktop 7.
    Uses mail.domain.tld as the email server.
    Uses the default SMTP port 25.
    Will not use SSL.
    Uses [email protected] for the email's Sent From address.
    Reads the contents of C:\Scripts\EmailBodyText.txt and uses the contents as the email body.

    -------------------------- EXAMPLE 5 --------------------------
    PS C:\PSScript >.\Get-PvDStatsExcel.ps1 -SmtpServer smtp.office365.com -SmtpPort 587 -UseSSL -From [email protected] -InputFile C:\Scripts\EmailBodyText.txt

    Will process all XenDesktop Catalogs.
    AdminAddress is the computer running the script for the AdminAddress.
    Will load Citrix SnapIn for XenDesktop 7.
    Uses smtp.office365.com.
    User SMTP port 587.
    Uses [email protected] for the email's Sent From address (this must be a valid address on the email system).
    Reads the contents of C:\Scripts\EmailBodyText.txt and uses the contents as the email body.

    -------------------------- EXAMPLE 6 --------------------------
    C:\PS>Sample input file.

    <br />
    <br />
    Your Personal vDisk space has reached over 90% of its capacity.   <br />
    You should resolve this as soon as possible before your Personal <br />
    vDisk fills up and needs to be reset.  When that happens, you <br />
    will need to reinstall your applications.<br />
    <br />
    You should move your 'User Data' from your C and or PvD drive <br />
    and it should be kept on your home drive.  The free space on your <br />
    workstation is to be used for application installs, temp files, <br />
    and other general system and application configurations.<br />
    <br />
    Let us know if you need any help.<br />
    <br />
    Thanks<br />
    <br />
    <br />
    The XenDesktop Help Desk Team at CarlWebster.com<br />

RELATED LINKS

PS C:\webster>

I hope you find the changes 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