Inserting Date

[wamatha]

I have a scheduled PowerCLI script that runs daily, I would like help so that the report has inserts a date stamp in this format "yyyy-mm-dd"

[RvdNieuwendijk]

The following PowerShell code will return the current date as a string in the "yyyy-mm-dd" format:

"{0:yyyy-MM-dd}" -f (Get-Date)

Regards, Robert

[wamatha]

I added that to the script but still no date on the report

[RvdNieuwendijk]

Can you share your updated script?  Then I can help you to fix this problem.

[wamatha]

Here you go

# Monthly VM Consumption Report

Add-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue

Connect-VIServer

Get-VM | `

  Select-Object Name,@{N="UsedSpaceMB";E={[math]::Round($_.UsedSpaceGB*1KB,0)}},NumCpu,MemoryMB,

    @{N="SnapshotSizeMB";E={[math]::Round(($_ | Get-Snapshot | Measure-Object -Property SizeMB -Sum).Sum,0)}} |

  Format-Table -AutoSize | Out-File D:\powershell\VMsReport.txt

# Send Email

$fileVM = “D:\Powershell\VMsreport.txt”

#Set Date format

"{0:yyyy-MM-dd}" -f (Get-Date)

Disconnect-VIServer * -Confirm:$false

[RvdNieuwendijk · Accepted solution]

I modified the script so that it shows the current date at the top of the output file. I also added the sending of the output file via e-mail part. And I made some other modifcations. I hope you like it.

# Monthly VM Consumption Report

# Variable definitions
$OutputFile = "D:\powershell\VMsReport.txt"
$vCenterServer = "MyvCenterServer"
$MailFrom = "User01 <user01@example.com>"
$MailTo = "User02 <user02@example.com>"
$MailSubject = "Monthly VM Consumption Report"
$MailBody = "Attached you will find the Monthly VM Consumption Report."
$SmtpServer = "smtp.fabrikam.com"

# Output the current date to the output file
"{0:yyyy-MM-dd}" -f (Get-Date) | Out-File -FilePath $OutPutFile

# Add an empty line to the output file
"" | Out-File -FilePath $OutPutFile -Append

# Add the PowerCLI snapin
Add-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue

# Connect to the vCenter server
Connect-VIServer -Server $vCenterServer

# Get all the VM information and append this to the output file
Get-VM | `
  Select-Object Name,@{N="UsedSpaceMB";E={[math]::Round($_.UsedSpaceGB*1KB,0)}},NumCpu,MemoryMB,
    @{N="SnapshotSizeMB";E={[math]::Round(($_ | Get-Snapshot | Measure-Object -Property SizeMB -Sum).Sum,0)}} | 
  Format-Table -AutoSize | Out-File -FilePath $OutPutFile -Append
  
# Send the output file via e-mail
Send-MailMessage -from $MailFrom -to $MailTo -subject $MailSubject `
  -body $MailBody -Attachment $OutputFile -smtpServer $SmtpServer

Disconnect-VIServer $vCenterServer -Confirm:$false

[wamatha]

works like charm, thank you very much!

Is it possible to append the date at the bottom of the report?

[RvdNieuwendijk]

This modified version of the script appends the date at the bottom of the report:

# Monthly VM Consumption Report

# Variable definitions
$OutputFile = "D:\powershell\VMsReport.txt"
$vCenterServer = "MyvCenterServer"
$MailFrom = "User01 <user01@example.com>"
$MailTo = "User02 <user02@example.com>"
$MailSubject = "Monthly VM Consumption Report"
$MailBody = "Attached you will find the Monthly VM Consumption Report."
$SmtpServer = "smtp.fabrikam.com"

# Add the PowerCLI snapin
Add-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue

# Connect to the vCenter server
Connect-VIServer -Server $vCenterServer

# Get all the VM information and append this to the output file
Get-VM | `
  Select-Object Name,@{N="UsedSpaceMB";E={[math]::Round($_.UsedSpaceGB*1KB,0)}},NumCpu,MemoryMB,
    @{N="SnapshotSizeMB";E={[math]::Round(($_ | Get-Snapshot | Measure-Object -Property SizeMB -Sum).Sum,0)}} | 
  Format-Table -AutoSize | Out-File -FilePath $OutPutFile
  
# Add an empty line to the output file
"" | Out-File -FilePath $OutPutFile -Append

# Output the current date to the output file
"{0:yyyy-MM-dd}" -f (Get-Date) | Out-File -FilePath $OutPutFile -Append
  
# Send the output file via e-mail
Send-MailMessage -from $MailFrom -to $MailTo -subject $MailSubject `
  -body $MailBody -Attachment $OutputFile -smtpServer $SmtpServer

# Disconnect the vCenter server
Disconnect-VIServer $vCenterServer -Confirm:$false

[wamatha]

This works like charm and thanks alot. Last request, how can I sort by name then export the report to csv ?

Sorry for being a pain.

[RvdNieuwendijk]

To sort the virtual machines by name and export the report to a .csv file, I modified the script into:

# Variable definitions
$OutputFile = "D:\powershell\VMsReport.csv"
$vCenterServer = "MyvCenterServer"
$MailFrom = "User01 <user01@example.com>"
$MailTo = "User02 <user02@example.com>"
$MailSubject = "Monthly VM Consumption Report"
$MailBody = "Attached you will find the Monthly VM Consumption Report."
$SmtpServer = "smtp.fabrikam.com"

# Add the PowerCLI snapin
Add-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue

# Connect to the vCenter server
Connect-VIServer -Server $vCenterServer

# The Get-VMInformation function gets all the VM information
function Get-VMInformation {
  process {
    Get-VM | Sort-Object -Property Name | `
      Select-Object Name,@{N="UsedSpaceMB";E={[math]::Round($_.UsedSpaceGB*1KB,0)}},NumCpu,MemoryMB,
        @{N="SnapshotSizeMB";E={[math]::Round(($_ | Get-Snapshot | Measure-Object -Property SizeMB -Sum).Sum,0)}}
  }
  end {
    "" | Select-Object @{N="Name";E={"{0:yyyy-MM-dd}" -f (Get-Date)}},UsedSpaceMB,NumCpu,MemoryMB,SnapshotSizeMB
  }
}

# Get all the VM information and export it to the output file
Get-VMInformation | 
  Export-Csv -Path $OutPutFile -NoTypeInformation -UseCulture 
  
# Send the output file via e-mail
Send-MailMessage -from $MailFrom -to $MailTo -subject $MailSubject `
  -body $MailBody -Attachment $OutputFile -smtpServer $SmtpServer

# Disconnect the vCenter server
Disconnect-VIServer $vCenterServer -Confirm:$false

Message was edited by: RvdNieuwendijk I modified the scripts comment.

[wamatha]

Thank you very much once again. the last question., how do I overwrite the output report file so that everytime the scritp runs,  as it's currently appending to the existing file

[RvdNieuwendijk]

The script actualy doesn't append, but overwrites the output file each time the script runs. While modifying the script, I forgot to change the comment . I will change the comment in my previous post to reflect the modifications.

Message was edited by: RvdNieuwendijk

[wamatha]

Working ver ver well

Great Thanks  :smileycool: