View source for VI-DB db-base-data-sub-v2.8.ps1

<source lang="powershell">

<# ========================================================================================================================================
  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

Version 2.6
  - Bugfix: Networks not being removed from ESX's when they no longer exist / no longer configured on ESX
  - Bugfix: Datastores not being removed from ESX's when they no longer exist / no longer configured on ESX
  - Change VC last updated field update to end of run
  - Prevent VM IP address being overwritten / blanked by 0.0.0.0
  
Version 2.7
  - Bugfix: VM's showing with incorrect Cluster (affecting duplicate name VMs)
  - Bugfix: Handle foreign (eg French) names for VM hard disk and network adapte
  - Improved IP address handling (filter out non-IPv4, blank addresses)
  - Get NAT's from Vyatta router
  
Version 2.8
  - Bugfix: PS Exceptions thrown when determining network types
  - Bugfix: Unable to get NIC number for some VI3 VM's (introduced by foreign name handling)
  - Changed Vyatta NATing to so that NAT NIC has NwId that matches NAT'ed IP range
  - Add datastore block size and canonical name
  - Add ESX - LUN multipathing policy


TO DO  
  - Show ESX's in clusters with inconsistent datastores
  - Add VM disk type (specifically RDMs)
  - Add VM disk usage
  - Add DRS groups
  - Add VM Templates
  - Improve removed VM clearup (vmdk, vm_nic)
  - Add basic process perf stats
  
  
 ========================================================================================================================================#>

# 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                        # vApps
$UpdateVM = 1                          # Virtual Machines
$UpdateVyNAT = 1                       # Vyatta NATs (requires $UpdateVM

$VyattaUser = "vi-db"
$VyattaPass = "syndrome"

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 ($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]
        }
        if ($ds.ExtensionData.Info.Vmfs.Extent.Count -ne 1) {
            $canonical = "NULL"
            Log-Error ("Can't get canonical name for datastore " + $ds.Name + " (extents=" + $ds.ExtensionData.Info.Vmfs.Extent.Count + ")")
        } else {
            $canonical = "'" + $ds.ExtensionData.Info.Vmfs.Extent[0].DiskName + "'"
        }
        
        $query = "INSERT INTO ds (vcid, name, exist, size, used, san_model, lun, block, canonical) VALUES ('" + $VC_vcid + "', '" + $ds.Name + "', 1, " + $ds.CapacityMB + ", " + ($ds.CapacityMB - $ds.FreeSpaceMB)
        $query += ", '" + $lun.Make + " " + $lun.Model + "', " + $lun.LUN + ", " + $ds.ExtensionData.Info.Vmfs.BlockSizeMB + ", $canonical) ON DUPLICATE KEY UPDATE "
        $query += "exist=1, size=" + $ds.CapacityMB + ", used=" + ($ds.CapacityMB - $ds.FreeSpaceMB) + ", san_model='" + $lun.Make + " " + $lun.Model + "', lun=" + $lun.LUN + ", "
        $query += "block=" + $ds.ExtensionData.Info.Vmfs.BlockSizeMB + ", canonical=$canonical;"
        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, canonical FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($esx in $dbESXs) {
        if (!$esx.esxid) {
            Continue
        }
        $vcESXDSs = Get-Datastore -VMHost (Get-VMHost ($esx.name + "*"))
        $vcESXLuns = Get-ScsiLun -VMHost (Get-VMHost ($esx.name + "*"))
        foreach ($esxds in $vcESXDSs) {
            # Match ESX DS name to db dsid
            $dsid = $dbDSs |?{$_.name -eq $esxds.Name}
            if (!$dsid.dsid) {
                Log-Error ("Couldn't find " + $esx.name + " datastore " + $esxds.Name + " in db")
                Continue
            }
            # Get path policy
            if ($dsid.canonical) {
                $mpath = $vcESXLuns |?{$_.CanonicalName -eq $dsid.canonical} | Select -ExpandProperty MultipathPolicy
                if (!$mpath) {
                    Log-Error ("Couldn't find multipath policy for " + $esx.name + " datastore " + $esxds.Name)
                    ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid.dsid +");")
                    Continue
                }
                ExecuteMySQLNonQuery("INSERT INTO esx2ds (esxid, dsid, mpath) VALUES (" + $esx.esxid + ", " + $dsid.dsid +", '$mpath') ON DUPLICATE KEY UPDATE mpath='$mpath';")
            } else {
                ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid.dsid +");")
            }
        }
        
        # Unlink datastores that are no longer configured on ESX
        $dbESXDSs = ExecuteMySQLQuery("SELECT dsid, ds.name FROM esx2ds JOIN ds USING (dsid) WHERE esxid = " + $esx.esxid + ";")
        foreach ($esxds in $dbESXDSs) {
            if (!$esxds.nwid) {
                Continue
            }
            if (!($vcESXDSs|?{$_.Name -eq $esxds.Name})) {
                Log ("UNLINK: " + $esx.name + " Datastore " + $esxds.Name)
                #Log ("DELETE FROM esx2ds WHERE esxid=" + $esx.esxid+ " AND dsid=" + $esxds.dsid + ";")
                ExecuteMySQLNonQuery("DELETE FROM esx2ds WHERE esxid=" + $esx.esxid + " AND dsid=" + $esxds.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 + ";")
            ExecuteMySQLNonQuery("DELETE FROM esx2ds 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
        }
        if ($MgmtNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_mgmt = 1} else {$is_mgmt = 0}
        if ($vMotNW |?{$_.PortGroupName -eq $nw.Name})  {$is_vmk = 1}  else {$is_vmk = 0}
        if ($FTolNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_ft = 1}   else {$is_ft = 0}
        #$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
            }
            if ($MgmtNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_mgmt = 1} else {$is_mgmt = 0}
            if ($vMotNW |?{$_.PortGroupName -eq $nw.Name})  {$is_vmk = 1}  else {$is_vmk = 0}
            if ($FTolNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_ft = 1}   else {$is_ft = 0}
            #$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, 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);")
        }
        
        # Unlink networks that are no longer configured on ESX
        $dbESXNWs = ExecuteMySQLQuery("SELECT nwid, nw.name FROM esx2nw JOIN nw USING (nwid) WHERE esxid = " + $esx.esxid + ";")
        foreach ($esxnw in $dbESXNWs) {
            if (!$esxnw.nwid) {
                Continue
            }
            if (!($vcESXNWs|?{$_.Name -eq $esxnw.Name})) {
                Log ("UNLINK: " + $esx.name + " Network " + $esxnw.Name)
                #Log ("DELETE FROM esx2nw WHERE esxid=" + $esx.esxid+ " AND nwid=" + $esxnw.nwid + ";")
                ExecuteMySQLNonQuery("DELETE FROM esx2nw WHERE esxid=" + $esx.esxid + " AND nwid=" + $esxnw.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 + ";")
            ExecuteMySQLNonQuery("DELETE FROM esx2nw 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;")
    
    $VyattaVM = @()
    
    # 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.Id] = $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.Id]
        
        #$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
            }
            # Replaced in v2.7 with more generic (international) version below
            #$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
            #    }
            #}
            $res = [regex]::matches($nic.Name, "\d")
            if ($res.count -gt 1) {
                Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name + " - too many numbers found")
                Continue
            } elseif ($res.count -lt 1) {
                if ($vm.NetworkAdapters.Count -eq 1) {
                    $nwno = 1
                } else {
                    Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name + " - no number found")
                    Continue
                }
            } else {
                $nwno = $res[0].Value
            }
            
            $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) {
                        Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " has no IP address in vCentre")
                        Continue
                    }
                    if (!([regex]::matches($ip, "^\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"))[0].Success) {
                        Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " IP: $ip is not a valid IPv4 address")
                        Continue
                    }
                    if ($ip -eq "0.0.0.0") {
                        Log ("Ignoring " + $vm.Name + "'s IP of " + $ip)
                        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})) {
            if (!($vcNICs |?{([regex]::matches($_.Name, "\d"))[0].Value -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 ", "")
            $dkno = ([regex]::matches($vmdk.Name, "\d"))[0].Value
            
            $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 (!($vm.HardDisks |?{([regex]::matches($_.Name, "\d"))[0].value -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;")
            }
        }
        
        # Detect if VM is Vyatta router
        if ($vm.ExtensionData.Guest.Disk | Where {$_.DiskPath -eq "/opt/vyatta/etc/config"}) {
            if ($vm.PowerState -ne "PoweredOn") {
                Log("NAT router: " + $router.Name + " not powered on, skipping")
            } else {
                $router = "" | Select Name, Net1addr, Net1id, Net1name, Net2id, Net2name
                $router.Name = $vm.Name
                $RoNics = ExecuteMySQLQuery("SELECT num, nw.nwid, nw.name, CONVERT(INET_NTOA(MIN(ip)), CHAR) AS ip FROM vm_nic JOIN nw USING (nwid) JOIN vm2ip USING (vnicid) where vm_nic.vmid=$vmid GROUP BY num;")
                foreach ($nic in $RoNics) {
                    if ($nic.num -eq 1) {
                        $router.Net1addr = $nic.ip
                        $router.Net1id = $nic.nwid
                        $router.Net1name = $nic.name
                    } elseif ($nic.num -eq 2) {
                        $router.Net2id = $nic.nwid
                        $router.Net2name = $nic.name
                    }
                }
                Log("NAT router: " + $router.Name + " (" +  $router.Net1addr + ") [" + $router.Net1id + "] " + $router.Net1name + " -> [" + $router.Net2id + "] " + $router.Net2name)
                $VyattaVM += $router
            }
        }
    }
            
    # 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 + ";")
            }
        }
    }
}

# Vyatta NATs ---------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVyNAT) {
    if ($VyattaVM) {
        # Load SSH library
        . .\lib\Renci-SSH-v1.ps1
        
        # $router = "" | Select Name, Net1addr, Net1id, Net1name, Net2id, Net2name
        
        foreach ($router in $VyattaVM) {
            Log("Get NATs from " + $router.Name + "...")
            
            # Connect to router and get NAT rules
            try {
                $SshClient = New-SshSession -server $router.Net1addr -user $VyattaUser -pass $VyattaPass
                $SshCommand = New-SshCommand -SshClient $SshClient -Command "exec vbash -i -c 'show nat rules'"       

                $NatText = $SshCommand.Result.Split("`n")

                $SshCommand.Dispose()
                $SshClient.Disconnect()
                $SshClient.Dispose()
            } catch {
                Log-Error("SSH fail " + $router.Name + ": " + $_)
                Continue
            }
            
            # Extract NATs from returned data
            $nats = @()
            foreach ($line in $NatText) {
                if (!$line.length) {
                    Continue
                }
                #Write-Host ("Parsing line [$line]") 
                $text = ([regex]::matches($line, "^\d{2,4}\s+(SRC|DST)\s+eth\d\s+(s|d)addr\s\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\sto\s\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"))[0].Value
                $text = [regex]::replace($text, "\s{2,}", " ")
                if ($text.length) {
                    $nat = "" | Select RealIP, NatIP
                    $words = $text.split(" ")
                    if ($words[1] -eq "DST") {
                        $nat.RealIP = $words[6]
                        $nat.NatIP = $words[4]
                    } elseif ($words[1] -eq "SRC") {
                        $nat.RealIP = $words[4]
                        $nat.NatIP = $words[6]
                    } else {
                        #Write-Host "Ignored invalid line [$text]"
                        Continue
                    }
                    #Write-Host ("Adding " + $nat.RealIP + " - " + $nat.NatIP)
                    $nats += $nat
                }
            }
            $nats = $nats | Sort-Object -Property RealIP -Unique
            
            # Get list of VM's on router's inside network
            $query = "SELECT vm_nic.vmid, CONVERT(INET_NTOA(MIN(ip)), CHAR) AS ip FROM vm_nic JOIN vm USING (vmid) JOIN vm2ip USING (vnicid) WHERE nwid=" + $router.Net2id + " AND exist=1 GROUP BY vmid;"
            $InsideVMs = ExecuteMySQLQuery($query)
            
            # Match up NATs with VMs and add translated address to db
            foreach ($nat in $nats) {
                $NATedVmid = $InsideVMs |?{$_.ip -eq $nat.RealIP} | Select -ExpandProperty vmid
                if (!$NATedVmid) {
                    Log ("No VM found for NAT " + $nat.RealIP + " -> " + $nat.NatIP)
                    Continue
                }
                
                if ($NATedVmid.length -gt 1) {
                    Log-Error ("Too many VM's found for NAT " + $nat.RealIP + " -> " + $nat.NatIP + ", VMID's $NATedVmid")
                    Continue
                }
                
                # Create dummy VM NIC
                ExecuteMySQLNonQuery("INSERT INTO vm_nic (vmid, num, nwid, type) VALUES ($NATedVmid, 0, " + $router.Net1id + ", 'Vyatta NAT') ON DUPLICATE KEY UPDATE nwid=" + $router.Net1id + ", vnicid=LAST_INSERT_ID(vnicid);")

                # Get vnicid from last query
                $vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
                
                $query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($NATedVmid, $vnicid, INET_ATON('" + $nat.NatIP + "')) "
                $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" +  $nat.NatIP + "');"
                ExecuteMySQLNonQuery($query)
            }
                
        }
    } else {
        Log("No Vyatta VM's found to process for NATs")
    }
}
    
        
# 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
if ($UpdateScriptRun) {
    # Update VC info
    ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=$VC_vcid;")
}

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


</source>

[[Category:PowerCLI Script]]