VI-DB db-base-data-v1.2.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
  =========================================================================================================================================
  Simon Strutt        Jan 2011
  =========================================================================================================================================

 Version 1 
  - Initial creation

 Version 1.1 - Mar 2011
 - Bugfix: Where VC provides no VM FolderID, VM is assigned incorrect VC folder path - now catch as error
 - Bugfix: VM's having no IPAddress in vCentre not handled properly, causing errors - now handled silently
 - Bugfix: Added vmvcid to better handle Lab Manager v4 VM's with duplicate UUIDs
 - Bugfix: ESX's moving clusters causes duplicate ESX entries - old ESX/cluster entry now gets expired
 - Added snapshot info

 Version 1.2 - Mar 2011
 - Bugfix: Snapshot sizes not right (known PowerCLI bug in Get-Snapshot that I'd wrongly assumed to be fixed)
 - Added population of snap.vrange (is snapshot a vRanger snapshot)
 - Added ESX extended data (inc IP - needs further work), ntp, hba and nic info
 - Consolidated SQL query strings

 Future improvements
 - Add sanity checks (make sure total no of clusters, ESXs, VMs in db matches VC - will prob mandate better error handling)
 - Add HA settings (isolation response, admin control, etc)
 - Add VM CBT
 - Add VM custom attributes
 - Add VM resource limits / shares
 - Calculate VMDK and total VM disk usage
 - Add state change datetime stamp
 - Catch all Powershell execptions in order to increment counter (add errors to db at later date)
 - Add who did what (event log extracting)

 ==========================================================================================================================================#>

$start = Get-Date
#$VC_List = "ESX-Check.csv"
$UserFile = "User.fil"
$PassFile = "Pass.fil"                           # Encrypted file to store password in
$Logfile = "db-base-data.log"

$Debug = 1

# Include library files
. .\lib\Standard.ps1
. .\lib\PowerCLI-helper-v1.1.ps1
. .\temp\DS-LUN-v4.ps1
. .\lib\getsnapshotsize-1.ps1

# DB connection stuff...
$user = 'powercli' 
$pass = 'powercli' 
$database = 'vi'
$MySQLHost = '192.168.196.128' 

$LabMgrSvr = "labmanager"

$Major_Error = 0                       # Major error counter
$ErrorLog = @()                        # Minor error tracker

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

if ($Debug) {
    $database = 'vi_test'
    $Logfile = "db-base-data-debug.log"
    $LogNonQueries = 1
}

Start-Transcript -Path $Logfile
Log "Started script run at $start" 

if (!$UpdateCL -or !$UpdateESX -or !$UpdateDS -or !$UpdateNW -or !$UpdateVM) {
    $UpdateScriptRun = 0
    Log("Updating of script run info in db disabled (performing partial run)")
}

$ScriptVer = [regex]::matches(($MyInvocation.MyCommand.Name), "(?<=v)[0-9]+(\.[0-9])+(?=.)")
Log "Script version is $ScriptVer"


# MySQL functions ========================================================================================================================

function ConnectMySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { 
    # Load MySQL .NET Connector Objects 
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 
 
    # Open Connection 
    $connStr = "server=$MySQLHost;port=3306;uid=$user;pwd=$pass;database=$database;Pooling=FALSE" 
    try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Log $_
        Exit
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        exit
    }
    Log ("Connected to MySQL database " + $conn.Database + " on " + $conn.DataSource + " (ver " + $conn.ServerVersion + ")")

    return $conn 
} 

function DisconnectMySQL {
    $SQLconn.Close()
}

function ExecuteMySQLNonQuery([string]$query) { 
    # NonQuery - Insert/Update/Delete query where no return data is required
    
    if ($LogNonQueries) {
        Log ($query)
    }
    
    try {
        $cmd = $SQLconn.CreateCommand()               # Create command object
        $cmd.CommandText = $query                     # Load query into object
        $RowsInserted = $cmd.ExecuteNonQuery()        # Execute command
        $cmd.Dispose()                                # Dispose of command object
        if ($RowsInserted) { 
            return $RowInserted 
        } else { 
            return $false 
        }
    } catch {
        Log ("ExecuteMySQLNonQuery($query) error...")
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Exit
    }
}

function ExecuteMySQLQuery([string]$query) { 
    # Query - Select etc query where return data is expected
    try {
        $cmd = $SQLconn.CreateCommand()                                             # Create command object
        $cmd.CommandText = $query                                                   # Load query into object
        $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)     # Create data adapter from query command
        $dataSet = New-Object System.Data.DataSet                                   # Create dataset
        $dataAdapter.Fill($dataSet, "data")                                         # Fill dataset from data adapter, with name "data"              
        $cmd.Dispose()
        $dataAdapter.Dispose()
        return $dataSet.Tables["data"]                                              # Returns an array of results
    } catch {
        Log ("ExecuteMySQLQuery($query) error...")
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Exit
    }
}

function ExecuteMySQLScalar([string]$query) {
    # Scalar - Select etc query where a single value of return data is expected
    try {
        $cmd = $SQLconn.CreateCommand()                                             # Create command object
        $cmd.CommandText = $query                                                   # Load query into object
        $cmd.ExecuteScalar()                                                        # Execute command
    } catch {
        Log ("ExecuteMySQLScalar($query) error...")
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Exit
    }
}

function EscQuote([string]$text) {
    [regex]::replace($text, "'", "\'")
}

# =============================================================================================
# Lab Manager SOAP connection functions (adapted from POSH - http://poshcode.org/753)

function New-ObjectFromProxy {
	param($proxy, $proxyAttributeName, $typeName)

	# Locate the assembly for $proxy
	$attribute = $proxy | gm | where { $_.Name -eq $proxyAttributeName }
	$str = "`$assembly = [" + $attribute.TypeName + "].assembly"
	invoke-expression $str

	# Instantiate an AuthenticationHeaderValue object.
	$type = $assembly.getTypes() | where { $_.Name -eq $typeName }
	return $assembly.CreateInstance($type)
}

function Connect-LabManager {
	param($server, $credential)
    
    Write-Host "Connecting to $server..."

	# Log in to Lab Manager's web service.
	$server = "https://" + $server + "/"
	$endpoint = $server + "LabManager/SOAP/LabManager.asmx"
    try {
	   $proxy = new-webserviceproxy -uri $endpoint -cred $credential -ErrorAction:Stop
    } catch {
        Write-Host $_
        if ($_.ErrorDetails.Message -match "401: Unauthorized") {
            Remove-Item $UserFile
            Remove-Item $PassFile
            Write-Host "User/pass credential cache files have been deleted"
        } else {
             Write-Host $_.Exception.Message
             Write-Host $_.Exception.GetType().FullName
        }     
        PressAnyKeyToExit
    }

	# Before continuing we need to add an Authentication Header to $proxy.
	$authHeader = New-ObjectFromProxy -proxy $proxy -proxyAttributeName "AuthenticationHeaderValue" -typeName "AuthenticationHeader"
	$authHeader.username = $credential.GetNetworkCredential().UserName
	$authHeader.password = $credential.GetNetworkCredential().Password
	$proxy.AuthenticationHeaderValue = $authHeader
	return $proxy
}

function Ignore-SslErrors {
	# Create a compilation environment
	$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
	$Compiler=$Provider.CreateCompiler()
	$Params=New-Object System.CodeDom.Compiler.CompilerParameters
	$Params.GenerateExecutable=$False
	$Params.GenerateInMemory=$True
	$Params.IncludeDebugInformation=$False
	$Params.ReferencedAssemblies.Add("System.DLL") > $null
	$TASource=@'
	  namespace Local.ToolkitExtensions.Net.CertificatePolicy {
	    public class TrustAll : System.Net.ICertificatePolicy {
	      public TrustAll() { 
	      }
	      public bool CheckValidationResult(System.Net.ServicePoint sp,
	        System.Security.Cryptography.X509Certificates.X509Certificate cert, 
	        System.Net.WebRequest req, int problem) {
	        return true;
	      }
	    }
	  }
'@ 
	$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
	$TAAssembly=$TAResults.CompiledAssembly

	## We now create an instance of the TrustAll and attach it to the ServicePointManager
	$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
	[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll
}

function Log-Error($text) {
#    if (!$ErrorLog) {
#        $ErrorLog = @()
#    }
    $script:ErrorLog += $text
    Log ("ERROR: " + $text)
    Log ("Error count now " + $ErrorLog.Length)
}

# Pre Amble ==============================================================================================================================

# Connect to MySQL database
$SQLconn = ConnectMySQL $user $pass $MySQLHost $database

# Create script_run entry
if ($UpdateScriptRun) {
    ExecuteMySQLNonQuery("INSERT INTO script_run (start, version) VALUES (NOW(), '$ScriptVer');")
    $runid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
}

# Get list of VC's (checks the SQL connection is in good working order as well)
$VCs = ExecuteMySQLQuery("SELECT vcid, name, site FROM vc;")

# Disconnect any existing VI Server sessions (otherwise you can end up with duplicate VM's)
if ($DefaultVIServers.Count) {
    Log("Disconnect existing vCentre server connections...")
    Disconnect-VIServer -Server * -Force -Confirm:$false
}

# Load password credential from encrypted file
$pass = Get-Content $PassFile | ConvertTo-SecureString
$user = Get-Content $UserFile
$cred = New-Object System.Management.Automation.PsCredential($user, $pass)

# Business Loop ============================================================================================================================

foreach ($vc in $VCs) {
    # Check its a valid record 1st
    if (!$vc.vcid) {
        Continue
    }
    
    # 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.")
        $Major_Error = 99
        Break
    } catch {
        Log("Unable to connect to vCentre - " + $_)
        $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;")
        #$dbESXs = ExecuteMySQLQuery("SELECT esxid, name from esx WHERE vcid = " + $vc.vcid + ";")
        
        # Add/update info in db
        foreach ($esx in $vcESXs) {
            $query = "INSERT INTO esx (vcid, clid, name, exist, ver, build, model, cpu_core, cpu_sock, mem, cpu_model) "
            $query += "VALUES (" + $vc.vcid + ", " + ($dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid) + ", '" + $esx.Name.Split(".")[0] + "', 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=" + ($dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty 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

            # 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
            }
            # TO DO cater for NTP server removal
            $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) {
        $vcNWs = Get-VirtualPortGroup
        
        foreach ($nw in $vcNWs) {
            $query = "INSERT INTO nw (vcid, name, exist, vlan) VALUES ('" + $vc.vcid + "', '" + $nw.Name + "', 1, " + $nw.VLanId + ") "
            $query += "ON DUPLICATE KEY UPDATE exist=1, vlan=" + $nw.VLanId + ";"
            ExecuteMySQLNonQuery($query)
        }
        
        # 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
        $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
            }
            <# ONLY REQUIRED ON FIRST RUN OF v1.1 TO UPDATE EXISTING VM's WITHOUT POPULATED VMVCID's
            $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 += "name='" + $vm.Name + "', exist=1, is_on=" + $on + ", clid=" + $clid + ", esxid=" + $esxid + ", hostname='" + $vmGuest.HostName + "';"
            #>
            $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
            
            foreach ($nic in $vcNICs) {
                # Add NIC
                $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)
                $ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -Property NetworkName, 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)
                }
                
            }
                        
            # 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)
                
            }
        
            # Add/update extended VM info
            if (!$vm.FolderId) {
                Log-Error ("No folder Id specified for VM: " + $vm.Name)
                $vmvcpath = ""
            } else {        
                $vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
            }            
            #$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")  <-- v1.0 (replaced by if above)
            $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, vc_path, vmx_path, scsi_hw, notes) VALUES ($vmid, " + $vm.NumCpu + ", " + $vm.MemoryMB + ", '$vmvcpath"
            $query += "', '$vmxpath', '$vmscsi', '$vmnotes') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB
            $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
        $dbVMs = ExecuteMySQLQuery("SELECT vmid, vmvcid, name FROM vm WHERE vcid = " + $vc.vcid + " AND exist=1;")
        foreach ($vm in $dbVMs) {
            if (!($vcVMs |?{$_.Name -eq $vm.name})) {
                if (!$vm.vmid) {
                    Continue
                }
                Log ("HIDE: " + $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 + ";")
                }
           }
       }
            
    }
          
    Disconnect-VIServer -Server $VCconn -Confirm:$false
}

#Lab Manager ---------------------------------------------------------------------------------------------------------------------------------
if ($UpdateLM) {
    # Connect to Lab Manager server
    try {
        Log("Connecting to " + $LabMgrSvr)
        Ignore-SslErrors
        $labManager = Connect-LabManager -server $LabMgrSvr -credential $cred
    } catch {
        Log-Error("Unable to connect to Lab Manager - " + $_)
        $Major_Error += 1
    }

    if ($labManager) {
        # Get List of VM's
        Log("Getting Lab Manager VM info...")
        $lmVMs = @()
        $lmConfigs = $labManager.ListConfigurations(1) 
        foreach ($config in $lmConfigs) {
            if ($config.isDeployed) { 
                $confVMs = $labManager.ListMachines($config.id)
                foreach ($vm in $confVMs) {
                    if ($vm.isDeployed) {
                        $VMrow = "" | Select Path, ConfigOrg, ConfigName, VMname, VMextIP
                        $VMrow.Path = $config.bucketName + "\\\\" + $config.name
                        $VMrow.ConfigOrg = $config.bucketName
                        $VMrow.ConfigName = $config.name
                        $VMrow.VMname = $vm.name
                        $VMrow.VMextIP = $vm.externalIP
                        $lmVMs = $lmVMs + $VMrow
                    }
                }
            }
        }
        
        foreach ($vm in $lmVMs) {
            # Get VM's db ID
            $query = "SELECT vmid FROM vm JOIN vm_ext USING (vmid) WHERE vc_path LIKE('%" + $vm.ConfigOrg + "\\\\" + $vm.ConfigName + "%') AND name LIKE('%" + $vm.VMname + "');"
            $vmid = ExecuteMySQLScalar($query)
            if (!$vmid) {
                #Log ($query)
                Log-Error("Unable to find db id for Labs VM: " + $vm.ConfigName + "\" + $vm.VMname)
                Continue
            }
            
            $query = "INSERT INTO vm_nic (vmid, num, type) VALUES ($vmid, 0, 'Labs NAT') "
            $query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid);"
            ExecuteMySQLNonQuery($query)
                
            # Get vnicid from last query
            $vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
                
            # Find related IP(s)
            $query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($vmid, $vnicid, INET_ATON('" + $vm.VMextIP + "')) "
            $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" +  $vm.VMextIP + "');"
            ExecuteMySQLNonQuery($query)
            }
        }    
    }


# Post Amble =================================================================================================================================

if ($Major_Error) {
    if ($ErrorLog.Length) {
        if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=$Major_Error, warn=" + $ErrorLog.Length + " WHERE runid=$runid;")}
        Log("Script encountered " + $ErrorLog.Count + " minor errors and $Major_Error severe errors (VC connect fails)!!!")
    } else {
        if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=$Major_Error, warn=0 WHERE runid=$runid;")}
        Log("Script encountered $Major_Error severe errors (VC connect fails)!!!")
    }
} else {
    if ($ErrorLog.Length) {
        if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=$Major_Error, warn=" + $ErrorLog.Length + " WHERE runid=$runid;")}
        Log("Script encountered " + $ErrorLog.Count + " minor errors!")
    } else {
        if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=0, warn=0 WHERE runid=$runid;")}
        Log("Script encountered no errors.")
    }
}

DisconnectMySQL
Stop-Transcript