Need help scripting vSphere data, packaging and sending it to data warehouse

[rcodo]

Greetings PowerCLI Gurus.

Can anybody offer suggestions on a script that can query vSphere and pull the following fields on VM's:

Name

State

Status

Host

Provisioned Space

Used Space

Format into a CSV format file and email that file to an FTP server?

Much respect to all, thanks so much in advance.

[mattboren]

Hello, rcodo-

For gathering the VM info, you could use the standard Get-VM cmdlet (slower):

Get-VM | Select Name,
    PowerState,
    @{n="OverallStatus"; e={$_.ExtensionData.OverallStatus}},
    @{n="VMHost"; e={$_.VMHost.Name}},
    ProvisionedSpaceGB,
    UsedSpaceGB | Export-Csv -NoTypeInformation c:\temp\vmInfo.csv

or, using the Get-View cmdlet and specifying just the Properties needed, do:

Get-View -ViewType VirtualMachine -Property Name, Runtime.PowerState, OverallStatus, Runtime.Host, Summary.Storage | Select-Object Name,
    @{n="PowerState"; e={$_.Runtime.PowerState}},
    OverallStatus,
    @{n="VMHost"; e={(Get-View $_.Runtime.Host -Property Name).Name}},
    @{n="ProvisionedSpaceGB"; e={"{0:n2}" -f (($_.Summary.Storage.Committed + $_.Summary.Storage.Uncommitted) / 1gb)}},
    @{n="UsedSpaceGB"; e={"{0:n2}" -f ($_.Summary.Storage.Committed / 1gb)}} | Export-Csv -NoTypeInformation c:\temp\vmInfo.csv

The second method should be far quicker.

As for transferring the resultant CSV file, must it go to an FTP server?  If so, you could user the regular ftp.exe that comes with Windows.  You put the commands into a text file, and then call ftp.exe with the -s parameter (do "ftp.exe -?" for further info).  This route involves storing the credentials in clear text, and FTP itself transmits things (including credentials) in clear text.

So, transferring the CSV file by some other means would probably be preferrable, for security's sake.  What other options are available to you?

[rcodo]

Hi BigMatt,

Thx so much for the great guidance.

To answer your question on file transport

>> So, transferring the CSV file by some other means would probably be preferrable, for security's sake. What other options are available to you?

The objective is to export the CSV file to a central data warehouse repository. I welcome any suggestions on the best method for doing so safely. What would you suggest?

[mattboren · Accepted solution]

Hello-

Glad to help.

Ok, well, if that data repository is accessible via a UNC path, you could do a straight copy using Copy-Item.  If you have to use different credentials, you could encrypt them and store them in an XML file.  Hal Rottenberg wrote about doing so at http://halr9000.com/article/531.

Or, if this data repo is something that supports secure copy (scp) or secure FTP (SFTP), those would be good options.  Again, you could store alternate credentials in encrypted format in an XML file and use them as needed.

Granted, there is a balance to be struck between security and usability.  It might be such that the data being transmitted is not deemed sensitive at all, and that clear data transfers are acceptable.  Probably still a good idea to take measures to protect the credentials at least.

[rcodo]

Fantastic! Thx so much Big Matt!