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

From vwiki
Revision as of 22:03, 25 October 2011 by Sstrutt (talk | contribs) (Updated categories)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
<# ========================================================================================================================================
  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")