?
Solved

powercli exporting inventory script to excel

Posted on 2011-05-06
15
Medium Priority
?
2,889 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:mo618
  • 7
  • 5
  • 3
15 Comments
 
LVL 124
ID: 35720435
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
 
LVL 124
ID: 35720455
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
 

Author Comment

by:mo618
ID: 35720530
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 124
ID: 35720562
To schedule the script you would need to run as a CRON job or Task Schedule on a Windows PC.
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35721075
$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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35721111
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
 

Author Comment

by:mo618
ID: 35724998
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
 
LVL 16

Accepted Solution

by:
Bryan Butler earned 2000 total points
ID: 35728898
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
 

Author Comment

by:mo618
ID: 35729800
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35729966
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
 

Author Comment

by:mo618
ID: 35730165
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35730784
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35731757
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
 

Author Comment

by:mo618
ID: 35733537
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35739704
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question