Pinball
Enthusiast
Enthusiast

ETL process to update vm details

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
6 Replies
LucD
Leadership
Leadership

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
Pinball
Enthusiast
Enthusiast

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.

0 Kudos
Pinball
Enthusiast
Enthusiast

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

0 Kudos
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
Pinball
Enthusiast
Enthusiast

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

0 Kudos
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos