ETL process to update vm details

[Pinball]

Hi all

I need some insight into the following. I have an existing script that evolved over time (original source WoodITWork.com - It's about time I let the world know what I was thinking...).

The intention is to connect to 10 virtual centers, export a list of vm properties from each vc to 10 different csv files.

What i need to add is the vm UUID and DNS name and then assistance with how to name each csv per the virtual center name if possible.

$VC = Connect-VIServer $VCServerName

#$VMFolder = "Workstations"

$ExportFilePath = "C:\PS\Export-VMInfo.csv"

$Report = @()

#$VMs = Get-Folder $VMFolder | Get-VM

$VMs = Get-VM

$Datastores = Get-Datastore | select Name, Id

$VMHosts = Get-VMHost | select Name, Parent

ForEach ($VM in $VMs) {

      $VMView = $VM | Get-View

      $VMInfo = {} | Select VMName,Powerstate,OS,IPAddress,Cluster,Datastore,NumCPU,MemMb,DiskGb, DiskFree, DiskUsed

      $VMInfo.VMName = $vm.name

      $VMInfo.Powerstate = $vm.Powerstate

      $VMInfo.OS = $vm.Guest.OSFullName

      $VMInfo.IPAddress = $vm.Guest.IPAddress[0]

      $VMInfo.Cluster = $vm.host.Parent.Name

      $VMInfo.Datastore = ($Datastores | where {$_.ID -match (($vmview.Datastore | Select -First 1) | Select Value).Value} | Select Name).Name

      $VMInfo.NumCPU = $vm.NumCPU

      $VMInfo.MemMb = [Math]::Round(($vm.MemoryMB),2)

      $VMInfo.DiskGb = [Math]::Round((($vm.HardDisks | Measure-Object -Property CapacityKB -Sum).Sum * 1KB / 1GB),2)

      $VMInfo.DiskFree = [Math]::Round((($vm.Guest.Disks | Measure-Object -Property FreeSpace -Sum).Sum / 1GB),2)

      $VMInfo.DiskUsed = $VMInfo.DiskGb - $VMInfo.DiskFree

      $Report += $VMInfo

}

$Report = $Report | Sort-Object VMName

IF ($Report -ne "") {

$report | Export-Csv $ExportFilePath -NoTypeInformation

}

The other question is if it's still the most efficient way of useing the syntax getting the data as 4 of the vc's has got 4000+ vm's

[LucD · Accepted solution]

The following should include these new properties you requested.

$VC = Connect-VIServer $VCServerName

$ExportFilePath = "C:\PS\Export-VMInfo.csv"

$Report = @()

$VMs = Get-VM

$Datastores = Get-Datastore | select Name, Id

$VMHosts = Get-VMHost | select Name, Parent

ForEach ($VM in $VMs) {

       $VMView = $VM | Get-View

       $VMInfo = {} | Select VMName,vCenter,Uuid,DNS,Powerstate,OS,IPAddress,Cluster,Datastore,NumCPU,MemMb,DiskGb, DiskFree, DiskUsed

       $VMInfo.VMName = $vm.name

       $VMInfo.vCenter = $vm.client.ServerUri.Split('@')[1]

       $VMInfo.Uuid = $VMView.Config.Uuid

       $VMInfo.DNS = $vmView.Guest.HostName

       $VMInfo.Powerstate = $vm.Powerstate

       $VMInfo.OS = $vm.Guest.OSFullName

       $VMInfo.IPAddress = $vm.Guest.IPAddress[0]

       $VMInfo.Cluster = $vm.host.Parent.Name

       $VMInfo.Datastore = ($Datastores | where {$_.ID -match (($vmview.Datastore | Select -First 1) | Select Value).Value} | Select Name).Name

       $VMInfo.NumCPU = $vm.NumCPU

       $VMInfo.MemMb = [Math]::Round(($vm.MemoryMB),2)

       $VMInfo.DiskGb = [Math]::Round((($vm.HardDisks | Measure-Object -Property CapacityKB -Sum).Sum * 1KB / 1GB),2)

       $VMInfo.DiskFree = [Math]::Round((($vm.Guest.Disks | Measure-Object -Property FreeSpace -Sum).Sum / 1GB),2)

       $VMInfo.DiskUsed = $VMInfo.DiskGb - $VMInfo.DiskFree

       $Report += $VMInfo

}

$Report = $Report | Sort-Object VMName

if ($Report -ne "") {

    $report | Export-Csv $ExportFilePath -NoTypeInformation

}

For a bigger environment, it would be in any case faster to go for the Get-View solution.

[Pinball]

Luc

Thank you very much, this worked perfectly. I'll give the "Get-View" a go, seen the latest list of article seems very impressive and just what i'm looking for.

[Pinball]

Hi Luc

Based on the below script, if i wish to execute it for 10 different virtual centers and change the export file name based on the current connected virtual center, is there a variable that i can use to define the export-csv file name?

Thanks

Johan

[LucD]

Let me see if I got the question right, you want separate CSV files for each vCenter (of the 10 you have connected) ?

[Pinball]

Hi Luc

Yes, use a source script that list all connections, this in turn calls the script in this thread and create one export per vc and name the file uniquely as per the vc's name.

Johan

[LucD]

Ok, I think I see where you want to go.

For the filename of the CSV, you could just update the line where you compose the name of the file.

Something like this

$VC = Connect-VIServer $VCServerName

$ExportFilePath = "C:\PS\Export-$($VCServerName)-VMInfo.csv"

If you have multiple vSphere server connections open when you call this script, you could add the Server parameter on the PowerCLI cmdlets.

That way the cmdlet will only return results for that specific vSphere server.

Something like this

Get-VM -Server $VCServerName