VI-DB db-base-data-sub-v2.5.ps1

From vwiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
<# ========================================================================================================================================
  Database Base Data Getter - Sub-script
  =========================================================================================================================================
  Simon Strutt        Apr 2011
  =========================================================================================================================================

 Version 2 
  - Initial creation !
  
 Version 2.1 - July 2011
  - Performance improvements (reduce's runtime by about 60%)
  -- Improved VM Cluster get to use lookup table
  -- Changed VM Guest OS get to use existing VM object
  - Bugfix: Lab Manager VM's not being removed after deletion (include checking of Persistent ID)
  
 Version 2.2
  - Minor improvements to error messages
  - Performance improvements
  -- Caching of Get-VMHostNetworkAdapter during ESX info gather
  - Added VMTools state and VM version
  
 Version 2.3
  - Added ESX NIC firmware version

 Version 2.4
  - Bugfix: VM's not in cluster don't get added to db (introduced in v2.1)
  - Bugfix: "Couldn't find ESX ID for VM" error catch not working
  - Added VM custom attributes
  - Added vApps
 
 Version 2.4.1
  - Bugfix: vm2ip can get populated with incorrect IP when VM is shutdown
  - Bugfix: vApps with no VM's not handled (causing invalid SQL statement, and so script crash) 
  
 Version 2.5
  - Bugfix: Remove old VMDK's for VMs didn't work (SQL query incorrect)
  - Bugfix: VM NICs with multiple IPs not handled correctly
  - Improved error logging (DS inaccessible, VMs not in cluster)
  - Added sev0 error log at completion to signal parent script that script exited cleanly


TO DO
  - ADD "pass completed" to db after sanity check and check for in parent script
  - Change VC last updated field update to end of run
  - Improve removed VM clearup (vmdk, vm_nic)
  - Add basic process perf stats
  - Improve efficiency in VM data gathering
  
  
 ========================================================================================================================================#>

# Get/handle job parameters
$var = $Input.<>4__this.Read()
$VC_name = $var[0]
$VC_vcid = $var[1]
$cred = $var[2]
$DBuser = $var[3]
$DBpass = $var[4]
$database = $var[5]
$MySQLHost = $var[6]
$runid = $var[7]
Set-Location $var[8]

# Include library files
. .\lib\Standard-v4.ps1
. .\lib\PowerCLI-helper-v1.1.ps1
. .\temp\DS-LUN-v4.ps1
. .\lib\getsnapshotsize-1.ps1
. .\lib\MySQL-v1.ps1
 
$start = Get-Date
$LogStamp = "Time"                      # Log timestamp format (Duration or Time (stamp))

Log "Started sub-script run at $start"

# Load VMware PS Snapin
Log("Loading VMware PowerShell Snapin...")
Add-PsSnapin *VMware*

$ScriptVer = [regex]::matches(($MyInvocation.MyCommand.Name), "(?<=v)[0-9]+(\.[0-9])+(?=.)")
Log ("Script version is $ScriptVer (" + (Get-PowerCLIversion).UserFriendlyVersion + " / Powershell " + (get-host).Version.ToString() + ")")

# Flags
$UpdateScriptRun = 1                   # Update script runs log in database
$LogNonQueries = 1                     # Non Query (insert/update etc) logging (gets forced if in debug mode)
$UpdateCL = 1                          # Clusters
$UpdateESX = 1                         # ESXs
$UpdateDS = 1                          # Datastores
$UpdateNW = 1                          # Networks
$UpdateVApp = 1
$UpdateVM = 1                          # Virtual Machines
$UpdateLM = 1                          # Lab Manager (update VM external IPs)

if (!$runid) {
    $UpdateScriptRun = 0
}
    
# Connect to MySQL database
$SQLconn = ConnectMySQL $DBuser $DBpass $MySQLHost $database
 
if (!$UpdateCL -or !$UpdateESX -or !$UpdateDS -or !$UpdateNW -or !$UpdateVM) {
    $UpdateScriptRun = 0
    Log("Updating of script run info in db disabled (performing partial run)")
}
        
function Log-Error($text, $vcid=$VC_vcid, $sev=3) {
    $script:ErrorLog += $text
    Log ("ERROR: " + $text)
    Log ("Error count now " + $ErrorLog.Length)
    if ($UpdateScriptRun) {
        ExecuteMySQLNonQuery("INSERT INTO script_run_error (runid, sev, vcid, text) VALUES ($runid, $sev, $vcid, '" + (EscQuote $text) + "');")
    }
}
        
function Log-Perf {
    Log ("Perf CPU(sec): " + [Math]::Round($proc.cpu, 0) + ", Paged Mem (MB): " + [Math]::Round(($proc.pm/1024), 0) + ", WrkSet Mem (MB): " + [Math]::Round(($proc.ws/1024), 0))
}

# Connect to vCentre
try {
    Log("Connecting to " + $VC_name)
    $VCconn = Connect-VIServer -Server $VC_name -Credential $cred -WarningAction SilentlyContinue -ErrorAction "Stop"
} catch [VMware.VimAutomation.ViCore.Types.V1.ErrorHandling.InvalidLogin] {
    Log("Unable to connect to vCentre, invalid logon error !!")
    Log("Abandoning further script processing in order to prevent potential account lockout.")
    Log-Error -text "vCentre invalid login error, script halt to prevent potential account lockout." -sev 2
    $Major_Error = 99
    Break
} catch {
    Log-Error -text ("Unable to connect to vCentre - " + $_) -sev 2
    $Major_Error += 1
    Continue
}

$vcinfo = (Get-View ServiceInstance).Content.About        
if ($UpdateScriptRun) {
    # Get VC info
    ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=$VC_vcid;")
}
if ($vcinfo.Version -lt 4) {
    $UpdateVApp = 0
    Log("Updating of vApp info disabled (this is a v3 vCentre!)")
}


        
# Clusters ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateCL) {
    # Get existing clusters from VC
    $vcClusters = Get-Cluster
            
    foreach ($cl in $vcClusters) {
        # Add/update cluster
        if ($cl.DrsEnabled) {
            $query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsAutomationLevel + "', 1) "
            $query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsAutomationLevel + "', exist=1;"
        } else {
            $query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsEnabled.GetHashCode() + "', 1) "
            $query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsEnabled.GetHashCode() + "', exist=1;"
        }
        ExecuteMySQLNonQuery($query)
    }
            
    # Mark clusters that no longer exist
    $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid=$VC_vcid AND exist=1;")
    foreach ($cl in $dbClusters) {
        if (!($vcClusters |?{$_.Name -eq $cl.name})) {
            if (!$cl.clid) {
                Continue
            }
            Log ("HIDE: " + $cl.name)
            ExecuteMySQLNonQuery("UPDATE cluster SET exist=0 WHERE clid=" + $cl.clid + ";")
        }
    }
}
        
# ESX --------------------------------------------------------------------------------------------------------------------------------
if ($UpdateESX) {
    $vcESXs = Get-VMHost
            
    # Get clusters and ESXs from db
    $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + " AND exist=1;")
            
    # Add/update info in db
    foreach ($esx in $vcESXs) {
        $clid = $dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid
        if (!$clid) {
            $clid = "NULL"
        }
        $name = [regex]::matches($esx.Name, "(^\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b)|(\A(\w|-)+)") 
        $query = "INSERT INTO esx (vcid, clid, name, exist, ver, build, model, cpu_core, cpu_sock, mem, cpu_model) "
        $query += "VALUES (" + $VC_vcid + ", $clid, '" + $name + "', 1"
        $query += ", '" + $esx.Version + "', " + $esx.Build + ", '" + ($esx.Model -replace ' {2,}',' ') + "', " + $esx.NumCpu + ", " + $esx.ExtensionData.Hardware.CpuPkg.Count + ", " + $esx.MemoryTotalMB
        $query += ", '" + $esx.ExtensionData.Summary.Hardware.CpuModel + "') ON DUPLICATE KEY UPDATE esxid=LAST_INSERT_ID(esxid), clid=$clid"
        $query += ", exist=1, ver='" + $esx.Version + "', build=" + $esx.Build + ", model='" + ($esx.Model -replace ' {2,}',' ')
        $query += "', cpu_core=" + $esx.NumCpu + ", cpu_sock=" + $esx.ExtensionData.Hardware.CpuPkg.Count + ", mem=" + $esx.MemoryTotalMB + ", cpu_model='" + $esx.ExtensionData.Summary.Hardware.CpuModel + "';"
        ExecuteMySQLNonQuery($query)
                
        # Get esxid from last query
        $esxid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
        #Debug($esx.Name.Split(".")[0] + " esxid is $esxid")
                
        $query = "INSERT INTO esx_state (esxid, state) VALUES (" + $esxid + ", '" + $esx.ConnectionState + "') ON DUPLICATE KEY UPDATE state='" + $esx.ConnectionState + "';"
        ExecuteMySQLNonQuery($query)
            
        # Add/update info in extended db tables
        if ($esx.ConnectionState.ToString() -notmatch "Connected" -and $esx.ConnectionState.ToString() -notmatch "Maintenance") {
            Log-Error ("ESX $name not connected so unable to get NIC / HBA info")
            Continue
        }

        # Store objects which get re-used for efficiency
        $ESXview = Get-View -VIObject $ESX
        $VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMHost $esx 
                
        # Extended data
        $ip = ($VMHostNetworkAdapter | Where {$_.ManagementTrafficEnabled -eq "True" -or $_.DeviceName -like "vswif*" -or $_.Name -eq "vmk0"}).IP
        $syslogSvr = $esx.ExtensionData.Config.Option.GetEnumerator() | ?{$_.Key -eq "Syslog.Remote.Hostname"} | Select -ExpandProperty Value
        if ($ESXView.Hardware.BiosInfo) {     # Works on some systems 
            $BiosVer = $ESXview.Hardware.BiosInfo.BiosVersion + " " + $ESXview.Hardware.BiosInfo.ReleaseDate.ToString("yyyy-MM-dd")   # Need date for HP servers as they use same version no for diff versions!
        } else {
            $BiosVer = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*BIOS*"}).Name
            $BiosVer = ([regex]::Matches($BiosVer, "[A-Z]\d{2} 20\d{2}-\d{2}-\d{2}"))[0].Value                  # HP regex to extract "A19 2010-09-30" for example
        }
        $dns1 = $esx.ExtensionData.Config.Network.DnsConfig.Address[0]
        $dns2 = $esx.ExtensionData.Config.Network.DnsConfig.Address[1]
                
        $query = "INSERT INTO esx_ext (esxid, ip, domain, dns_svr1, dns_svr2, syslog, bios_ver) VALUES ($esxid, INET_ATON('$ip'), '" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName
        $query += "', INET_ATON('$dns1'), INET_ATON('$dns2'), '$syslogSvr', '$BiosVer') "
        $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('$ip'), domain='" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName + "', dns_svr1=INET_ATON('$dns1'), "
        $query += "dns_svr2=INET_ATON('$dns2'), syslog='$syslogSvr', bios_ver='$BiosVer';"
        ExecuteMySQLNonQuery($query)
                
        # NTP data
        $idx=0
        $ntp_svrs = $esx.ExtensionData.Config.DateTimeInfo.NtpConfig.Server
        foreach ($ntp in $ntp_svrs) {
            ExecuteMySQLNonQuery("INSERT INTO esx_ntp (esxid, idx, svr) VALUES ($esxid, $idx, '$ntp') ON DUPLICATE KEY UPDATE svr='$ntp';")
            $idx += 1
        }
        # Remove NTP entires that no longer exist
        $dbESXntps = ExecuteMySQLQuery("SELECT svr FROM esx_ntp WHERE esxid=$esxid;")
        foreach ($dbESXntp in $dbESXntps) {
            if (!$dbESXntp.svr -or ($ntp_svrs |?{$_ -eq $dbESXntp.svr})) {
                Continue
            }
            Log("REMOVE: " + $esx.name + " " + $dbESXntp.svr)
            ExecuteMySQLNonQuery("DELETE FROM esx_ntp WHERE esxid=$esxid AND svr='" + $dbESXntp.svr + "';")
        }
                
        # HBA data
        $vmhbas = $esx.ExtensionData.Config.StorageDevice.HostBusAdapter | Where {$_.Key -like "*FibreChannel*"}
        foreach ($vmhba in $vmhbas) {
            $ver = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*" + $vmhba.Driver + "*"}).Name
            $ver = ([regex]::Matches($ver, "(\b\d)(.*?)(?=\s)"))[0].Value
            $query = "INSERT INTO esx_hba (esxid, device, model, driver, drv_ver) VALUES ($esxid, '" + $vmhba.Device + "', '" + $vmhba.Model + "', '" + $vmhba.Driver + "', '$ver') "
            $query += "ON DUPLICATE KEY UPDATE device='" + $vmhba.Device + "', model='" + $vmhba.Model + "', driver='" + $vmhba.Driver + "', drv_ver='$ver';"
            ExecuteMySQLNonQuery($query)
        }
                
        # Remove HBA's that no longer exist
        $dbESXhbas = ExecuteMySQLQuery("SELECT device FROM esx_hba WHERE esxid=$esxid;")
        foreach ($dbESXhba in $dbESXhbas) {
            if (!$dbESXhba.device -or ($vmhbas |?{$_.Device -eq $dbESXhba.device})) {
                 Continue
            }
            Log("REMOVE: " + $esx.name + " " + $dbESXhba.device)
            ExecuteMySQLNonQuery("DELETE FROM esx_hba WHERE esxid=$esxid AND device='" + $dbESXhba.device + "';")
        }
                
        # NIC data
        $vmnics = $VMHostNetworkAdapter | Where {$_.Id -like "*.PhysicalNic*"}
        $nicDevices = $esxView.Hardware.PciDevice | Where {$_.ClassId -eq 512}
        foreach ($vmnic in $vmnics) {
            $ver = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like $vmnic.ExtensionData.Driver + " driver*"} | Get-Unique).Name
            $ver = ([regex]::Matches($ver, "(\b\d)(.*)(?=\s)"))[0].Value        # Strips out any superfluous text (or rather, matches a version number)
            $fware = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like $vmnic.ExtensionData.Driver + " device firmware*"} | Get-Unique).Name
            $fware = ([regex]::Matches($fware, "(\b\d)(.*)(?=\s)"))[0].Value        # Strips out any superfluous text (or rather, matches a version number)
            $nicDev = $nicDevices | Where {$_.Id -eq $vmnic.ExtensionData.Pci} | Select VendorName, DeviceName
            #Log ("Device   : " + $vmnic.DeviceName)
            #Log ("Speed    : " + $vmnic.BitRatePerSec + " MB")
            #Log ("Driver   : " + $vmnic.ExtensionData.Driver)
            #Log ("Version  : " + )
            #Log ("PCI      : " + $vmnic.ExtensionData.Pci)
            #Log ("Model    : " + $nicDev.VendorName + " " + $nicDev.DeviceName)
            $query = "INSERT INTO esx_nic (esxid, device, speed, model, driver, drv_ver, fware) VALUES ($esxid, '" + $vmnic.DeviceName + "', " + $vmnic.BitRatePerSec
            $query += ", '" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', '" + $vmnic.ExtensionData.Driver + "', '$ver', '$fware') ON DUPLICATE KEY UPDATE "
            $query += "speed=" + $vmnic.BitRatePerSec + ", model='" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', driver='" + $vmnic.ExtensionData.Driver + "', drv_ver='$ver', fware='$fware';"
            ExecuteMySQLNonQuery($query)                
        }
                
        # Remove NIC's that no longer exist
        $dbESXnics = ExecuteMySQLQuery("SELECT device FROM esx_nic WHERE esxid=$esxid;")
        foreach ($dbESXnic in $dbESXnics) {
            if (!$dbESXnic.device -or ($vmnics |?{$_.DeviceName -eq $dbESXnic.device})) {
                Continue
            }
            Log("REMOVE: " + $esx.name + " " + $dbESXnic.device)
            ExecuteMySQLNonQuery("DELETE FROM esx_nic WHERE esxid=$esxid AND device='" + $dbESXnic.device + "';")
        }
    }           
            
    # Mark ESX's that no longer exist
    $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($esx in $dbESXs) {
        if (!$esx.esxid -or ($vcESXs |?{$_.Name.Split(".")[0] -eq $esx.name})) {
            Continue
        }
        Log("HIDE: " + $esx.name)
        ExecuteMySQLNonQuery("UPDATE esx SET exist=0 WHERE esxid=" + $esx.esxid + ";")
    }
            
    # Check for ESX's that have moved cluster
    $dbESXs = ExecuteMySQLQuery("SELECT esxid, name, COUNT(*) AS count, clid FROM esx WHERE vcid=" + $VC_vcid + " AND exist=1 GROUP BY name;")
    foreach ($esx in $dbESXs) {
        if ($esx.count -gt 1) {
            Log("HIDE dupe: " + $esx.name)
            # First esxid will be old, SELECT provides the first ID, so easy to remove
            ExecuteMySQLNonQuery("UPDATE esx SET exist=0 WHERE esxid=" + $esx.esxid + ";")
        }
    }
                 
}
        
# Datastores ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateDS) {
    $vcDSs = Get-Datastore
            
    # Get SAN model(s) and LUN ID's
    if ($vcinfo.version -lt 4) {
        Log("Getting v3 LUN info...")
        $luns = Get-DS-LUNs-v3 (Get-VMHost)
    } else {
        Log("Getting v4 LUN info...")
        $luns = Get-DS-LUNs-v4
    }
    Log("...got " + $luns.count + " LUNs")
            
    foreach ($ds in $vcDSs) {
        if (!$ds.Accessible) {
            Log-Error ("Datastore is inaccessible/down: " + $ds.Name)
            Continue
        }
        $lun = $luns |?{$_.Datastore -eq $ds.Name}
        if (!$lun) {
            Log-Error ("Couldn't find LUN ID for datastore " + $ds.Name)
            Continue
        }
        # Workaround for occassional duplicate LUNs retruned by Get-DS-LUN
        if ($lun.count) {
            $lun = $lun[0]
        }
        $query = "INSERT INTO ds (vcid, name, exist, size, used, san_model, lun) VALUES ('" + $VC_vcid + "', '" + $ds.Name + "', 1, " + $ds.CapacityMB + ", " + ($ds.CapacityMB - $ds.FreeSpaceMB)
        $query += ", '" + $lun.Make + " " + $lun.Model + "', " + $lun.LUN + ") ON DUPLICATE KEY UPDATE "
        $query += "exist=1, size=" + $ds.CapacityMB + ", used=" + ($ds.CapacityMB - $ds.FreeSpaceMB) + ", san_model='" + $lun.Make + " " + $lun.Model + "', lun=" + $lun.LUN + ";"
        ExecuteMySQLNonQuery($query)
    }
            
    # Link ESX's to DS's
    $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
    $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($esx in $dbESXs) {
        if (!$esx.esxid) {
            Continue
        }
        $vcESXDCs = Get-Datastore -VMHost (Get-VMHost ($esx.name + "*"))
        foreach ($esxdc in $vcESXDCs) {
            # Catch linking failure (prevents SQL query failure, allows script to continue)
            $dsid = $dbDSs |?{$_.name -eq $esxdc.Name} | Select -ExpandProperty dsid
            if (!$dsid) {
                Log-Error ("Couldn't find " + $esx.name + " datastore " + $esxdc.Name + " in db")
                Continue
            }
            ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid +");")
        }
    }    
            
    # Mark datastores that no longer exist
    $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($ds in $dbDSs) {
        if (!($vcDSs |?{$_.Name -eq $ds.name})) {
            if (!$ds.dsid) {
                Continue
            }
            Log ("HIDE: " + $ds.name)
            ExecuteMySQLNonQuery("UPDATE ds SET exist=0 WHERE dsid=" + $ds.dsid + ";")
        }
    }   
            
}
        
# Networks ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateNW) {
    
    # Get special network types
    $MgmtNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.ManagementTrafficEnabled -eq "True"} | Select -Property PortGroupName -Unique
    $vMotNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.VMotionEnabled -eq "True"} | Select -Property PortGroupName -Unique
    $FTolNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.FaultToleranceLoggingEnabled -eq "True"} | Select -Property PortGroupName -Unique
            
    # Get standard (old school) port groups
    $vcNWs = Get-VirtualPortGroup -Standard
    foreach ($nw in $vcNWs) {
        if (!$nw.VLanId) {
            $vlan = "NULL"
        } else {
            $vlan = $nw.VLanId
        }
        $is_mgmt = [int][bool]($MgmtNWs |?{$_.PortGroupName -eq $nw.Name})
        $is_vmk = [int][bool]($vMotNWs |?{$_.PortGroupName -eq $nw.Name})
        $is_ft = [int][bool]($FTolNWs |?{$_.PortGroupName -eq $nw.Name})
        $query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 0) "
        $query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=0;"
        ExecuteMySQLNonQuery($query)
    }
            
    if ($vcinfo.Version -ge 4) {
        # Get distributed port groups
        $vcNWs = Get-VirtualPortGroup -Distributed
        foreach ($nw in $vcNWs) {
            if ($nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId.GetType().IsArray) {
                $vlan= 9999
            } elseif (!$nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId) {
                $vlan = "NULL"
            } else {
                $vlan = $nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId
            }
            [int][bool]$is_mgmt = $MgmtNWs |?{$_.PortGroupName -eq $nw.Name}
            [int][bool]$is_vmk = $vMotNWs |?{$_.PortGroupName -eq $nw.Name}
            [int][bool]$is_ft = $FTolNWs |?{$_.PortGroupName -eq $nw.Name}
            $query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 1) "
            $query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=1;"
            ExecuteMySQLNonQuery($query)
        }
    } else {
        Log("Skipping dvSwitch gather (pre vSphere vCentre)")
    }

            
    # Link ESX's to NW's
    $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
    $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($esx in $dbESXs) {
        if (!$esx.esxid) {
            Continue
        }
        $vcESXNWs = Get-VirtualPortGroup -VMHost (Get-VMHost ($esx.name + "*"))
        foreach ($esxnw in $vcESXNWs) {
            # Catch linking failure (prevents SQL query failure, allows script to continue)
            $nwid = $dbNWs |?{$_.name -eq $esxnw.Name} | Select -ExpandProperty nwid
            if (!$nwid) {
                Log-Error ("Couldn't find network " + $esxnw.Name + " (vlan " + $esxnw.VLanId + ") in db")
                Continue
            }
            ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2nw (esxid, nwid) VALUES (" + $esx.esxid + ", $nwid);")
        }
    }
            
    # Mark networks that no longer exist
    $vcNWs = Get-VirtualPortGroup
    $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($ds in $dbNWs) {
        if (!($vcNWs |?{$_.Name -eq $nw.name})) {
            if (!$nw.nwid) {
                Continue
            }
            Log ("HIDE: " + $nw.name)
            ExecuteMySQLNonQuery("UPDATE nw SET exist=0 WHERE nwid=" + $nw.nwid + ";")
        }
    }
}

# vApps (create vapp) ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVApp) {
    $vcVApps = Get-VApp
    if ($vcVApps) {
        # Create base vApp entry
        $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
        
        foreach ($vApp in $vcVApps) {
            $vappvcid = [regex]::replace($vApp.Id, "VirtualApp-resgroup-v", "")
            $clid = $dbClusters |?{$_.name -eq (Get-Cluster -Id $vApp.ExtensionData.Owner)} | Select -ExpandProperty clid
            $vcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vApp.ExtensionData.ParentFolder)), "\\", "\\")
            
            if (!$clid) {
                Log-Error ("Couldn't find cluster for vApp: " + $vApp.Name + " in db")
                $clid = "NULL"
            }
            
            if ($vApp.ExtensionData.VAppConfig.Product[0].Name) {
                $prod_name = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Name + "'"
            } else {
                $prod_name = "NULL"
            }
            
            if ($vApp.ExtensionData.VAppConfig.Product[0].Version) {
                $prod_ver = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Version + "'"
            } else {
                $prod_ver = "NULL"
            }
            
            if ($vApp.ExtensionData.VAppConfig.Product[0].FullVersion) {
                $prod_fver = "'" + $vApp.ExtensionData.VAppConfig.Product[0].FullVersion + "'"
            } else {
                $prod_fver = "NULL"
            }
            
            if ($vApp.ExtensionData.VAppConfig.Product[0].Vendor) {
                $prod_vend = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Vendor + "'"
            } else {
                $prod_vend = "NULL"
            }
                    
            $query = "INSERT INTO vapp (vcid, vappvcid, clid, name, vc_path, status, exist, prod_name, prod_ver, prod_fver, prod_vend) VALUES ($VC_vcid, $vappvcid, $clid, '" + $vApp.Name + "', '$vcpath', '" + $vApp.Status
            $query += "', 1, $prod_name, $prod_ver, $prod_fver, $prod_vend) ON DUPLICATE KEY UPDATE vappvcid=$vappvcid, clid=$clid, vc_path='$vcpath', status='" + $vApp.Status + "', exist=1, prod_name=$prod_name, "
            $query += "prod_ver=$prod_ver, prod_fver=$prod_fver, prod_vend=$prod_vend;"
            ExecuteMySQLNonQuery($query)
            
        }
              
        # Mark vApps that no longer exist
        $dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid=$VC_vcid AND exist=1;")
        foreach ($vapp in $dbVApps) {
            if (!($vcVApps |?{$_.Name -eq $vapp.name})) {
                if (!$vapp.vappid) {
                    Continue
                }
                Log ("HIDE: " + $vapp.name + " vApp")
                ExecuteMySQLNonQuery("UPDATE vapp SET exist=0 WHERE vappid=" + $vapp.vappid + ";")
                ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vappid=" + $vapp.vappid + ";")
            }
        }
    } else {
        Log ("No vApps found")
    }
    
}

# Virtual Machines --------------------------------------------------------------------------------------------------------------------------
if ($UpdateVM) {
    $vcVMs = Get-VM
    $vcClusters = Get-Cluster
    $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
    $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
    $dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid = " + $VC_vcid + " AND exist=1;")
    $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + ";")
    $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + ";")
    $dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
    
    # Create cluster lookup hash
    Log("Building VM2clid lookup hash...")
    $VM2clid = @{}
    foreach ($vcCluster in $vcClusters) {
        $clid = $dbClusters |?{$_.name -eq $vcCluster.Name} | Select -ExpandProperty clid
        $clVMs = Get-VM -Location $vcCluster
        foreach ($vm in $clVMs) {
            $VM2clid[$vm.Name] = $clid
        }
    }
    Log("...completed")      
            
    # Add/update VM's in database
    foreach ($vm in $vcVMs) {

        # Catch linking failure (prevents SQL query failure, allows script to continue)
        $vmvcid = [regex]::replace($vm.Id, "VirtualMachine-vm-", "")
        
        # Version 1 - Takes ages
        #$cluster = (Get-Cluster -VM $vm).Name
        #$clid = $dbClusters |?{$_.name -eq $cluster} | Select -ExpandProperty clid
        
        # version 2 - Takes about half the time, but still 2+ secs
        #$cluster = (Get-View -ID (Get-View -ViewType HostSystem -Filter @{"Name"=$vm.VMHost.Name} -Property Parent).Parent -Property Name).Name
        #$clid = $dbClusters |?{$_.name -eq $cluster} | Select -ExpandProperty clid
        
        # Version 3 - Uses pre-built lookup hash
        $clid = $VM2clid[$vm.Name]
        
        #$vmGuest = Get-VMGuest -VM $vm

        if (!$clid) {
            # No cluster found for VM in lookup - check this is OK
            $clust = Get-Cluster -VM $vm
            if ($clust) {
                Log-Error ("Couldn't find cluster for VM: " + $vm.Name + " in db lookup (" + $clust.name + ")")
            }
            $clid = "NULL"
        }
        $esxid = $dbESXs |?{$_.name -eq $vm.VMHost.Name.Split(".")[0]} | Select -ExpandProperty esxid
        if ($esxid.Count -gt 1) {
            Log-Error ("Multiple ESX IDs (" + $esxid + ") found for VM: " + $vm.Name + " in db")
            Continue
        }
        if (!$esxid) {
            Log-Error ("Couldn't find ESX " + $vm.VMHost.Name.Split(".")[0] + " for VM: " + $vm.Name + " in db")
            Continue
        }
        if ($vm.PowerState -eq "PoweredOn") {
            $on = 1
        } else {
            $on = 0
        }
        
        # Get VM's containing folder and/or vApp info
        if (!$vm.FolderId) {
            $vApp = Get-VApp -Id $vm.ExtensionData.ParentVApp
            $vAppName = $vApp.Name
            $vappid = $dbVApps |?{$_.name -eq $vAppName} | Select -ExpandProperty vappid
            if (!$vappid) {
                Log-Error ("No vApp found for VM: " + $vm.Name + " in db")
                $vAppName = ""
                $vappid = "NULL"
            }
            if (!$vApp) {
                Log-Error ("No folder Id or vApp specified for VM: " + $vm.Name)
                $vmvcpath = ""
            } else {
                $vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vApp.ExtensionData.ParentFolder)), "\\", "\\")
            }
        } else {        
            $vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
            $vAppName = ""
            $vappid = "NULL"
        }            
        
        # Update base VM entry
        $query = "INSERT INTO vm (uuid, vmvcid, pid, name, exist, is_on, vcid, clid, esxid, vappid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', $vmvcid, '" + $vm.PersistentId 
        $query += "', '" + $vm.Name + "', 1, $on, " + $VC_vcid + ", $clid, $esxid, $vappid, '" + $vm.ExtensionData.Guest.HostName + "') ON DUPLICATE KEY UPDATE vmid=LAST_INSERT_ID(vmid), "
        $query += " vmvcid=$vmvcid, name='" + $vm.Name + "', exist=1, is_on=$on, clid=$clid, esxid=$esxid, vappid=$vappid, hostname='" + $vm.ExtensionData.Guest.HostName + "';"
        ExecuteMySQLNonQuery($query)
                
        # Get vmid from last query
        $vmid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
        
        # Update extended VM entry
        $vmxpath = $vm.ExtensionData.Config.Files.VmPathName
        $vmtools = $vm.ExtensionData.Guest.ToolsStatus.ToString()
        $vmscsi = ($vm.ExtensionData.Config.Hardware.Device | where {$_.DeviceInfo.Label -match "SCSI Controller"}).DeviceInfo.Summary
        $vm_ver = [regex]::replace($vm.Version.ToString(), "v", "")
        $vmnotes = [regex]::replace($vm.Notes, "(\s?\bvRanger.*Repository \[.*\]\s?)|(\[vRanger.*Host \[.*\]\.\]\s?)", "")
        $vmnotes = [regex]::replace($vmnotes, "'", "\'")
        $vmnotes = [regex]::replace($vmnotes, "`n", "\\n")
                
        $query = "INSERT INTO vm_ext (vmid, cpu, mem, vapp, vc_path, vmx_path, tools, scsi_hw, vm_ver, notes) VALUES ($vmid, " + $vm.NumCpu + ", " + $vm.MemoryMB + ", '$vAppName"
        $query += "', '$vmvcpath" + "', '$vmxpath', '$vmtools', '$vmscsi', '$vm_ver', '$vmnotes') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB + ", vapp='$vAppName"
        $query += "', vc_path='$vmvcpath', vmx_path='$vmxpath', tools='$vmtools', scsi_hw='$vmscsi', vm_ver='$vm_ver', notes='$vmnotes';"
        ExecuteMySQLNonQuery($query)
                
        $vcNICs = Get-NetworkAdapter -VM $vm       #(VI3 doesn't return Network Adapter x info)
        #$vcNICs = $vm.Guest.Nics
                
        # Add NICs
        foreach ($nic in $vcNICs) {
            $nwid = $dbNWs |?{$_.name -eq $nic.NetworkName} | Select -ExpandProperty nwid 
            if (!$nwid) {
                if ($nic.extensiondata.Connectable.Status -eq "untried") {
                    Log ("Ignoring " + $vm.Name + " - " + $nic.Name + " (assumed to be LabManager service VM)")
                } else {
                    Log-Error ("Couldn't find network " + $nic.NetworkName + "(" + $nic.Name + ") for VM: " + $vm.Name + " in db")
                }
                Continue
            }
            $nwno = [regex]::replace($nic.Name, "Network (?i:A)dapter ", "")
            if (!([regex]::matches($nwno, "\d").Count)) {
                if ($vm.NetworkAdapters.Count -eq 1) {
                    $nwno = 1
                } else {
                    Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name)
                    Continue
                }
            }
            $query = "INSERT INTO vm_nic (vmid, num, nwid, type) VALUES ($vmid, $nwno, $nwid, '" + $nic.Type + "') "
            $query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid), nwid=$nwid, type='" + $nic.Type + "';"
            ExecuteMySQLNonQuery($query)
                    
            # Get vnicid from last query
            $vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
                    
            # Find related IP(s)
            try {
                # Failsafe - workaround for bug where $ips still populated from previous iteration and doesn't get updated
                if ($ips) {
                    Clear-Variable ips
                }
            
                if ($vcinfo.Version -lt 4) {
                #if ($vm.Guest.nics[0].NetworkName) {
                    # Works for VI3 hosted guests
                    $ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -Property IPAddress
                } else {
                    # Works for VI4 hosted guests
                    $ips = $vm.Guest.Nics |?{$_.Device.Name -eq $nic.Name} | Select -Property IPAddress
                }
                if (!$ips) {
                    Log-Error ("Couldn't match NICs to find IP for VM: " + $vm.Name + " NIC: " + $nic.Name + " in data from VC")
                    Continue
                }
                foreach ($ip in $ips.IPAddress) {
                    #if (!$ip.IPAddress) {
                    #    Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " has no IP address in vCentre")
                    #    Continue
                    #}
                    $query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($vmid, $vnicid, INET_ATON('$ip')) "
                    $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('$ip');"
                    ExecuteMySQLNonQuery($query)
                }
            } catch {
                Log-Error("Unexpected error when trying to match NICs to find IP for VM: " + $vm.Name + " NIC: " + $nic.Name + " [" + $_.Exception.Message + "]")
            }
        }
                
        # Remove old NICs
        $dbNICs = ExecuteMySQLQuery("SELECT vnicid, num FROM vm_nic WHERE vmid=$vmid;")
        foreach ($nic in $dbNICs) {
            if (!$nic.vnicid) {
                Continue
            }
            # Catch where num of NIC's is 1 (regex fails as NIC is called "Virtual Ethernet Adapter"
            if (($vcNICs -and !$vcNICs.count) -and ($dbNICs.count -eq 2)) {
                Log ("VM: " + $vm.Name + " NIC count of one matches db and vc (workaround)")
                Continue
            }
            if (!($vcNICs |?{[regex]::replace($_.Name, "Network (?i:A)dapter ", "") -eq $nic.num})) {
                Log ("DELETE: " + $vm.name + " NIC num " + $nic.num)
                ExecuteMySQLNonQuery("DELETE FROM vm_nic WHERE vnicid=" + $nic.vnicid + ";")
                ExecuteMySQLNonQuery("DELETE FROM vm2ip WHERE vnicid=" + $nic.vnicid + ";")
            }
        }
                            
        # Add VMDK record(s)
        foreach ($vmdk in $vm.HardDisks) {
            # Create VMDK record
            $dsid = $dbDSs |?{$_.name -eq (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value)} | Select -ExpandProperty dsid
            if (!$dsid) {
                Log-Error ("Couldn't find datastore " + (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value) + " for VM: " + $vm.Name + " in db")
                Continue
            }
            $dkno = [regex]::replace($vmdk.Name, "Hard (?i:D)isk ", "")
            $dksize = [math]::Round(($vmdk.CapacityKB / 1024), 0)
            if ($vmdk.StorageFormat -eq "Thin") {
                $dkthin = 1
            } else {
                $dkthin = 0
            }
            $query = "INSERT INTO vmdk (dsid, vmid, num, size, thin, path) VALUES ($dsid, $vmid, $dkno, $dksize, $dkthin"
            $query +=  ", '" + $vmdk.Filename + "') ON DUPLICATE KEY UPDATE dsid=$dsid, size=$dksize, thin=$dkthin, path='" + $vmdk.Filename + "';"
            ExecuteMySQLNonQuery($query)
        }
                
        # Remove old VMDK's
        $dbVMDKs = ExecuteMySQLQuery("SELECT dkid, num FROM vmdk WHERE vmid=$vmid;")
        foreach ($vmdk in $dbVMDKs) {
            if (!($vm.HardDisks |?{[regex]::replace($_.Name, "Hard (?i:D)isk ", "") -eq $vmdk.num})) {
                if (!$vmdk.dkid) {
                    Continue
                }
                Log ("DELETE: " + $vm.name + " VMDK num " + $vmdk.num)
                ExecuteMySQLNonQuery("DELETE FROM vmdk WHERE dkid=" + $vmdk.dkid + ";")
            }
        }
                    
        # Work out OSID if VM is up
        if ($vm.PowerState -eq "PoweredOn") {
            #$osname = (Get-VMGuest -VM $vm).OSFullName
            $osname = $vm.ExtensionData.Config.GuestFullName
            if (!$osname) {
                Log-Error ("Couldn't update OS for VM: " + $vm.Name + " in db")
                Continue
            }
            $osid = $dbOSs |?{$_.guest_name -eq $osname} | Select -ExpandProperty osid
            if (!$osid) {
                # No existing db record for OS, add a new one
                ExecuteMySQLNonQuery("INSERT INTO os (guest_name) VALUES ('$osname');")
                $osid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
                $dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
            }
            ExecuteMySQLNonQuery("UPDATE vm_ext SET osid=$osid WHERE vmid=$vmid;")
        }
        
        # Get VM's custom attributes
        $Attribs = Get-Annotation -Entity $vm
        foreach ($attrib in $Attribs) {
            if ($attrib.Value.Length) {
                ExecuteMySQLNonQuery("INSERT into vm_attrib (vmid, name, data) VALUES ($vmid, '" + $attrib.Name + "', '" + $attrib.Value + "') ON DUPLICATE KEY UPDATE data='" + $attrib.Value + "';")
            } else {
                ExecuteMySQLNonQuery("INSERT into vm_attrib (vmid, name, data) VALUES ($vmid, '" + $attrib.Name + "', NULL) ON DUPLICATE KEY UPDATE data=NULL;")
            }
        }
    }
            
    # Mark VM's that no longer exist
    Log("Checking for VM's that no longer exist")
    $dbVMs = ExecuteMySQLQuery("SELECT vmid, uuid, pid, vmvcid, name FROM vm WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($vm in $dbVMs) {
        if ($vcVMs |?{($_.ExtensionData.Config.Uuid -eq $vm.uuid) -and ($_.PersistentId -eq $vm.pid)}) {
            #Log ("Found " + $vm.vmid + " - " + $vm.name + " (" + $vm.uuid + ")")
        } else {
            if (!$vm.vmid) {
                Continue
            }
            Log ("HIDE: " + $vm.vmid + " - " + $vm.name + " (UUID: " + $vm.uuid + " PersistID: " + $vm.pid + ")")
            ExecuteMySQLNonQuery("UPDATE vm SET exist=0 WHERE vmid=" + $vm.vmid + ";")
        }
    }
            
    # Snapshots - add/update, then remove old
    Log ("Get snapshots from VC...")
    #$vcSnaps = $vcVMs | Get-Snapshot     <--- Get-Snapshot doesn't return accurate MB used info
    # CalculateVMSnapshotsSizeMB returns property VirtualMachineID, Get-Snapshot returns VMId (swap below when reverting to Get-Snapshot)
    $vcSnaps = @()
    foreach ($vm in $vcVMs) {
        $Snaps = CalculateVMSnapshotsSizeMB -VM $vm
        if ($Snaps) {
            $vcSnaps = $vcSnaps + $Snaps
        }
    }
            
    if (!$vcSnaps) {
        Log ("No snapshots found, removing any for this VC in db")
        ExecuteMySQLNonQuery("DELETE FROM snap WHERE vcid=" + $VC_vcid + ";")        
    } else {    
       foreach ($snap in $vcSnaps) {
            # Create/update entry (must convert size into integer)
            $sid = [regex]::replace($snap.Id, "VirtualMachineSnapshot-snapshot-", "")
            if (!$snap.ParentSnapshotId) {
                $psid = "NULL"
            } else {
                $psid = [regex]::replace($snap.ParentSnapshotId, "VirtualMachineSnapshot-snapshot-", "")
            }
            $vmid = $dbVMs |?{$_.vmvcid -eq [regex]::replace($snap.VirtualMachineID, "VirtualMachine-vm-", "")} | Select -ExpandProperty vmid
            if (!$vmid) {
                Log-Error ("Couldn't find vmid for snapshot (" + $snap.Id + ", " + $snap.VirtualMachineID+ ")")
                Continue
            }
            if (!$snap.Quiesced) {         # Not returned by CalculateVMSnapshotsSizeMB
                $quiesced = "NULL"
            } else { 
                if ($snap.Quiesced -eq "True") {
                    $quiesced = 1
                } else {
                    $quiesced = 0
                }
            }
            if (!$snap.Created) {           # CalculateVMSnapshotsSizeMB returns CreateTime not Created
                $created = $snap.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")
            } else {
                $created = $snap.Created.ToString("yyyy-MM-dd HH:mm:ss")
            }
            if ($snap.PowerState -eq "PoweredOn") {
                $on = 1
            } else {
                $on = 1
            }
            if ($snap.Name.Contains("Created by vRanger")) {
                $vrange = 1
            } else {
                $vrange = 0
            }
            $query = "INSERT into snap  (vmid, sid, vcid, name, descr, created, quiesced, vrange, vm_on, size, parent_sid) VALUES ($vmid, $sid, " + $VC_vcid + ", '" + (EscQuote $snap.Name)
            $query += "', '" + (EscQuote $snap.Description) + "', '$created', $quiesced, $vrange, $on, " + [math]::Round($snap.SizeMB, 0)
            $query += ", $psid) ON DUPLICATE KEY UPDATE name='" + (EscQuote $snap.Name) + "', descr='" + (EscQuote $snap.Description) + "', size=" + [math]::Round($snap.SizeMB, 0) + ";"
            ExecuteMySQLNonQuery($query)
        }
            
        # Remove old snapshots
        $dbSnaps = ExecuteMySQLQuery("SELECT sid FROM snap WHERE vcid=" + $VC_vcid + ";")
        foreach ($snap in $dbSnaps) {
            if (!$snap.sid) {
                Continue
            }
            if (!($vcSnaps |?{$_.Id -eq ("VirtualMachineSnapshot-snapshot-" + $snap.sid)})) {
                Log ("REMOVE: VirtualMachineSnapshot-snapshot-" + $snap.sid)
                ExecuteMySQLNonQuery("DELETE FROM snap WHERE sid=" + $snap.sid + " AND vcid= " + $VC_vcid + ";")
            }
        }
    }           
}

# vApps (update VMs) -------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVApp) {

    if ($vcVApps) {
        # Create base vApp entry
        $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
        
        foreach ($vApp in $vcVApps) {
            $vappvcid = [regex]::replace($vApp.Id, "VirtualApp-resgroup-v", "")
            $vappid = ExecuteMySQLScalar("SELECT vappid FROM vapp WHERE vappvcid=$vappvcid AND vcid=$VC_vcid;")
            
            # Update vApp VM list
            if ($vApp.ExtensionData.VAppConfig.EntityConfig) {
                foreach ($vappvm in $vApp.ExtensionData.VAppConfig.EntityConfig) {
                    $vmvcid = [regex]::replace($vappvm.Key.ToString(), "VirtualMachine-vm-", "")
                    $vmid = ExecuteMySQLScalar("SELECT vmid FROM vm WHERE vcid=$VC_vcid AND vmvcid=$vmvcid;")
                    if (!$vmid) {
                        Log-Error("Failed to get vmid for " + $vappvm.Tag + " in vApp " + $vApp.Name)
                        Continue
                    }
                    $query = "INSERT INTO vapp_vm (vappid, vmid, vmvcid, ord, start_dly, start_wait, stop_dly, stop_act) VALUES ($vappid, $vmid, $vmvcid, " + $vappvm.StartOrder + ", " + $vappvm.StartDelay
                    $query += ", " + $vappvm.WaitingForGuest.GetHashCode() + ", " + $vappvm.StopDelay + ", '" + $vappvm.StopAction + "') ON DUPLICATE KEY UPDATE vmvcid=$vmvcid, ord=" + $vappvm.StartOrder
                    $query += ", start_dly=" + $vappvm.StartDelay + ", start_wait=" + $vappvm.WaitingForGuest.GetHashCode() +", stop_dly=" + $vappvm.StopDelay + ", stop_act='" + $vappvm.StopAction + "';"
                    ExecuteMySQLNonQuery($query)
                }
            }
            
            # Remove VMs on longer in vApp
            $dbVAppsVMs = ExecuteMySQLQuery("SELECT vmid, vmvcid FROM vapp_vm WHERE vappid=$vappid;")
            foreach ($vappvm in $dbVAppsVMs) {
                if (!($vApp.ExtensionData.VAppConfig.EntityConfig |?{[regex]::replace($_.Key.ToString(), "VirtualMachine-vm-", "") -eq $vappvm.vmvcid})) {
                    if (!$vappvm.vmid) {
                        Continue
                    }
                    Log ("DELETE: vmid " + $vappvm.vmid + " from vApp " + $vapp.name)
                    ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vmid=" + $vappvm.vmid + " AND vappid=$vappid;")
                }
            }
        
        }
              
        # Mark vApps that no longer exist
        $dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid=$VC_vcid AND exist=1;")
        foreach ($vapp in $dbVApps) {
            if (!($vcVApps |?{$_.Name -eq $vapp.name})) {
                if (!$vapp.vappid) {
                    Continue
                }
                Log ("HIDE: " + $vapp.name + " vApp")
                ExecuteMySQLNonQuery("UPDATE vapp SET exist=0 WHERE vappid=" + $vapp.vappid + ";")
                ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vappid=" + $vapp.vappid + ";")
            }
        }
    }
}
        
# Sanity checks -------------------------------------------------------------------------------------------------------------------------------
Log ("Perfoming sanity checks for " + $VC_name)
$SanityOK = 1
if ($UpdateCL) {
    $dbCLs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM cluster WHERE vcid=" + $VC_vcid + " AND exist=1;")
    if ($vcClusters -and -not $vcClusters.Count) {
         $vcClusterCount = 1
    } else {
         $vcClusterCount = $vcClusters.Count
    }
    if ($dbCLs -ne $vcClusterCount) {
         Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of Clusters [VC: " + $vcClusterCount + " DB: " + $dbCLs + "]")
         $SanityOK = 0
    }
}
if ($UpdateESX) {
    $dbESXs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM esx WHERE vcid=" + $VC_vcid + " AND exist=1;")
    if ($dbESXs -ne $vcESXs.Count) {
        Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of ESXs [VC: " + $vcESXs.Count + " DB: " + $dbESXs + "]")
        $SanityOK = 0
    }
}
if ($UpdateVM) {
    $dbVMs = ExecuteMySQLQuery("SELECT COUNT(*) AS total, SUM(IF(is_on=1,1,0)) AS is_on FROM vm WHERE vcid=" + $VC_vcid + " AND exist=1;")
    if ($dbVMs[1].total -ne $vcVMs.Count) {
        Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of VMs [VC: " + $vcVMs.Count + " DB: " + $dbVMs[1].total + "]")
        $SanityOK = 0
    }
    if ($dbVMs[1].is_on -ne ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count) {
        Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of powered on VMs [VC: " + ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count + " DB: " + $dbVMs[1].is_on + "]")
        $SanityOK = 0
    }
}
        
if ($SanityOK) {
    Log ("Sanity checks completed, no errors found")
} else {
    Log-Error -text ("Sanity check problems found with data for " + $VC_name) -sev 2
}

# Add finished record to db so parent script knows all completed (didn't bomb out)
Log-Error -text ($VC_name + " - completed") -sev 0

Log("Diconnecting from " + $VC_name)
Disconnect-VIServer -Server $VCconn -Confirm:$false
Log("Disconnecting from database")
DisconnectMySQL
Log("Elvis has left the building")