VI-DB db-base-data-v1.2.ps1

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