asked on

powercli exporting inventory script to excel

I found this script online and i need assistance in exporting the results to an excel spreadsheet and email the results using SMTP as an attachment. Any advice is appreciated.

Get-VM | `
  ForEach-Object {
    $VM = $_
    $VMview = $VM | Get-View
    $VMResourceConfiguration = $VM | Get-VMResourceConfiguration
    $VMHardDisks = $VM | Get-HardDisk
    $HardDisksSizesGB = @()
    $Temp = $VMHardDisks | ForEach-Object { $HardDisksSizesGB += [Math]::Round($_.CapacityKB/1MB) }
    $VmdkSizeGB = ""
    $Temp = $HardDisksSizesGB | ForEach-Object { $VmdkSizeGB += "$_+" }
    $VmdkSizeGB = $VmdkSizeGB.TrimEnd("+")
    $TotalHardDisksSizeGB = 0
    $Temp = $HardDisksSizesGB | ForEach-Object { $TotalHardDisksSizeGB += $_ }
    $Snapshots = $VM | Get-Snapshot
    $Report = "" | Select-Object VMname,ESXname,MemoryGB,vCPUcount,vNICcount,IPaddresses,VmdkSizeGB,TotalVmdkSizeGB,DatastoreName,ToolsVersion,ToolsUpdate,NumCpuShares,NumMemShares,ReservationsMB,LimitMB,SnapshotCount,GuestOS
    $Report.VMName = $VM.name
    $Report.ESXname = $VM.Host
    $Report.MemoryGB = $VM.MemoryMB/1024
    $Report.vCPUcount = $VM.NumCpu
    $Report.vNICcount = $VM.Guest.Nics.Count
    $Report.IPaddresses = [String]$vm.Guest.IPAddress
    $Report.VmdkSizeGB = $VmdkSizeGB
    $Report.TotalVmdkSizeGB = $TotalHardDisksSizeGB
    $Report.DatastoreName = $VMview.Config.DatastoreUrl
    $Report.ToolsVersion = $VMview.Config.Tools.ToolsVersion
    $Report.ToolsUpdate = $VMview.Guest.ToolsStatus
    $Report.NumCpuShares = $VMResourceConfiguration.NumCPUShares
    $Report.NumMemShares = $VMResourceConfiguration.NumMemShares
    $Report.ReservationsMB = $VMResourceConfiguration.MemReservationMB
    $Report.LimitMB = $VMResourceConfiguration.MemLimitMB
    $Report.SnapshotCount = (@($VM | Get-Snapshot)).Count
    $Report.GuestOS = $VM.Guest.OSFullName
    Write-Output $Report
  }

Open in new window

Andrew Hancock (VMware vExpert PRO / EE Fellow/British Beekeeper)

I think you'll get a much better GUI interface and Inventory Reporting using the VMware Community Pack which includes exporting to CSV, XML and creating Reports

I would recommend using the following, also VMware recommends the use of PowerCLI, which is PowerShell interface with vSphere-specific additions.

http://communities.vmware.com/community/vmtn/server/vsphere/automationtools/powercli

One of the simplest PowerCLI examples, and something which is actually extremely useful on its own, is the Get-VM cmdlet. Which lists VMs.

PowerCLI can be a bit overwhelming to use, But it can be enhanced with a nice toolset and a library of preconfigured scripts to jump-start your mass changes, which is possible with PowerGUI

http://powergui.org/index.jspa

and

VMware Community Pack

http://www.virtu-al.net/featured-scripts/vmware-powerpack/

It's also worth looking at The free VMware Guest Console

http://labs.vmware.com/flings/vgc

The free VMware Guest Console, an experimental application created by VMware Labs, is a great tool for managing vSphere VM processes and files. You can view, sort and kill processes across all VMs, and you can also run a script on all Windows or Linux VMs.

Andrew Hancock (VMware vExpert PRO / EE Fellow/British Beekeeper)

You are already using PowerCLI, so you just need to download PowerGUI and the VMware Community Pack, and you'll be producing Excel CSV Reports in no time at all, rather than trying to modiy someone eleses script.

PowerGUI

http://powergui.org/index.jspa

VMware Community Pack

http://www.virtu-al.net/featured-scripts/vmware-powerpack/

mo618

ASKER

The problem I have is that we want this scheduled on a monthly basis and the GUI may not allow us to do that. the Get-VM cmdlet doesn't provide the detailed information that we need. The script I found does export the results to an CSV formated to our exact needs by running the script with the command export-csv, but i wanted to know if there are any commands that i can place in the script and just schedule this particular script itself.

Andrew Hancock (VMware vExpert PRO / EE Fellow/British Beekeeper)

To schedule the script you would need to run as a CRON job or Task Schedule on a Windows PC.

Bryan Butler

$outlookobj = New-Object -comObject Outlook.Application
$message = $outlookobj.CreateItem(0)
$message.Recipients.Add(my@email.com)
$message.Subject = "subject"
$message.Body = "See attachment`r`n`Thanks, yourname"

$file = "C:\yourfile.csv"
$message.Attachments.Add($file)

or if you have 2.0:

Send-MailMessage -from "me <me@test.com>" -to "you <you@test.com>", "her <her@test.com>" `
-subject "The Attachment" -body "Here is the attachment." `
-attachment "yourdata.csv" -smtpServer <smtp.youremailserver.com>

Bryan Butler

Forgot the $message.send() on that first one. So it would be:

$outlookobj = New-Object -comObject Outlook.Application
$message = $outlookobj.CreateItem(0)
$message.Recipients.Add(my@email.com)
$message.Subject = "subject"
$message.Body = "See attachment`r`n`Thanks, yourname"

$file = "C:\yourfile.csv"
$message.Attachments.Add($file)
$message.send()

mo618

ASKER

developedtester - thanks, I'll test the email function. would you know how to set it up so the original script exports the results in excel format from within the script?

ASKER CERTIFIED SOLUTION

Bryan Butler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mo618

ASKER

developedtester - Thanks for the info. I tweaked it a bit more and added the following lines to the original script.

$ExportFilePath = "C:\test.csv" #added line
$allLines = @() #added line
Get-VM | `
ForEach-Object {
$VM = $_

$Report.GuestOS = $VM.Guest.OSFullName
      $allLines += $Report #added line
      #$alllines = $alllines | Sort-Object VMName #added line
      $alllines | Export-Csv $ExportFilePath -NoTypeInformation #added line
}

I still haven't had the time to test the email portion of the script. I am assuming i would need to create a powershell script to call this script and then enter the code you provided to send the email. From what I understand, you can't send emails from a powercli script..correct? Sorry, I am really new to powershell and powercli. All help is appreciated it.

Thanks

Bryan Butler

Glad to help out. Good catch on the "notypeinfomation" . You can send an email from the CLI. At least I could. Outlook did have a pop-up where I had to click "OK" as I guess it monitors any other programs using email on my box. Hopefully you don't have outlook installed on the box.

mo618

ASKER

I don't think we will be installing Outlook on the box. However, I am assuming that we need SMTP installed on the box to send an email out thru exchange. Thanks for all your help.

Bryan Butler

Ooops, the SMTP commands above use outlook as the SMTP client. That's why my outlook was butting in. Here's the commands to use the CLR:

$message = New-Object System.Net.Mail.MailMessage –ArgumentList email1@email.com, email2@email.com, 'Test subject', "body of email"
attachment = New-Object System.Net.Mail.Attachment –ArgumentList 'c:\test.txt’, ‘Application/Octet’
$message.Attachments.Add($attachment)
$smtp = New-Object System.Net.Mail.SMTPClient –ArgumentList 10.10.10.10
$smtp.Send($message)

Open in new window

Bryan Butler

Missed the $ sign there. Also, this is more clear:

$SmtpClient = new-object system.net.mail.smtpClient 
$MailMessage = New-Object system.net.mail.mailmessage 
$attachment = New-Object System.Net.Mail.Attachment –ArgumentList 'C:\test.txt’, ‘Application/Octet’
$SmtpClient.Host = "<email server>" 
$mailmessage.from = ("your@email.com") 
$mailmessage.To.add("to@email.com") 
$mailmessage.Subject = “Message” 
$mailmessage.Body = “Body” 
$mailmessage.attachement.add($attachment)
$smtpclient.Send($mailmessage)

Open in new window

mo618

ASKER

That worked for me!! Thanks. Any tips on how i can secure the password on the script? Since I will be running this on a server as a scheduled task, i don't want to leave the password in clear text..

Bryan Butler

Does this work for you?
http://powershell.com/cs/blogs/tips/archive/2009/04/08/encrypting-powershell-scripts.aspx