App-V PowerShell: Script to Query XenApp Servers for App-V Publishing Errors and Output an Excel Document with the Results

App-V PowerShell:  Script to Query XenApp Published Desktops for App-V Publishing Errors and Output an Excel Document with the Results.  The XenApp Published Dekstops are presented as a list that can be filtered.  Multiple Published Desktops can be selected.  Once the script has completed, emails are sent to the configured recipients.   I prefer to run this in the PowerShell ISE so that I can view the verbose messages.

! The Citrix.XenApp.Commands Snapin is requred.

There are three scripts included that need to be created.  

• AppV-Publishing-Error-Report.ps1 - The main script
• AppVCustomFunctions.psm1 - Needs to be placed in the location you define in $ModuleLoc.  This module is used to define the Error code you are querying against and how far back you wish to pull data for. 
• ConvertCVStoExcel.psm1 - Needs to be placed in the location you define in $ModuleLoc

# ============================================================
#
# NAME: AppV-Publishing-Error-Report.ps1
#
# AUTHOR(s): Cody Lambert (MVP)
# DATE  : 7/11/2014
#
# Pre-Reqs:  Citrix.XenApp.Commands SnapIn, AppVCustomFunctions.psm1 & ConvertCVStoExcel.psm1 (included at bottom of page)
#
# Instructions:  Define variables in the section marked ## !!
#
#
# COMMENT:   Script checks for AppV related errors on Chosen Citrix XenApp Full Desktops
#                       Query parameters are defined in AppVCustomFunctions.psm1
#
#                       A report is generated and saved to $OutputPath
#                       An Email is sent that includes the path to the exported report
#
#
# =============================================================

# Set Preference Variables

$VerbosePreference = 'Continue'

## !! Assign Variables for environment
$ScriptName = "App-V XenApp Publishing Error Report"
$timestamp = Get-Date -Format yyyyMMddHHmmss
$XenAppController = 'Insert XenApp Controller Name'
$ModuleLoc = "insert location of PSM1 modules (included below)"
$OutputPath = "insert UNC path to where the .xls files will be Output to"
$SMTPServer = 'insert SMTP server name'
$ReportEmailRecipients = 'email1@address.com','email2@address.com'
$FromEmail = 'DoNotReply@email.com'

# Add the Modules, SnapIns and Functions  ==================================
Write-Host "Importing Modules, SnapIns and Functions"
Add-PSSnapin -Name Citrix.XenApp.Commands
Set-XADefaultComputerName -computername $XenAppController
Import-module "$ModuleLoc\AppVCustomFunctions.psm1"
Import-module "$ModuleLoc\ConvertCVStoExcel.psm1"

# Request BrowserName  ==============================================
Write-Host "Querying Citrix for Enabled Published Desktops"
$name = Get-XAApplication | Where-Object {$_.ApplicationType -eq 'ServerDesktop' -and $_.Enabled -eq $True} | sort | select -exp BrowserName | Out-GridView -PassThru
 
# Retrieve Citrix Servers ===============================================
$servers = Get-XAServer -BrowserName $name | select -exp servername | sort
 
# Run the Report ===================================================
Write-Host "Gathering Data from Citrix Servers"
$report = Get-AppVEvent -computername $servers
 
# Export the Report =================================================
$ReportFile = "$env:temp\name.$timestamp"
$report | Select-Object -Property Message, Id, MachineName, TimeCreated, TaskDisplayName, UserAccount | export-csv -path "$ReportFile.csv"

ConvertCSVToExcel "$ReportFile.csv" "$OutputPath\name.$timestamp.xls"

# Send Email Alert ==================================================
$Subject = "The $ScriptName for $Name is completed"
Write-Host "Sending E-Mail Alert"
 Send-MailMessage `
  -SmtpServer $SMTPServer `
  -From $FromEmail `
  -To $ReportEmailRecipients `
  -Subject $Subject `
  -Body "$Subject. The report can be found at $OutputPath" `
  -BodyAsHtml

# End of Script ====================================================

Module 1

# =============================================================

# NAME: ConvertCVStoExcel.psm1

Function ConvertCSVToExcel {
param([string]$inputfile,[string]$outputfile=$($inputfile.replace(".csv",".xls")))

 Write-Host "Converting CSV to Excel: $inputfile"

 $xl = New-Object -comobject Excel.Application
 $xl.DisplayAlerts = $False
 $Workbook = $xl.workbooks.open($inputfile)
 $WorkSheet = $Workbook.worksheets.Item(1)
 $Resize = $WorkSheet.UsedRange
 [void]$Resize.EntireColumn.AutoFit()
 $Workbook.SaveAs($outputfile,1)
 $xl.Quit()
 [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
 Remove-Variable xl
 
 $outputfile

}
# End of Script ====================================================

Module 2

# =============================================================

# NAME: AppVCustomFunctions.psm1

function sid2user ($sid) {
    $Obj = New-Object System.Security.Principal.SecurityIdentifier($sid)
    $obj.Translate([System.Security.Principal.NTAccount]).value
}

 

function Get-AppVEvent {
   
    [cmdletbinding()]
    param(
        [string[]]$Computername
    )

    $ht = @{
        Starttime = (Get-Date).AddDays(-1)
        LogName = "Microsoft-Appv-Client/Admin"
        ID = 19104
    }

    $report = @()
                               
    foreach ($computer in $Computername)
    {
        $obj = Get-WinEvent -ComputerName $computer -FilterHashtable $ht
       
        if ($obj)
        {
            $obj = $obj | select *, @{l="UserAccount";e={sid2user $_.UserID}}
        }
        else
        {
            continue
        }

        $report += $obj
    }

    return $report

}
# End of Script ===============================================