Very slow script - help optimizing it - getting last powered on/off and created date.

[GScully42]

This script works but is very slow, almost an hour per vm, what can I do to make it faster?

I'm grabbing vm's that are on the vCloud hence the name > 30

Any suggestions or help is appreciated.

Thanks

function Get-VMLastPoweredOnDate {

  param([Parameter(Mandatory=$true,ValueFromPipeline=$true)]

        [VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImpl] $vm)

  process {

    $Report = "" | Select-Object -Property Name,LastPoweredOnDate,UserName

  $Report.Name = $_.Name

  $Event = Get-VIEvent -Entity $vm | Sort-Object -Property CreatedTime -Descending |`

      Where-Object { $_.Gettype().Name -eq "VmPoweredOnEvent" } | `

   Select-Object -First 1

  $Report.LastPoweredOnDate = $Event.CreatedTime

  $Report.UserName = $Event.UserName

  $Report

  }

}

New-VIProperty -Name LastPoweredOffDate -ObjectType VirtualMachine -Value {(Get-VMLastPoweredOffDate -vm $Args[0]).LastPoweredOffDate} -Force

#get cloud vm's only

$vms = @()

$vms = get-vm | where {$_.name.Length -gt 30}

$time = get-date -Format MM/dd/yyyy

$shortdate = $time

foreach ($vm in $vms) {

  $name = $vm.name.Split('(')[0]

    $civm = Get-CIVM -name $name.Trim() -ErrorAction SilentlyContinue

    $owner = $civm.VApp.owner.name   

    if($owner -eq $null){

        $owner = "No Data"}

    $vmOrg = $civm.Org.Name

    if($vmOrg -eq $null){

        $VMOrg = "Catalog Item"}

  $poweredState = $vm.PowerState

    if($poweredState -eq "PoweredOff"){

        $poweredOn = 0 } else {$poweredOn = 1}

    $LastpowerOffDate = $vm.LastPoweredOffDate

    if($LastpowerOffDate -eq $Null){

        $LastpowerOffDate = "1/1/2013"}

  $vmCPU = $vm.NumCpu

    $vmMem = $vm.MemoryGB

    $vmHost = $vm.VMHost.Name

    $UsedSpaceGB = [Math]::Round($vm.UsedSpaceGB,2)

    $allocatedSpaceGB = [MATH]::Round($vm.ProvisionedSpaceGB,2)

    $vmds = Get-datastore -VM $vm | select Name

    if($vmds.name.Length -ge 150){$vmds.Name = $vmds.Name.substring(0,148)}

    #_ get create date

    $stats = Get-Stat -Entity $vms -start (get-date).AddDays(-1) -Finish (Get-Date)-MaxSamples 100 -stat "cpu.usage.average","mem.usage.average" ,"disk.maxTotalLatency.latest"  -ErrorAction SilentlyContinue

    $stats | Group-Object -Property Entity | %{

    $vmstat = "" | Select VmName, MemMax, MemAvg, MemMin, CPUMax, CPUAvg, CPUMin, DiskMax, DiskAvg, DiskMin

    $vmstat.VmName = $_.name.split(' (')[0]

    $cpu = $_.Group | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property value -Average -Maximum -Minimum

    $mem = $_.Group | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property value -Average -Maximum -Minimum

    $dsk = $_.Group | where {$_.MetricId -eq "disk.maxTotalLatency.latest"} | Measure-Object -Property value -Average -Maximum -Minimum

    $vmstat.CPUMax = [int]$cpu.Maximum

    $vmstat.CPUAvg = [int]$cpu.Average

    $vmstat.CPUMin = [int]$cpu.Minimum

    $vmstat.MemMax = [int]$mem.Maximum

    $vmstat.MemAvg = [int]$mem.Average

    $vmstat.MemMin = [int]$mem.Minimum

    $vmstat.DiskMax = [int]$dsk.Maximum

    $vmstat.DiskAvg = [int]$dsk.Average

    $vmstat.DiskMin = [int]$dsk.Minimum 

  }

    Write-host "Got stats on $vm, Getting creation date.."

    $vmevents = Get-VIEvent $vm -MaxSamples([int]::MaxValue) | Where-Object {$_.FullFormattedMessage -like "Clone*"} |Select CreatedTime

    if ($vmevents) {       

        Write-Host "Found a Cloned $vm"

        $type = "Cloned"

    }

    if (!$vmevents) {

        $vmevents = Get-VIEvent $vm -MaxSamples([int]::MaxValue) | Where-Object {$_.FullFormattedMessage -like "Template"} |Select CreatedTime

        Write-Host "Searching by Template"

        $type = "From Template"

    }

    #If no events were found, search for events where the VM was created from scratch

    if (!$vmevents) {

        $vmevents = Get-VIEvent $VM -MaxSamples([int]::MaxValue) | Where-Object {$_.FullFormattedMessage -like "Created*"} |Select CreatedTime

        Write-Host "Searching by Created"

        $type = "From Scratch"

    }

    #If no events were found, search for events where the VM was discovered

    if (!$vmevents) {

        $vmevents = Get-VIEvent $VM -MaxSamples([int]::MaxValue) | Where-Object {$_.FullFormattedMessage -like "Discovered*"} |Select CreatedTime

        Write-Host "Searching by Discovered"

        $type = "Discovered"

    }

    #If no events were found, search for events where the VM was connected (typically from Backup Restores)

    if (!$vmevents) {

        $vmevents = Get-VIEvent $VM -MaxSamples([int]::MaxValue) | Where-Object {$_.FullFormattedMessage -like "* connected"} |Select CreatedTime

        Write-Host "Searching by Connected"

        $type = "Connected"

    }

    #I have no idea how this VM came to be.

    if (!$vmevents) {

        Write-Host "No clue how this VM got here!"

        $type = "Immaculate Conception"

    }

    foreach($event in $vmevents){  

    $birthday = $event.CreatedTime.ToString("MM/dd/yy")   }

    Invoke-Sqlcmd -serverinstance rndmanager  -Database cloudstats -Username 'sa' -Password ***' -Query "insert into VMStatus (Org, Owner,VMName,PoweredOn,LastPowerOff,hostName,StorageArrays, vmCPU,vmMemory,VMDisksAllocated,VMDiskUsed,ReportDate,CPUMax,CPUAvg,CPUMin,MemMax,MemAvg,MemMin,DiskMax,DiskAvg,DiskMin,CreateDate) values ('$vmOrg','$owner','$name','$poweredOn','$LastpowerOffDate','$vmhost', '$($vmds.Name)', '$vmCPU','$vmMem','$allocatedSpaceGB','$UsedSpaceGB','$time','$($vmstat.CPUMax)','$($vmstat.CPUAvg)','$($vmstat.CPUMin)','$($vmstat.MemMax)','$($vmstat.MemAvg)','$($vmstat.MemMin)','$($vmstat.DiskMax)','$($vmstat.DiskAvg)','$($vmstat.DiskMin)','$birthday')"          

    write-output "$vm done"      

       }

[LucD]

There are a couple of things that could improve the execution duration

• You do the same call to Get-VIEvent five times per VM. Since you are fecthing all the events and then the filtering with the Where-clause, you can reduce this to one call to Get-VIEvent
• You are getting all the events that are kept in the vCenter DB, an alternative is to use my Get-VIEventPlus, where you can specify the type of event on the call.
• Same goes for the Get-VMLastPoweredOnDate function
• You are calling Get-Stat for each VM. An alternative could be to first collect all the VMs and then call Get-Stat only once. With the Group-Object cmdlet you can organise the returned statistical data
• You call Get-Stat and ask for the data of the last day, yet you limit the number of returned results to 100. For a day there are 60/5*24 = 288 results. Decide what you need

[GScully42]

Thanks for taking a look, I'll implement those changes and try again.