Automation

 View Only
  • 1.  ETL process to update vm details

    Posted Jul 24, 2015 12:41 PM

    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



  • 2.  RE: ETL process to update vm details
    Best Answer

    Posted Jul 24, 2015 01:14 PM

    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.



  • 3.  RE: ETL process to update vm details

    Posted Jul 28, 2015 07:21 PM

    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.



  • 4.  RE: ETL process to update vm details

    Posted Aug 03, 2015 11:48 AM

    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



  • 5.  RE: ETL process to update vm details

    Posted Aug 03, 2015 12:25 PM

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



  • 6.  RE: ETL process to update vm details

    Posted Aug 03, 2015 01:12 PM

    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



  • 7.  RE: ETL process to update vm details

    Posted Aug 03, 2015 01:39 PM

    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