VI-DB db-base-data-sub-v2.0.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 !
  
 TO DO
  - 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))

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

# 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
$UpdateVM = 1                          # Virtual Machines
$UpdateLM = 1                          # Lab Manager (update VM external IPs)

if (!$runid) {
    $UpdateScriptRun = 0
}
   
Log "Started sub-script run at $start"
    
# 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
}
        
if ($UpdateScriptRun) {
    # Get VC info
    $vcinfo = (Get-View ServiceInstance).Content.About
    ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=$VC_vcid;")
}
        
# 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 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 = Get-VMHostNetworkAdapter -VMHost $esx | 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)
            $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) VALUES ($esxid, '" + $vmnic.DeviceName + "', " + $vmnic.BitRatePerSec
            $query += ", '" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', '" + $vmnic.ExtensionData.Driver + "', '$ver') ON DUPLICATE KEY UPDATE "
            $query += "speed=" + $vmnic.BitRatePerSec + ", model='" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', driver='" + $vmnic.ExtensionData.Driver + "', drv_ver='$ver';"
            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) {
        $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 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 + ";")
        }
    }
}
        
# Virtual Machines --------------------------------------------------------------------------------------------------------------------------
if ($UpdateVM) {
    $vcVMs = Get-VM
    $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;")
    $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;")
            
    # Add/update VM's in database
    foreach ($vm in $vcVMs) {
        # Add base record
        # Catch linking failure (prevents SQL query failure, allows script to continue)
        $vmvcid = [regex]::replace($vm.Id, "VirtualMachine-vm-", "")
        $clid = $dbClusters |?{$_.name -eq (Get-Cluster -VM $vm).Name} | Select -ExpandProperty clid
        $vmGuest = Get-VMGuest -VM $vm
        if (!$clid) {
            Log-Error ("Couldn't find cluster " + (Get-Cluster -VM $vm).Name + " for VM: " + $vm.Name + " in db")
            Continue
        }
        $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 (!$clid) {
            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
        }
        $query = "INSERT INTO vm (uuid, vmvcid, pid, name, exist, is_on, vcid, clid, esxid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', $vmvcid, '" + $vm.PersistentId 
        $query += "', '" + $vm.Name + "', 1, $on, " + $VC_vcid + ", $clid, $esxid, '" + $vmGuest.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, hostname='" + $vmGuest.HostName + "';"
        ExecuteMySQLNonQuery($query)
                
        # Get vmid from last query
        $vmid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
                
        $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) {
                Log-Error ("Couldn't find network " + $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)
            if ($vm.Guest.nics[0].NetworkName) {
                # Works for VI3 hosted guests
                $ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -Property NetworkName, IPAddress
            } else {
                # Works for VI4 hosted guests
                $ips = $vm.Guest.Nics |?{$_.Device.Name -eq $nic.Name} | Select -Property Device, IPAddress
            }
            if (!$ips) {
                Log-Error ("Couldn't match NICs for VM: " + $vm.Name + " NIC: " + $nic.Name + " from VC")
                Continue
            }
            foreach ($ip in $ips) {
                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.IPAddress + "')) "
                $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $ip.IPAddress + "');"
                ExecuteMySQLNonQuery($query)
            }
        }
                
        # 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.num + ";")
            }
        }
            
        # Add/update extended VM info
        if (!$vm.FolderId) {
            $vApp = Get-VApp -Id $vm.ExtensionData.ParentVApp
            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)), "\\", "\\")
        }            

        $vmxpath = $vm.ExtensionData.Config.Files.VmPathName
        $vmscsi = ($vm.ExtensionData.Config.Hardware.Device | where {$_.DeviceInfo.Label -match "SCSI Controller"}).DeviceInfo.Summary
        $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, scsi_hw, notes) VALUES ($vmid, " + $vm.NumCpu + ", " + $vm.MemoryMB + ", '" + $vapp.ExtensionData.Name
        $query += "', '$vmvcpath" + "', '$vmxpath', '$vmscsi', '$vmnotes') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB + ", vapp='" + $vapp.ExtensionData.Name
        $query += "', vc_path='$vmvcpath', vmx_path='$vmxpath', scsi_hw='$vmscsi', notes='$vmnotes';"
        ExecuteMySQLNonQuery($query)
                
        # Work out OSID if VM is up
        if ($vm.PowerState -eq "PoweredOn") {
            $osname = (Get-VMGuest -VM $vm).OSFullName
            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;")
        }
    }
            
    # Mark VM's that no longer exist
    Log("Checking for VM's that no longer exist")
    $dbVMs = ExecuteMySQLQuery("SELECT vmid, uuid, vmvcid, name FROM vm WHERE vcid = " + $VC_vcid + " AND exist=1;")
    foreach ($vm in $dbVMs) {
        if ($vcVMs |?{$_.ExtensionData.Config.Uuid -eq $vm.uuid}) {
            #Log ("Found " + $vm.vmid + " - " + $vm.name + " (" + $vm.uuid + ")")
        } else {
            if (!$vm.vmid) {
                Continue
            }
            Log ("HIDE: " + $vm.vmid + " - " + $vm.name)
            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 + ";")
            }
        }
    }           
}
        
# 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
}

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