Merge Multiple email outputs to singlemail

[vin01]

Hi all,

Can someone help me to complete this script.

My question is with the below script I can send error report of each vcenter as a single mail but I need all the connected vcenter error report output in a singlemail.

$allvcenterservers = "vcenter1","vcenter2","vcenter3","vcenter4"
foreach($vcenterserver in $allvcenterservers){
$cred = Get-VICredentialStoreItem -User domain\user -Host $vcenterserver  -File C:\cred.xml
Connect-VIServer -Server $cred.Host -User $cred.User -Password $cred.Password | Out-Null
$daysback = 1
$report = @()
$report+= Get-VIEvent -Types Error -maxsamples ([int]::MaxValue) -Start (Get-Date).AddDays(-$daysback) |
Select @{N="Vcenterserver";E={$vcenterserver}},
@{N="Type";E={$_.GetType().Name}},
@{N='CreatedTime';E={$_.CreatedTime}},
@{N='User';E={$_.UserName}},
@{N="Message";E={$_.FullFormattedMessage}}
$CurrentDate = Get-Date -Format 'MM-dd-yyyy_hh-mm-ss'
$filename = "C:\eventreport on $vcenterserver -$($CurrentDate).csv"
if($report.Count -eq 0){
}
else{
$report |Export-Csv -Path $filename -NoTypeInformation -NoClobber -UseCulture
Send-MailMessage -From "vineeth@mail.com" -To "vineeth@mail.com" -Subject "Error Events on $vcenterserver" -Body "The attachment contains all error events which are triggered from past 24hrs on $vcenterserver" -Attachments $filename -SmtpServ.com'
}}

[LucD]

Move the sending of the report out of the foreach loop.

Something like this

$allvcenterservers = "vcenter1","vcenter2","vcenter3","vcenter4" 

$report = @() 

foreach($vcenterserver in $allvcenterservers){ 

    $cred = Get-VICredentialStoreItem -User domain\user -Host $vcenterserver  -File C:\cred.xml 

    Connect-VIServer -Server $cred.Host -User $cred.User -Password $cred.Password | Out-Null 

    $daysback = 1 

    $report+= Get-VIEvent -Types Error -maxsamples ([int]::MaxValue) -Start (Get-Date).AddDays(-$daysback) | 

        Select @{N="Vcenterserver";E={$vcenterserver}}, 

            @{N="Type";E={$_.GetType().Name}},  

            @{N='CreatedTime';E={$_.CreatedTime}}, 

            @{N='User';E={$_.UserName}}, 

            @{N="Message";E={$_.FullFormattedMessage}} 

}

$CurrentDate = Get-Date -Format 'MM-dd-yyyy_hh-mm-ss' 

$filename = "C:\eventreport on $vcenterserver -$($CurrentDate).csv" 

if($report.Count -ne 0){ 

    $report |Export-Csv -Path $filename -NoTypeInformation -NoClobber -UseCulture 

    Send-MailMessage -From "vineeth@mail.com" -To "vineeth@mail.com" -Subject "Error Events on $vcenterserver" -Body "The attachment contains all error events which are triggered from past 24hrs on $vcenterserver" -Attachments $filename -SmtpServer 'smtpserver.com'

}

[vin01]

Thanks Guru That works perfectly.

Is it possible to get as this variation.

Example : vcenter1 is sheet 1 and vcenter 2 - sheet2 vcenter3 - sheet3.in single .csv file

or

generate each vcenter in one .csv and attach in single mail.

Please remove the smtp server in the below post. I posted accidentally my corp server.

[LucD]

Try something like this

$allvcenterservers = "vcenter1","vcenter2","vcenter3","vcenter4" 

$CurrentDate = Get-Date -Format 'MM-dd-yyyy_hh-mm-ss'

$csvFiles = @()

foreach($vcenterserver in $allvcenterservers){ 

    $report = @() 

    $cred = Get-VICredentialStoreItem -User domain\user -Host $vcenterserver  -File C:\cred.xml 

    Connect-VIServer -Server $cred.Host -User $cred.User -Password $cred.Password | Out-Null 

    $daysback = 1 

    $report+= Get-VIEvent -Types Error -maxsamples ([int]::MaxValue) -Start (Get-Date).AddDays(-$daysback) | 

        Select @{N="Vcenterserver";E={$vcenterserver}}, 

            @{N="Type";E={$_.GetType().Name}},  

            @{N='CreatedTime';E={$_.CreatedTime}}, 

            @{N='User';E={$_.UserName}}, 

            @{N="Message";E={$_.FullFormattedMessage}} 

    if($report.Count -ne 0){ 

        $filename = "C:\eventreport-on-$($vcenterserver)-$($CurrentDate).csv" 

        $csvFiles += $filename

        $report |Export-Csv -Path $filename -NoTypeInformation -NoClobber -UseCulture

    }

}

Send-MailMessage -From "vineeth@mail.com" -To "vineeth@mail.com" -Subject "Error Events on $($vcenterserver)" `

    -Body "The attachment contains all error events which are triggered from past 24hrs on $($vcenterserver)" `

    -Attachments $csvFiles -SmtpServer 'smtpserver.com'

[vin01]

Thanks Luc. This worked as per my requirement.

Is it possible to append all the vcenter information in a single .csv like..

Sheet 1 --vcenter 1

sheet 2 --vcenter 2

sheet3 -- vcenter 3

b/c I need to fetch from 10 vcenters.

[LucD · Accepted solution]

I don't think CSV supports worksheets, it will have to be a XLSX file in that case.

I used the ImportExcel module from Doug to make it easier. I highly advise to use that module, it doesn't require the installation of Excel (ideal for scripts running on servers).

$allvcenterservers = "vcenter1","vcenter2","vcenter3","vcenter4" 

$CurrentDate = Get-Date -Format 'MM-dd-yyyy_hh-mm-ss'

$filename = "C:\eventreport-on-$($CurrentDate).xlsx" 

foreach($vcenterserver in $allvcenterservers){ 

    $report = @() 

    $cred = Get-VICredentialStoreItem -User domain\user -Host $vcenterserver  -File C:\cred.xml 

    Connect-VIServer -Server $cred.Host -User $cred.User -Password $cred.Password | Out-Null 

    $daysback = 1 

    $report+= Get-VIEvent -Types Error -maxsamples ([int]::MaxValue) -Start (Get-Date).AddDays(-$daysback) | 

        Select @{N="Vcenterserver";E={$vcenterserver}}, 

            @{N="Type";E={$_.GetType().Name}},  

            @{N='CreatedTime';E={$_.CreatedTime}}, 

            @{N='User';E={$_.UserName}}, 

            @{N="Message";E={$_.FullFormattedMessage}} 

    if($report.Count -ne 0){ 

        $report |Export-Excel -Path $filename -WorksheetName $vcenterserver -AutoSize -FreezeTopRow

    }

}

Send-MailMessage -From "vineeth@mail.com" -To "vineeth@mail.com" -Subject "Error Events on $($vcenterserver)" `

    -Body "The attachment contains all error events which are triggered from past 24hrs on $($vcenterserver)" `

    -Attachments $filename -SmtpServer 'smtpserver.com'

[vin01]

Thanks Guru That worked and thanks for your advice.