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

mo618Asked:
Who is Participating?
 
Bryan ButlerConnect With a Mentor Commented:
A CSV file would be:

$Report | Export-Csv c:\report.csv


An XLS file with formatting and all would be harder.  Here's an article and let us know if you need help this way.

http://social.technet.microsoft.com/Forums/en/exchangesvradmin/thread/f12ad5d2-41ce-4aee-9940-214f11bd0441
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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

 Reporting to CSV using VMware Community Pack
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.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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/
0
Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

 
mo618Author Commented:
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.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
To schedule the script you would need to run as a CRON job or Task Schedule on a Windows PC.
0
 
Bryan ButlerCommented:
$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>

0
 
Bryan ButlerCommented:
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()
0
 
mo618Author Commented:
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?
0
 
mo618Author Commented:
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
0
 
Bryan ButlerCommented:
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.  
0
 
mo618Author Commented:
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.
0
 
Bryan ButlerCommented:
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

0
 
Bryan ButlerCommented:
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

0
 
mo618Author Commented:
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..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.