vin01
Expert
Expert

Merge Multiple email outputs to singlemail

Jump to solution

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'

}}

Regards Vineeth.K
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership

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'

  


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
6 Replies
LucD
Leadership
Leadership

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'

}

  


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

vin01
Expert
Expert

Thanks GuruSmiley Happy 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.

Regards Vineeth.K
0 Kudos
LucD
Leadership
Leadership

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'

  


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
vin01
Expert
Expert

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.

Regards Vineeth.K
0 Kudos
LucD
Leadership
Leadership

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'

  


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
vin01
Expert
Expert

Thanks GuruSmiley Happy That worked and thanks for your advice.

Regards Vineeth.K
0 Kudos