Datastore report generating using script.

[skasai_1]

Hi Team,

I found one of the scripts from the Lucd discussion for generating the report for datastore usages via email 

I would like to implement the script for multiple vcenter at once, Can someone help me with this?

Attached the script and error statement....

#### Script starting#######

Get-Module -Name VMware* -ListAvailable | Import-Module

Connect-VIServer -Server '10.20.10.8'

$head = @"

<style>

TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}

TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;;background-color:thistle}

TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}

</style>

"@

$body = Get-Datastore |

    Select @{N='Datastore';E={$_.Name}},

         @{N='CapacityGB';E={[math]::Round($_.CapacityGB,1)}},

         @{N='FreeSpaceGB';E={[math]::Round($_.FreeSpaceGB,1)}},

         @{N='UsedPercent';E={[math]::Round(($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB*100,1)}},

         @{N='FreePercent';E={

             $p = [math]::Round((1 - ($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)*100,1)

             if($p -lt 10){"#br#$($p)"}

             elseif($p -lt 30){"#by#$($p)"}

             else{"#bg#$($p)"}

         }} |

     Sort-Object -Property UsedPercent -Descending |

     ConvertTo-Html -Head $head

$body = $body.Replace('>#br#',' bgcolor="red">').Replace('>#by#',' bgcolor="yellow">').Replace('>#bg#',' bgcolor="green">')

$sMail = @{

     To = 'me@domain.com'

     From = 'me@domain.com'

     Subject = 'Datastore Report'

     BodyAsHtml = $true

     Body = $body | Out-String   

     SmtpServer = 'owa.me.domain.com'

}

Send-MailMessage @sMail

Disconnect-VIServer -Server '10.20.10.8' -Confirm:$false

###### Error Getting like below #######

Name                           Port  User

----                           ----  ----

10.20.10.8                     443   MSSI\xxxx

Send-MailMessage : The operation has timed out.

At C:\Scripts1\New Datastore report.ps1:35 char:1

+ Send-MailMessage @sMail

+ ~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (System.Net.Mail.SmtpClient:Sm

   tpClient) [Send-MailMessage], SmtpException

    + FullyQualifiedErrorId : SmtpException,Microsoft.PowerShell.Commands.Send

   MailMessage

[LucD]

Looks like you have the wrong SMTP server, or that you don't have access to that server.

Check with your mail admins.

[skasai_1]

Let me check the same...

1) Is possible to get the report for multiple vcenter at the same time for this script.
2) Also, let me know where i have to insert the command to export the report in CSV and how.

[LucD]

1) Do you mean 1 report with multiple vCenters, or 1 report per vCenter?

2) A CSV as attachment to the email(s), or a CSV locally stored where you run the script?

[skasai_1]

1) 1 report with multiple vcenter

2) both CSV need to attach in email and CSV need to save in locally on the perticlur path.

[LucD · Accepted solution]

Try something like this

$head = @"

<style>

TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}

TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;;background-color:thistle}

TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}

</style>

"@

$reportNames = @()

$reportHtml = @()

foreach($VC IN $global:DefaultVIServers){

    $report = Get-Datastore -Server $VC |

        Select @{N='vCenter';E={$vc.Name}},

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

            @{N='CapacityGB';E={[math]::Round($_.CapacityGB,1)}},

            @{N='FreeSpaceGB';E={[math]::Round($_.FreeSpaceGB,1)}},

            @{N='UsedPercent';E={[math]::Round(($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB*100,1)}},

            @{N='FreePercent';E={[math]::Round((1 - ($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)*100,1)}} |

        Sort-Object -Property UsedPercent -Descending

    $reportName = ".\$($vc.Name)-report.csv"

    $report | Export-Csv -Path $reportName -NoTypeInformation

    $reportNames += $reportName

    $body = $report | select vCenter,Datastore,CapacityGB,FreeSpaceGB,UsedPercent,@{N='Freeprecent';E={

                if($_.FreePercent -lt 10){"#br#$($_.FreePercent)"}

                elseif($_.FreePercent -lt 30){"#by#$($_.FreePercent)"}

                else{"#bg#$($_.FreePercent)"}}} | ConvertTo-Html -Head $head

    $reportHtml += $body.Replace('>#br#',' bgcolor="red">').Replace('>#by#',' bgcolor="yellow">').Replace('>#bg#',' bgcolor="green">')

}

$sMail = @{

     To = 'me@domain.com'

     From = 'me@domain.com'

     Subject = 'Datastore Report'

     BodyAsHtml = $true

     Body = $reportHtml | Out-String 

     SmtpServer = 'owa.domain.com'

     Attachments = $reportNames

}

Send-MailMessage @sMaiL

[skasai_1]

Lucd,
Thanks a lot,

1.Where I have to mention the Vcenter names on the script Ex: (vcenter1, vcenter2, vcenter3)

2. How should I mention the default location to save the CSV file locally?

[LucD]

1. Just connect to all the vCenters you want to include in the report, before starting the script
2. Update the path in the variable $reportName

[skasai_1]

Its working but i need make some corrections on it.
1. The numeric value comes with. instead of ,

2. The free percent column having some unknown variables

3. On the vcenter column that particular vcenter name is not present.

if i export the file into HTML. it does not come with proper format alignment\colors are missing

[LucD]

I updated the script above, the typeinformation in the CSV is gone.

For me the vCenter name displays correctly, not sure what is happening when you run the script.

The dot vs comma is most probably a result of regional setting.

Try adding the -UseCulture switch on the Export-Csv cmdlet.

Not sure which file you are exporting to HTML.

Is that the attached CSV file?

The HTML report is in the body of the email.

[skasai_1]

Yes, it looks good.

Am trying to pull the report for 3 vcenters which am having also before executing the script I logged in the vcenter on powerCLI but it pulls the report only one vcenter.

[LucD]

What is in $global:defaultviservers?

You might want to check the setting for DefaultVIServerMode with Get-PowerCLIConfiguration

It should say multiple, if not, change it with Set-PowerCLIConfiguration

[skasai_1]

Thx Lucd,

Its working good....Thx again

[vmk2014]

LucD,

I tried to execute the script and getting the output over mail in csv and html, but just want to know why it triggers an error Head:The term"head' is not recognized. Can it be fixed ?

is it possible to save the csv report locally on the drive ? I still need the report attachment in csv format to be sent over mail.

regards.

V

[LucD]

Looks like you forgot to copy the '$' before head