VI-DB db-base-data-sub-v2.8.ps1
Jump to navigation
Jump to search
<# ========================================================================================================================================
Database Base Data Getter - Sub-script
=========================================================================================================================================
Simon Strutt Apr 2011
=========================================================================================================================================
Version 2
- Initial creation !
Version 2.1 - July 2011
- Performance improvements (reduce's runtime by about 60%)
-- Improved VM Cluster get to use lookup table
-- Changed VM Guest OS get to use existing VM object
- Bugfix: Lab Manager VM's not being removed after deletion (include checking of Persistent ID)
Version 2.2
- Minor improvements to error messages
- Performance improvements
-- Caching of Get-VMHostNetworkAdapter during ESX info gather
- Added VMTools state and VM version
Version 2.3
- Added ESX NIC firmware version
Version 2.4
- Bugfix: VM's not in cluster don't get added to db (introduced in v2.1)
- Bugfix: "Couldn't find ESX ID for VM" error catch not working
- Added VM custom attributes
- Added vApps
Version 2.4.1
- Bugfix: vm2ip can get populated with incorrect IP when VM is shutdown
- Bugfix: vApps with no VM's not handled (causing invalid SQL statement, and so script crash)
Version 2.5
- Bugfix: Remove old VMDK's for VMs didn't work (SQL query incorrect)
- Bugfix: VM NICs with multiple IPs not handled correctly
- Improved error logging (DS inaccessible, VMs not in cluster)
- Added sev0 error log at completion to signal parent script that script exited cleanly
Version 2.6
- Bugfix: Networks not being removed from ESX's when they no longer exist / no longer configured on ESX
- Bugfix: Datastores not being removed from ESX's when they no longer exist / no longer configured on ESX
- Change VC last updated field update to end of run
- Prevent VM IP address being overwritten / blanked by 0.0.0.0
Version 2.7
- Bugfix: VM's showing with incorrect Cluster (affecting duplicate name VMs)
- Bugfix: Handle foreign (eg French) names for VM hard disk and network adapte
- Improved IP address handling (filter out non-IPv4, blank addresses)
- Get NAT's from Vyatta router
Version 2.8
- Bugfix: PS Exceptions thrown when determining network types
- Bugfix: Unable to get NIC number for some VI3 VM's (introduced by foreign name handling)
- Changed Vyatta NATing to so that NAT NIC has NwId that matches NAT'ed IP range
- Add datastore block size and canonical name
- Add ESX - LUN multipathing policy
TO DO
- Show ESX's in clusters with inconsistent datastores
- Add VM disk type (specifically RDMs)
- Add VM disk usage
- Add DRS groups
- Add VM Templates
- Improve removed VM clearup (vmdk, vm_nic)
- Add basic process perf stats
========================================================================================================================================#>
# Get/handle job parameters
$var = $Input.‘<>4__this’.Read()
$VC_name = $var[0]
$VC_vcid = $var[1]
$cred = $var[2]
$DBuser = $var[3]
$DBpass = $var[4]
$database = $var[5]
$MySQLHost = $var[6]
$runid = $var[7]
Set-Location $var[8]
# Include library files
. .\lib\Standard-v4.ps1
. .\lib\PowerCLI-helper-v1.1.ps1
. .\temp\DS-LUN-v4.ps1
. .\lib\getsnapshotsize-1.ps1
. .\lib\MySQL-v1.ps1
$start = Get-Date
$LogStamp = "Time" # Log timestamp format (Duration or Time (stamp))
Log "Started sub-script run at $start"
# Load VMware PS Snapin
Log("Loading VMware PowerShell Snapin...")
Add-PsSnapin *VMware*
$ScriptVer = [regex]::matches(($MyInvocation.MyCommand.Name), "(?<=v)[0-9]+(\.[0-9])+(?=.)")
Log ("Script version is $ScriptVer (" + (Get-PowerCLIversion).UserFriendlyVersion + " / Powershell " + (get-host).Version.ToString() + ")")
# Flags
$UpdateScriptRun = 1 # Update script runs log in database
$LogNonQueries = 1 # Non Query (insert/update etc) logging (gets forced if in debug mode)
$UpdateCL = 1 # Clusters
$UpdateESX = 1 # ESXs
$UpdateDS = 1 # Datastores
$UpdateNW = 1 # Networks
$UpdateVApp = 1 # vApps
$UpdateVM = 1 # Virtual Machines
$UpdateVyNAT = 1 # Vyatta NATs (requires $UpdateVM
$VyattaUser = "vi-db"
$VyattaPass = "syndrome"
if (!$runid) {
$UpdateScriptRun = 0
}
# Connect to MySQL database
$SQLconn = ConnectMySQL $DBuser $DBpass $MySQLHost $database
#if (!$UpdateCL -or !$UpdateESX -or !$UpdateDS -or !$UpdateNW -or !$UpdateVM) {
# $UpdateScriptRun = 0
# Log("Updating of script run info in db disabled (performing partial run)")
#}
function Log-Error($text, $vcid=$VC_vcid, $sev=3) {
$script:ErrorLog += $text
Log ("ERROR: " + $text)
Log ("Error count now " + $ErrorLog.Length)
if ($UpdateScriptRun) {
ExecuteMySQLNonQuery("INSERT INTO script_run_error (runid, sev, vcid, text) VALUES ($runid, $sev, $vcid, '" + (EscQuote $text) + "');")
}
}
function Log-Perf {
Log ("Perf CPU(sec): " + [Math]::Round($proc.cpu, 0) + ", Paged Mem (MB): " + [Math]::Round(($proc.pm/1024), 0) + ", WrkSet Mem (MB): " + [Math]::Round(($proc.ws/1024), 0))
}
# Connect to vCentre
try {
Log("Connecting to " + $VC_name)
$VCconn = Connect-VIServer -Server $VC_name -Credential $cred -WarningAction SilentlyContinue -ErrorAction "Stop"
} catch [VMware.VimAutomation.ViCore.Types.V1.ErrorHandling.InvalidLogin] {
Log("Unable to connect to vCentre, invalid logon error !!")
Log("Abandoning further script processing in order to prevent potential account lockout.")
Log-Error -text "vCentre invalid login error, script halt to prevent potential account lockout." -sev 2
$Major_Error = 99
Break
} catch {
Log-Error -text ("Unable to connect to vCentre - " + $_) -sev 2
$Major_Error += 1
Continue
}
$vcinfo = (Get-View ServiceInstance).Content.About
if ($vcinfo.Version -lt 4) {
$UpdateVApp = 0
Log("Updating of vApp info disabled (this is a v3 vCentre!)")
}
# Clusters ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateCL) {
# Get existing clusters from VC
$vcClusters = Get-Cluster
foreach ($cl in $vcClusters) {
# Add/update cluster
if ($cl.DrsEnabled) {
$query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsAutomationLevel + "', 1) "
$query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsAutomationLevel + "', exist=1;"
} else {
$query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsEnabled.GetHashCode() + "', 1) "
$query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsEnabled.GetHashCode() + "', exist=1;"
}
ExecuteMySQLNonQuery($query)
}
# Mark clusters that no longer exist
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid=$VC_vcid AND exist=1;")
foreach ($cl in $dbClusters) {
if (!($vcClusters |?{$_.Name -eq $cl.name})) {
if (!$cl.clid) {
Continue
}
Log ("HIDE: " + $cl.name)
ExecuteMySQLNonQuery("UPDATE cluster SET exist=0 WHERE clid=" + $cl.clid + ";")
}
}
}
# ESX --------------------------------------------------------------------------------------------------------------------------------
if ($UpdateESX) {
$vcESXs = Get-VMHost
# Get clusters and ESXs from db
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + " AND exist=1;")
# Add/update info in db
foreach ($esx in $vcESXs) {
$clid = $dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid
if (!$clid) {
$clid = "NULL"
}
$name = [regex]::matches($esx.Name, "(^\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b)|(\A(\w|-)+)")
$query = "INSERT INTO esx (vcid, clid, name, exist, ver, build, model, cpu_core, cpu_sock, mem, cpu_model) "
$query += "VALUES (" + $VC_vcid + ", $clid, '" + $name + "', 1"
$query += ", '" + $esx.Version + "', " + $esx.Build + ", '" + ($esx.Model -replace ' {2,}',' ') + "', " + $esx.NumCpu + ", " + $esx.ExtensionData.Hardware.CpuPkg.Count + ", " + $esx.MemoryTotalMB
$query += ", '" + $esx.ExtensionData.Summary.Hardware.CpuModel + "') ON DUPLICATE KEY UPDATE esxid=LAST_INSERT_ID(esxid), clid=$clid"
$query += ", exist=1, ver='" + $esx.Version + "', build=" + $esx.Build + ", model='" + ($esx.Model -replace ' {2,}',' ')
$query += "', cpu_core=" + $esx.NumCpu + ", cpu_sock=" + $esx.ExtensionData.Hardware.CpuPkg.Count + ", mem=" + $esx.MemoryTotalMB + ", cpu_model='" + $esx.ExtensionData.Summary.Hardware.CpuModel + "';"
ExecuteMySQLNonQuery($query)
# Get esxid from last query
$esxid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
#Debug($esx.Name.Split(".")[0] + " esxid is $esxid")
$query = "INSERT INTO esx_state (esxid, state) VALUES (" + $esxid + ", '" + $esx.ConnectionState + "') ON DUPLICATE KEY UPDATE state='" + $esx.ConnectionState + "';"
ExecuteMySQLNonQuery($query)
# Add/update info in extended db tables
if ($esx.ConnectionState.ToString() -notmatch "Connected" -and $esx.ConnectionState.ToString() -notmatch "Maintenance") {
Log-Error ("ESX $name not connected so unable to get NIC / HBA info")
Continue
}
# Store objects which get re-used for efficiency
$ESXview = Get-View -VIObject $ESX
$VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMHost $esx
# Extended data
$ip = ($VMHostNetworkAdapter | Where {$_.ManagementTrafficEnabled -eq "True" -or $_.DeviceName -like "vswif*" -or $_.Name -eq "vmk0"}).IP
$syslogSvr = $esx.ExtensionData.Config.Option.GetEnumerator() | ?{$_.Key -eq "Syslog.Remote.Hostname"} | Select -ExpandProperty Value
if ($ESXView.Hardware.BiosInfo) { # Works on some systems
$BiosVer = $ESXview.Hardware.BiosInfo.BiosVersion + " " + $ESXview.Hardware.BiosInfo.ReleaseDate.ToString("yyyy-MM-dd") # Need date for HP servers as they use same version no for diff versions!
} else {
$BiosVer = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*BIOS*"}).Name
$BiosVer = ([regex]::Matches($BiosVer, "[A-Z]\d{2} 20\d{2}-\d{2}-\d{2}"))[0].Value # HP regex to extract "A19 2010-09-30" for example
}
$dns1 = $esx.ExtensionData.Config.Network.DnsConfig.Address[0]
$dns2 = $esx.ExtensionData.Config.Network.DnsConfig.Address[1]
$query = "INSERT INTO esx_ext (esxid, ip, domain, dns_svr1, dns_svr2, syslog, bios_ver) VALUES ($esxid, INET_ATON('$ip'), '" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName
$query += "', INET_ATON('$dns1'), INET_ATON('$dns2'), '$syslogSvr', '$BiosVer') "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('$ip'), domain='" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName + "', dns_svr1=INET_ATON('$dns1'), "
$query += "dns_svr2=INET_ATON('$dns2'), syslog='$syslogSvr', bios_ver='$BiosVer';"
ExecuteMySQLNonQuery($query)
# NTP data
$idx=0
$ntp_svrs = $esx.ExtensionData.Config.DateTimeInfo.NtpConfig.Server
foreach ($ntp in $ntp_svrs) {
ExecuteMySQLNonQuery("INSERT INTO esx_ntp (esxid, idx, svr) VALUES ($esxid, $idx, '$ntp') ON DUPLICATE KEY UPDATE svr='$ntp';")
$idx += 1
}
# Remove NTP entires that no longer exist
$dbESXntps = ExecuteMySQLQuery("SELECT svr FROM esx_ntp WHERE esxid=$esxid;")
foreach ($dbESXntp in $dbESXntps) {
if (!$dbESXntp.svr -or ($ntp_svrs |?{$_ -eq $dbESXntp.svr})) {
Continue
}
Log("REMOVE: " + $esx.name + " " + $dbESXntp.svr)
ExecuteMySQLNonQuery("DELETE FROM esx_ntp WHERE esxid=$esxid AND svr='" + $dbESXntp.svr + "';")
}
# HBA data
$vmhbas = $esx.ExtensionData.Config.StorageDevice.HostBusAdapter | Where {$_.Key -like "*FibreChannel*"}
foreach ($vmhba in $vmhbas) {
$ver = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*" + $vmhba.Driver + "*"}).Name
$ver = ([regex]::Matches($ver, "(\b\d)(.*?)(?=\s)"))[0].Value
$query = "INSERT INTO esx_hba (esxid, device, model, driver, drv_ver) VALUES ($esxid, '" + $vmhba.Device + "', '" + $vmhba.Model + "', '" + $vmhba.Driver + "', '$ver') "
$query += "ON DUPLICATE KEY UPDATE device='" + $vmhba.Device + "', model='" + $vmhba.Model + "', driver='" + $vmhba.Driver + "', drv_ver='$ver';"
ExecuteMySQLNonQuery($query)
}
# Remove HBA's that no longer exist
$dbESXhbas = ExecuteMySQLQuery("SELECT device FROM esx_hba WHERE esxid=$esxid;")
foreach ($dbESXhba in $dbESXhbas) {
if (!$dbESXhba.device -or ($vmhbas |?{$_.Device -eq $dbESXhba.device})) {
Continue
}
Log("REMOVE: " + $esx.name + " " + $dbESXhba.device)
ExecuteMySQLNonQuery("DELETE FROM esx_hba WHERE esxid=$esxid AND device='" + $dbESXhba.device + "';")
}
# NIC data
$vmnics = $VMHostNetworkAdapter | Where {$_.Id -like "*.PhysicalNic*"}
$nicDevices = $esxView.Hardware.PciDevice | Where {$_.ClassId -eq 512}
foreach ($vmnic in $vmnics) {
$ver = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like $vmnic.ExtensionData.Driver + " driver*"} | Get-Unique).Name
$ver = ([regex]::Matches($ver, "(\b\d)(.*)(?=\s)"))[0].Value # Strips out any superfluous text (or rather, matches a version number)
$fware = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like $vmnic.ExtensionData.Driver + " device firmware*"} | Get-Unique).Name
$fware = ([regex]::Matches($fware, "(\b\d)(.*)(?=\s)"))[0].Value # Strips out any superfluous text (or rather, matches a version number)
$nicDev = $nicDevices | Where {$_.Id -eq $vmnic.ExtensionData.Pci} | Select VendorName, DeviceName
#Log ("Device : " + $vmnic.DeviceName)
#Log ("Speed : " + $vmnic.BitRatePerSec + " MB")
#Log ("Driver : " + $vmnic.ExtensionData.Driver)
#Log ("Version : " + )
#Log ("PCI : " + $vmnic.ExtensionData.Pci)
#Log ("Model : " + $nicDev.VendorName + " " + $nicDev.DeviceName)
$query = "INSERT INTO esx_nic (esxid, device, speed, model, driver, drv_ver, fware) VALUES ($esxid, '" + $vmnic.DeviceName + "', " + $vmnic.BitRatePerSec
$query += ", '" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', '" + $vmnic.ExtensionData.Driver + "', '$ver', '$fware') ON DUPLICATE KEY UPDATE "
$query += "speed=" + $vmnic.BitRatePerSec + ", model='" + $nicDev.VendorName + " " + $nicDev.DeviceName + "', driver='" + $vmnic.ExtensionData.Driver + "', drv_ver='$ver', fware='$fware';"
ExecuteMySQLNonQuery($query)
}
# Remove NIC's that no longer exist
$dbESXnics = ExecuteMySQLQuery("SELECT device FROM esx_nic WHERE esxid=$esxid;")
foreach ($dbESXnic in $dbESXnics) {
if (!$dbESXnic.device -or ($vmnics |?{$_.DeviceName -eq $dbESXnic.device})) {
Continue
}
Log("REMOVE: " + $esx.name + " " + $dbESXnic.device)
ExecuteMySQLNonQuery("DELETE FROM esx_nic WHERE esxid=$esxid AND device='" + $dbESXnic.device + "';")
}
}
# Mark ESX's that no longer exist
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($esx in $dbESXs) {
if (!$esx.esxid -or ($vcESXs |?{$_.Name.Split(".")[0] -eq $esx.name})) {
Continue
}
Log("HIDE: " + $esx.name)
ExecuteMySQLNonQuery("UPDATE esx SET exist=0 WHERE esxid=" + $esx.esxid + ";")
}
# Check for ESX's that have moved cluster
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name, COUNT(*) AS count, clid FROM esx WHERE vcid=" + $VC_vcid + " AND exist=1 GROUP BY name;")
foreach ($esx in $dbESXs) {
if ($esx.count -gt 1) {
Log("HIDE dupe: " + $esx.name)
# First esxid will be old, SELECT provides the first ID, so easy to remove
ExecuteMySQLNonQuery("UPDATE esx SET exist=0 WHERE esxid=" + $esx.esxid + ";")
}
}
}
# Datastores ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateDS) {
$vcDSs = Get-Datastore
# Get SAN model(s) and LUN ID's
if ($vcinfo.version -lt 4) {
Log("Getting v3 LUN info...")
$luns = Get-DS-LUNs-v3 (Get-VMHost)
} else {
Log("Getting v4 LUN info...")
$luns = Get-DS-LUNs-v4
}
Log("...got " + $luns.count + " LUNs")
foreach ($ds in $vcDSs) {
if (!$ds.Accessible) {
Log-Error ("Datastore is inaccessible/down: " + $ds.Name)
Continue
}
$lun = $luns |?{$_.Datastore -eq $ds.Name}
if (!$lun) {
Log-Error ("Couldn't find LUN ID for datastore " + $ds.Name)
Continue
}
# Workaround for occassional duplicate LUNs retruned by Get-DS-LUN
if ($lun.count) {
$lun = $lun[0]
}
if ($ds.ExtensionData.Info.Vmfs.Extent.Count -ne 1) {
$canonical = "NULL"
Log-Error ("Can't get canonical name for datastore " + $ds.Name + " (extents=" + $ds.ExtensionData.Info.Vmfs.Extent.Count + ")")
} else {
$canonical = "'" + $ds.ExtensionData.Info.Vmfs.Extent[0].DiskName + "'"
}
$query = "INSERT INTO ds (vcid, name, exist, size, used, san_model, lun, block, canonical) VALUES ('" + $VC_vcid + "', '" + $ds.Name + "', 1, " + $ds.CapacityMB + ", " + ($ds.CapacityMB - $ds.FreeSpaceMB)
$query += ", '" + $lun.Make + " " + $lun.Model + "', " + $lun.LUN + ", " + $ds.ExtensionData.Info.Vmfs.BlockSizeMB + ", $canonical) ON DUPLICATE KEY UPDATE "
$query += "exist=1, size=" + $ds.CapacityMB + ", used=" + ($ds.CapacityMB - $ds.FreeSpaceMB) + ", san_model='" + $lun.Make + " " + $lun.Model + "', lun=" + $lun.LUN + ", "
$query += "block=" + $ds.ExtensionData.Info.Vmfs.BlockSizeMB + ", canonical=$canonical;"
ExecuteMySQLNonQuery($query)
}
# Link ESX's to DS's
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
$dbDSs = ExecuteMySQLQuery("SELECT dsid, name, canonical FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($esx in $dbESXs) {
if (!$esx.esxid) {
Continue
}
$vcESXDSs = Get-Datastore -VMHost (Get-VMHost ($esx.name + "*"))
$vcESXLuns = Get-ScsiLun -VMHost (Get-VMHost ($esx.name + "*"))
foreach ($esxds in $vcESXDSs) {
# Match ESX DS name to db dsid
$dsid = $dbDSs |?{$_.name -eq $esxds.Name}
if (!$dsid.dsid) {
Log-Error ("Couldn't find " + $esx.name + " datastore " + $esxds.Name + " in db")
Continue
}
# Get path policy
if ($dsid.canonical) {
$mpath = $vcESXLuns |?{$_.CanonicalName -eq $dsid.canonical} | Select -ExpandProperty MultipathPolicy
if (!$mpath) {
Log-Error ("Couldn't find multipath policy for " + $esx.name + " datastore " + $esxds.Name)
ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid.dsid +");")
Continue
}
ExecuteMySQLNonQuery("INSERT INTO esx2ds (esxid, dsid, mpath) VALUES (" + $esx.esxid + ", " + $dsid.dsid +", '$mpath') ON DUPLICATE KEY UPDATE mpath='$mpath';")
} else {
ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid.dsid +");")
}
}
# Unlink datastores that are no longer configured on ESX
$dbESXDSs = ExecuteMySQLQuery("SELECT dsid, ds.name FROM esx2ds JOIN ds USING (dsid) WHERE esxid = " + $esx.esxid + ";")
foreach ($esxds in $dbESXDSs) {
if (!$esxds.nwid) {
Continue
}
if (!($vcESXDSs|?{$_.Name -eq $esxds.Name})) {
Log ("UNLINK: " + $esx.name + " Datastore " + $esxds.Name)
#Log ("DELETE FROM esx2ds WHERE esxid=" + $esx.esxid+ " AND dsid=" + $esxds.dsid + ";")
ExecuteMySQLNonQuery("DELETE FROM esx2ds WHERE esxid=" + $esx.esxid + " AND dsid=" + $esxds.dsid + ";")
}
}
}
# Mark datastores that no longer exist
$dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($ds in $dbDSs) {
if (!($vcDSs |?{$_.Name -eq $ds.name})) {
if (!$ds.dsid) {
Continue
}
Log ("HIDE: " + $ds.name)
ExecuteMySQLNonQuery("UPDATE ds SET exist=0 WHERE dsid=" + $ds.dsid + ";")
ExecuteMySQLNonQuery("DELETE FROM esx2ds WHERE dsid=" + $ds.dsid + ";")
}
}
}
# Networks ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateNW) {
# Get special network types
$MgmtNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.ManagementTrafficEnabled -eq "True"} | Select -Property PortGroupName -Unique
$vMotNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.VMotionEnabled -eq "True"} | Select -Property PortGroupName -Unique
$FTolNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.FaultToleranceLoggingEnabled -eq "True"} | Select -Property PortGroupName -Unique
# Get standard (old school) port groups
$vcNWs = Get-VirtualPortGroup -Standard
foreach ($nw in $vcNWs) {
if (!$nw.VLanId) {
$vlan = "NULL"
} else {
$vlan = $nw.VLanId
}
if ($MgmtNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_mgmt = 1} else {$is_mgmt = 0}
if ($vMotNW |?{$_.PortGroupName -eq $nw.Name}) {$is_vmk = 1} else {$is_vmk = 0}
if ($FTolNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_ft = 1} else {$is_ft = 0}
#$is_mgmt = [int][bool]($MgmtNWs |?{$_.PortGroupName -eq $nw.Name})
#$is_vmk = [int][bool]($vMotNWs |?{$_.PortGroupName -eq $nw.Name})
#$is_ft = [int][bool]($FTolNWs |?{$_.PortGroupName -eq $nw.Name})
$query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 0) "
$query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=0;"
ExecuteMySQLNonQuery($query)
}
if ($vcinfo.Version -ge 4) {
# Get distributed port groups
$vcNWs = Get-VirtualPortGroup -Distributed
foreach ($nw in $vcNWs) {
if ($nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId.GetType().IsArray) {
$vlan= 9999
} elseif (!$nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId) {
$vlan = "NULL"
} else {
$vlan = $nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId
}
if ($MgmtNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_mgmt = 1} else {$is_mgmt = 0}
if ($vMotNW |?{$_.PortGroupName -eq $nw.Name}) {$is_vmk = 1} else {$is_vmk = 0}
if ($FTolNWs |?{$_.PortGroupName -eq $nw.Name}) {$is_ft = 1} else {$is_ft = 0}
#$is_mgmt = [int][bool]($MgmtNWs |?{$_.PortGroupName -eq $nw.Name})
#$is_vmk = [int][bool]($vMotNWs |?{$_.PortGroupName -eq $nw.Name})
#$is_ft = [int][bool]($FTolNWs |?{$_.PortGroupName -eq $nw.Name})
$query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 1) "
$query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=1;"
ExecuteMySQLNonQuery($query)
}
} else {
Log("Skipping dvSwitch gather (pre vSphere vCentre)")
}
# Link ESX's to NW's
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
$dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($esx in $dbESXs) {
if (!$esx.esxid) {
Continue
}
$vcESXNWs = Get-VirtualPortGroup -VMHost (Get-VMHost ($esx.name + "*"))
foreach ($esxnw in $vcESXNWs) {
# Catch linking failure (prevents SQL query failure, allows script to continue)
$nwid = $dbNWs |?{$_.name -eq $esxnw.Name} | Select -ExpandProperty nwid
if (!$nwid) {
Log-Error ("Couldn't find network " + $esxnw.Name + " (vlan " + $esxnw.VLanId + ") in db")
Continue
}
ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2nw (esxid, nwid) VALUES (" + $esx.esxid + ", $nwid);")
}
# Unlink networks that are no longer configured on ESX
$dbESXNWs = ExecuteMySQLQuery("SELECT nwid, nw.name FROM esx2nw JOIN nw USING (nwid) WHERE esxid = " + $esx.esxid + ";")
foreach ($esxnw in $dbESXNWs) {
if (!$esxnw.nwid) {
Continue
}
if (!($vcESXNWs|?{$_.Name -eq $esxnw.Name})) {
Log ("UNLINK: " + $esx.name + " Network " + $esxnw.Name)
#Log ("DELETE FROM esx2nw WHERE esxid=" + $esx.esxid+ " AND nwid=" + $esxnw.nwid + ";")
ExecuteMySQLNonQuery("DELETE FROM esx2nw WHERE esxid=" + $esx.esxid + " AND nwid=" + $esxnw.nwid + ";")
}
}
}
# Mark networks that no longer exist
$vcNWs = Get-VirtualPortGroup
$dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($ds in $dbNWs) {
if (!($vcNWs |?{$_.Name -eq $nw.name})) {
if (!$nw.nwid) {
Continue
}
Log ("HIDE: " + $nw.name)
ExecuteMySQLNonQuery("UPDATE nw SET exist=0 WHERE nwid=" + $nw.nwid + ";")
ExecuteMySQLNonQuery("DELETE FROM esx2nw WHERE nwid=" + $nw.nwid + ";")
}
}
}
# vApps (create vapp) ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVApp) {
$vcVApps = Get-VApp
if ($vcVApps) {
# Create base vApp entry
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
foreach ($vApp in $vcVApps) {
$vappvcid = [regex]::replace($vApp.Id, "VirtualApp-resgroup-v", "")
$clid = $dbClusters |?{$_.name -eq (Get-Cluster -Id $vApp.ExtensionData.Owner)} | Select -ExpandProperty clid
$vcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vApp.ExtensionData.ParentFolder)), "\\", "\\")
if (!$clid) {
Log-Error ("Couldn't find cluster for vApp: " + $vApp.Name + " in db")
$clid = "NULL"
}
if ($vApp.ExtensionData.VAppConfig.Product[0].Name) {
$prod_name = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Name + "'"
} else {
$prod_name = "NULL"
}
if ($vApp.ExtensionData.VAppConfig.Product[0].Version) {
$prod_ver = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Version + "'"
} else {
$prod_ver = "NULL"
}
if ($vApp.ExtensionData.VAppConfig.Product[0].FullVersion) {
$prod_fver = "'" + $vApp.ExtensionData.VAppConfig.Product[0].FullVersion + "'"
} else {
$prod_fver = "NULL"
}
if ($vApp.ExtensionData.VAppConfig.Product[0].Vendor) {
$prod_vend = "'" + $vApp.ExtensionData.VAppConfig.Product[0].Vendor + "'"
} else {
$prod_vend = "NULL"
}
$query = "INSERT INTO vapp (vcid, vappvcid, clid, name, vc_path, status, exist, prod_name, prod_ver, prod_fver, prod_vend) VALUES ($VC_vcid, $vappvcid, $clid, '" + $vApp.Name + "', '$vcpath', '" + $vApp.Status
$query += "', 1, $prod_name, $prod_ver, $prod_fver, $prod_vend) ON DUPLICATE KEY UPDATE vappvcid=$vappvcid, clid=$clid, vc_path='$vcpath', status='" + $vApp.Status + "', exist=1, prod_name=$prod_name, "
$query += "prod_ver=$prod_ver, prod_fver=$prod_fver, prod_vend=$prod_vend;"
ExecuteMySQLNonQuery($query)
}
# Mark vApps that no longer exist
$dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid=$VC_vcid AND exist=1;")
foreach ($vapp in $dbVApps) {
if (!($vcVApps |?{$_.Name -eq $vapp.name})) {
if (!$vapp.vappid) {
Continue
}
Log ("HIDE: " + $vapp.name + " vApp")
ExecuteMySQLNonQuery("UPDATE vapp SET exist=0 WHERE vappid=" + $vapp.vappid + ";")
ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vappid=" + $vapp.vappid + ";")
}
}
} else {
Log ("No vApps found")
}
}
# Virtual Machines --------------------------------------------------------------------------------------------------------------------------
if ($UpdateVM) {
$vcVMs = Get-VM
$vcClusters = Get-Cluster
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
$dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid = " + $VC_vcid + " AND exist=1;")
$dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + ";")
$dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + ";")
$dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
$VyattaVM = @()
# Create cluster lookup hash
Log("Building VM2clid lookup hash...")
$VM2clid = @{}
foreach ($vcCluster in $vcClusters) {
$clid = $dbClusters |?{$_.name -eq $vcCluster.Name} | Select -ExpandProperty clid
$clVMs = Get-VM -Location $vcCluster
foreach ($vm in $clVMs) {
$VM2clid[$vm.Id] = $clid
}
}
Log("...completed")
# Add/update VM's in database
foreach ($vm in $vcVMs) {
# Catch linking failure (prevents SQL query failure, allows script to continue)
$vmvcid = [regex]::replace($vm.Id, "VirtualMachine-vm-", "")
# Version 1 - Takes ages
#$cluster = (Get-Cluster -VM $vm).Name
#$clid = $dbClusters |?{$_.name -eq $cluster} | Select -ExpandProperty clid
# version 2 - Takes about half the time, but still 2+ secs
#$cluster = (Get-View -ID (Get-View -ViewType HostSystem -Filter @{"Name"=$vm.VMHost.Name} -Property Parent).Parent -Property Name).Name
#$clid = $dbClusters |?{$_.name -eq $cluster} | Select -ExpandProperty clid
# Version 3 - Uses pre-built lookup hash
$clid = $VM2clid[$vm.Id]
#$vmGuest = Get-VMGuest -VM $vm
if (!$clid) {
# No cluster found for VM in lookup - check this is OK
$clust = Get-Cluster -VM $vm
if ($clust) {
Log-Error ("Couldn't find cluster for VM: " + $vm.Name + " in db lookup (" + $clust.name + ")")
}
$clid = "NULL"
}
$esxid = $dbESXs |?{$_.name -eq $vm.VMHost.Name.Split(".")[0]} | Select -ExpandProperty esxid
if ($esxid.Count -gt 1) {
Log-Error ("Multiple ESX IDs (" + $esxid + ") found for VM: " + $vm.Name + " in db")
Continue
}
if (!$esxid) {
Log-Error ("Couldn't find ESX " + $vm.VMHost.Name.Split(".")[0] + " for VM: " + $vm.Name + " in db")
Continue
}
if ($vm.PowerState -eq "PoweredOn") {
$on = 1
} else {
$on = 0
}
# Get VM's containing folder and/or vApp info
if (!$vm.FolderId) {
$vApp = Get-VApp -Id $vm.ExtensionData.ParentVApp
$vAppName = $vApp.Name
$vappid = $dbVApps |?{$_.name -eq $vAppName} | Select -ExpandProperty vappid
if (!$vappid) {
Log-Error ("No vApp found for VM: " + $vm.Name + " in db")
$vAppName = ""
$vappid = "NULL"
}
if (!$vApp) {
Log-Error ("No folder Id or vApp specified for VM: " + $vm.Name)
$vmvcpath = ""
} else {
$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vApp.ExtensionData.ParentFolder)), "\\", "\\")
}
} else {
$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
$vAppName = ""
$vappid = "NULL"
}
# Update base VM entry
$query = "INSERT INTO vm (uuid, vmvcid, pid, name, exist, is_on, vcid, clid, esxid, vappid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', $vmvcid, '" + $vm.PersistentId
$query += "', '" + $vm.Name + "', 1, $on, " + $VC_vcid + ", $clid, $esxid, $vappid, '" + $vm.ExtensionData.Guest.HostName + "') ON DUPLICATE KEY UPDATE vmid=LAST_INSERT_ID(vmid), "
$query += " vmvcid=$vmvcid, name='" + $vm.Name + "', exist=1, is_on=$on, clid=$clid, esxid=$esxid, vappid=$vappid, hostname='" + $vm.ExtensionData.Guest.HostName + "';"
ExecuteMySQLNonQuery($query)
# Get vmid from last query
$vmid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
# Update extended VM entry
$vmxpath = $vm.ExtensionData.Config.Files.VmPathName
$vmtools = $vm.ExtensionData.Guest.ToolsStatus.ToString()
$vmscsi = ($vm.ExtensionData.Config.Hardware.Device | where {$_.DeviceInfo.Label -match "SCSI Controller"}).DeviceInfo.Summary
$vm_ver = [regex]::replace($vm.Version.ToString(), "v", "")
$vmnotes = [regex]::replace($vm.Notes, "(\s?\bvRanger.*Repository \[.*\]\s?)|(\[vRanger.*Host \[.*\]\.\]\s?)", "")
$vmnotes = [regex]::replace($vmnotes, "'", "\'")
$vmnotes = [regex]::replace($vmnotes, "`n", "\\n")
$query = "INSERT INTO vm_ext (vmid, cpu, mem, vapp, vc_path, vmx_path, tools, scsi_hw, vm_ver, notes) VALUES ($vmid, " + $vm.NumCpu + ", " + $vm.MemoryMB + ", '$vAppName"
$query += "', '$vmvcpath" + "', '$vmxpath', '$vmtools', '$vmscsi', '$vm_ver', '$vmnotes') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB + ", vapp='$vAppName"
$query += "', vc_path='$vmvcpath', vmx_path='$vmxpath', tools='$vmtools', scsi_hw='$vmscsi', vm_ver='$vm_ver', notes='$vmnotes';"
ExecuteMySQLNonQuery($query)
$vcNICs = Get-NetworkAdapter -VM $vm #(VI3 doesn't return Network Adapter x info)
#$vcNICs = $vm.Guest.Nics
# Add NICs
foreach ($nic in $vcNICs) {
$nwid = $dbNWs |?{$_.name -eq $nic.NetworkName} | Select -ExpandProperty nwid
if (!$nwid) {
if ($nic.extensiondata.Connectable.Status -eq "untried") {
Log ("Ignoring " + $vm.Name + " - " + $nic.Name + " (assumed to be LabManager service VM)")
} else {
Log-Error ("Couldn't find network " + $nic.NetworkName + "(" + $nic.Name + ") for VM: " + $vm.Name + " in db")
}
Continue
}
# Replaced in v2.7 with more generic (international) version below
#$nwno = [regex]::replace($nic.Name, "Network (?i:A)dapter ", "")
#if (!([regex]::matches($nwno, "\d").Count)) {
# if ($vm.NetworkAdapters.Count -eq 1) {
# $nwno = 1
# } else {
# Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name)
# Continue
# }
#}
$res = [regex]::matches($nic.Name, "\d")
if ($res.count -gt 1) {
Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name + " - too many numbers found")
Continue
} elseif ($res.count -lt 1) {
if ($vm.NetworkAdapters.Count -eq 1) {
$nwno = 1
} else {
Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name + " - no number found")
Continue
}
} else {
$nwno = $res[0].Value
}
$query = "INSERT INTO vm_nic (vmid, num, nwid, type) VALUES ($vmid, $nwno, $nwid, '" + $nic.Type + "') "
$query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid), nwid=$nwid, type='" + $nic.Type + "';"
ExecuteMySQLNonQuery($query)
# Get vnicid from last query
$vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
# Find related IP(s)
try {
# Failsafe - workaround for bug where $ips still populated from previous iteration and doesn't get updated
if ($ips) {
Clear-Variable ips
}
if ($vcinfo.Version -lt 4) {
#if ($vm.Guest.nics[0].NetworkName) {
# Works for VI3 hosted guests
$ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -Property IPAddress
} else {
# Works for VI4 hosted guests
$ips = $vm.Guest.Nics |?{$_.Device.Name -eq $nic.Name} | Select -Property IPAddress
}
if (!$ips) {
Log-Error ("Couldn't match NICs to find IP for VM: " + $vm.Name + " NIC: " + $nic.Name + " in data from VC")
Continue
}
foreach ($ip in $ips.IPAddress) {
if (!$ip) {
Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " has no IP address in vCentre")
Continue
}
if (!([regex]::matches($ip, "^\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b"))[0].Success) {
Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " IP: $ip is not a valid IPv4 address")
Continue
}
if ($ip -eq "0.0.0.0") {
Log ("Ignoring " + $vm.Name + "'s IP of " + $ip)
Continue
}
$query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($vmid, $vnicid, INET_ATON('$ip')) "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('$ip');"
ExecuteMySQLNonQuery($query)
}
} catch {
Log-Error("Unexpected error when trying to match NICs to find IP for VM: " + $vm.Name + " NIC: " + $nic.Name + " [" + $_.Exception.Message + "]")
}
}
# Remove old NICs
$dbNICs = ExecuteMySQLQuery("SELECT vnicid, num FROM vm_nic WHERE vmid=$vmid;")
foreach ($nic in $dbNICs) {
if (!$nic.vnicid) {
Continue
}
# Catch where num of NIC's is 1 (regex fails as NIC is called "Virtual Ethernet Adapter"
if (($vcNICs -and !$vcNICs.count) -and ($dbNICs.count -eq 2)) {
Log ("VM: " + $vm.Name + " NIC count of one matches db and vc (workaround)")
Continue
}
#if (!($vcNICs |?{[regex]::replace($_.Name, "Network (?i:A)dapter ", "") -eq $nic.num})) {
if (!($vcNICs |?{([regex]::matches($_.Name, "\d"))[0].Value -eq $nic.num})) {
Log ("DELETE: " + $vm.name + " NIC num " + $nic.num)
ExecuteMySQLNonQuery("DELETE FROM vm_nic WHERE vnicid=" + $nic.vnicid + ";")
ExecuteMySQLNonQuery("DELETE FROM vm2ip WHERE vnicid=" + $nic.vnicid + ";")
}
}
# Add VMDK record(s)
foreach ($vmdk in $vm.HardDisks) {
# Create VMDK record
$dsid = $dbDSs |?{$_.name -eq (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value)} | Select -ExpandProperty dsid
if (!$dsid) {
Log-Error ("Couldn't find datastore " + (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value) + " for VM: " + $vm.Name + " in db")
Continue
}
#$dkno = [regex]::replace($vmdk.Name, "Hard (?i:D)isk ", "")
$dkno = ([regex]::matches($vmdk.Name, "\d"))[0].Value
$dksize = [math]::Round(($vmdk.CapacityKB / 1024), 0)
if ($vmdk.StorageFormat -eq "Thin") {
$dkthin = 1
} else {
$dkthin = 0
}
$query = "INSERT INTO vmdk (dsid, vmid, num, size, thin, path) VALUES ($dsid, $vmid, $dkno, $dksize, $dkthin"
$query += ", '" + $vmdk.Filename + "') ON DUPLICATE KEY UPDATE dsid=$dsid, size=$dksize, thin=$dkthin, path='" + $vmdk.Filename + "';"
ExecuteMySQLNonQuery($query)
}
# Remove old VMDK's
$dbVMDKs = ExecuteMySQLQuery("SELECT dkid, num FROM vmdk WHERE vmid=$vmid;")
foreach ($vmdk in $dbVMDKs) {
#if (!($vm.HardDisks |?{[regex]::replace($_.Name, "Hard (?i:D)isk ", "") -eq $vmdk.num})) {
if (!($vm.HardDisks |?{([regex]::matches($_.Name, "\d"))[0].value -eq $vmdk.num})) {
if (!$vmdk.dkid) {
Continue
}
Log ("DELETE: " + $vm.name + " VMDK num " + $vmdk.num)
ExecuteMySQLNonQuery("DELETE FROM vmdk WHERE dkid=" + $vmdk.dkid + ";")
}
}
# Work out OSID if VM is up
if ($vm.PowerState -eq "PoweredOn") {
#$osname = (Get-VMGuest -VM $vm).OSFullName
$osname = $vm.ExtensionData.Config.GuestFullName
if (!$osname) {
Log-Error ("Couldn't update OS for VM: " + $vm.Name + " in db")
Continue
}
$osid = $dbOSs |?{$_.guest_name -eq $osname} | Select -ExpandProperty osid
if (!$osid) {
# No existing db record for OS, add a new one
ExecuteMySQLNonQuery("INSERT INTO os (guest_name) VALUES ('$osname');")
$osid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
$dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
}
ExecuteMySQLNonQuery("UPDATE vm_ext SET osid=$osid WHERE vmid=$vmid;")
}
# Get VM's custom attributes
$Attribs = Get-Annotation -Entity $vm
foreach ($attrib in $Attribs) {
if ($attrib.Value.Length) {
ExecuteMySQLNonQuery("INSERT into vm_attrib (vmid, name, data) VALUES ($vmid, '" + $attrib.Name + "', '" + $attrib.Value + "') ON DUPLICATE KEY UPDATE data='" + $attrib.Value + "';")
} else {
ExecuteMySQLNonQuery("INSERT into vm_attrib (vmid, name, data) VALUES ($vmid, '" + $attrib.Name + "', NULL) ON DUPLICATE KEY UPDATE data=NULL;")
}
}
# Detect if VM is Vyatta router
if ($vm.ExtensionData.Guest.Disk | Where {$_.DiskPath -eq "/opt/vyatta/etc/config"}) {
if ($vm.PowerState -ne "PoweredOn") {
Log("NAT router: " + $router.Name + " not powered on, skipping")
} else {
$router = "" | Select Name, Net1addr, Net1id, Net1name, Net2id, Net2name
$router.Name = $vm.Name
$RoNics = ExecuteMySQLQuery("SELECT num, nw.nwid, nw.name, CONVERT(INET_NTOA(MIN(ip)), CHAR) AS ip FROM vm_nic JOIN nw USING (nwid) JOIN vm2ip USING (vnicid) where vm_nic.vmid=$vmid GROUP BY num;")
foreach ($nic in $RoNics) {
if ($nic.num -eq 1) {
$router.Net1addr = $nic.ip
$router.Net1id = $nic.nwid
$router.Net1name = $nic.name
} elseif ($nic.num -eq 2) {
$router.Net2id = $nic.nwid
$router.Net2name = $nic.name
}
}
Log("NAT router: " + $router.Name + " (" + $router.Net1addr + ") [" + $router.Net1id + "] " + $router.Net1name + " -> [" + $router.Net2id + "] " + $router.Net2name)
$VyattaVM += $router
}
}
}
# Mark VM's that no longer exist
Log("Checking for VM's that no longer exist")
$dbVMs = ExecuteMySQLQuery("SELECT vmid, uuid, pid, vmvcid, name FROM vm WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($vm in $dbVMs) {
if ($vcVMs |?{($_.ExtensionData.Config.Uuid -eq $vm.uuid) -and ($_.PersistentId -eq $vm.pid)}) {
#Log ("Found " + $vm.vmid + " - " + $vm.name + " (" + $vm.uuid + ")")
} else {
if (!$vm.vmid) {
Continue
}
Log ("HIDE: " + $vm.vmid + " - " + $vm.name + " (UUID: " + $vm.uuid + " PersistID: " + $vm.pid + ")")
ExecuteMySQLNonQuery("UPDATE vm SET exist=0 WHERE vmid=" + $vm.vmid + ";")
}
}
# Snapshots - add/update, then remove old
Log ("Get snapshots from VC...")
#$vcSnaps = $vcVMs | Get-Snapshot <--- Get-Snapshot doesn't return accurate MB used info
# CalculateVMSnapshotsSizeMB returns property VirtualMachineID, Get-Snapshot returns VMId (swap below when reverting to Get-Snapshot)
$vcSnaps = @()
foreach ($vm in $vcVMs) {
$Snaps = CalculateVMSnapshotsSizeMB -VM $vm
if ($Snaps) {
$vcSnaps = $vcSnaps + $Snaps
}
}
if (!$vcSnaps) {
Log ("No snapshots found, removing any for this VC in db")
ExecuteMySQLNonQuery("DELETE FROM snap WHERE vcid=" + $VC_vcid + ";")
} else {
foreach ($snap in $vcSnaps) {
# Create/update entry (must convert size into integer)
$sid = [regex]::replace($snap.Id, "VirtualMachineSnapshot-snapshot-", "")
if (!$snap.ParentSnapshotId) {
$psid = "NULL"
} else {
$psid = [regex]::replace($snap.ParentSnapshotId, "VirtualMachineSnapshot-snapshot-", "")
}
$vmid = $dbVMs |?{$_.vmvcid -eq [regex]::replace($snap.VirtualMachineID, "VirtualMachine-vm-", "")} | Select -ExpandProperty vmid
if (!$vmid) {
Log-Error ("Couldn't find vmid for snapshot (" + $snap.Id + ", " + $snap.VirtualMachineID+ ")")
Continue
}
if (!$snap.Quiesced) { # Not returned by CalculateVMSnapshotsSizeMB
$quiesced = "NULL"
} else {
if ($snap.Quiesced -eq "True") {
$quiesced = 1
} else {
$quiesced = 0
}
}
if (!$snap.Created) { # CalculateVMSnapshotsSizeMB returns CreateTime not Created
$created = $snap.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")
} else {
$created = $snap.Created.ToString("yyyy-MM-dd HH:mm:ss")
}
if ($snap.PowerState -eq "PoweredOn") {
$on = 1
} else {
$on = 1
}
if ($snap.Name.Contains("Created by vRanger")) {
$vrange = 1
} else {
$vrange = 0
}
$query = "INSERT into snap (vmid, sid, vcid, name, descr, created, quiesced, vrange, vm_on, size, parent_sid) VALUES ($vmid, $sid, " + $VC_vcid + ", '" + (EscQuote $snap.Name)
$query += "', '" + (EscQuote $snap.Description) + "', '$created', $quiesced, $vrange, $on, " + [math]::Round($snap.SizeMB, 0)
$query += ", $psid) ON DUPLICATE KEY UPDATE name='" + (EscQuote $snap.Name) + "', descr='" + (EscQuote $snap.Description) + "', size=" + [math]::Round($snap.SizeMB, 0) + ";"
ExecuteMySQLNonQuery($query)
}
# Remove old snapshots
$dbSnaps = ExecuteMySQLQuery("SELECT sid FROM snap WHERE vcid=" + $VC_vcid + ";")
foreach ($snap in $dbSnaps) {
if (!$snap.sid) {
Continue
}
if (!($vcSnaps |?{$_.Id -eq ("VirtualMachineSnapshot-snapshot-" + $snap.sid)})) {
Log ("REMOVE: VirtualMachineSnapshot-snapshot-" + $snap.sid)
ExecuteMySQLNonQuery("DELETE FROM snap WHERE sid=" + $snap.sid + " AND vcid= " + $VC_vcid + ";")
}
}
}
}
# vApps (update VMs) -------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVApp) {
if ($vcVApps) {
# Create base vApp entry
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + ";")
foreach ($vApp in $vcVApps) {
$vappvcid = [regex]::replace($vApp.Id, "VirtualApp-resgroup-v", "")
$vappid = ExecuteMySQLScalar("SELECT vappid FROM vapp WHERE vappvcid=$vappvcid AND vcid=$VC_vcid;")
# Update vApp VM list
if ($vApp.ExtensionData.VAppConfig.EntityConfig) {
foreach ($vappvm in $vApp.ExtensionData.VAppConfig.EntityConfig) {
$vmvcid = [regex]::replace($vappvm.Key.ToString(), "VirtualMachine-vm-", "")
$vmid = ExecuteMySQLScalar("SELECT vmid FROM vm WHERE vcid=$VC_vcid AND vmvcid=$vmvcid;")
if (!$vmid) {
Log-Error("Failed to get vmid for " + $vappvm.Tag + " in vApp " + $vApp.Name)
Continue
}
$query = "INSERT INTO vapp_vm (vappid, vmid, vmvcid, ord, start_dly, start_wait, stop_dly, stop_act) VALUES ($vappid, $vmid, $vmvcid, " + $vappvm.StartOrder + ", " + $vappvm.StartDelay
$query += ", " + $vappvm.WaitingForGuest.GetHashCode() + ", " + $vappvm.StopDelay + ", '" + $vappvm.StopAction + "') ON DUPLICATE KEY UPDATE vmvcid=$vmvcid, ord=" + $vappvm.StartOrder
$query += ", start_dly=" + $vappvm.StartDelay + ", start_wait=" + $vappvm.WaitingForGuest.GetHashCode() +", stop_dly=" + $vappvm.StopDelay + ", stop_act='" + $vappvm.StopAction + "';"
ExecuteMySQLNonQuery($query)
}
}
# Remove VMs on longer in vApp
$dbVAppsVMs = ExecuteMySQLQuery("SELECT vmid, vmvcid FROM vapp_vm WHERE vappid=$vappid;")
foreach ($vappvm in $dbVAppsVMs) {
if (!($vApp.ExtensionData.VAppConfig.EntityConfig |?{[regex]::replace($_.Key.ToString(), "VirtualMachine-vm-", "") -eq $vappvm.vmvcid})) {
if (!$vappvm.vmid) {
Continue
}
Log ("DELETE: vmid " + $vappvm.vmid + " from vApp " + $vapp.name)
ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vmid=" + $vappvm.vmid + " AND vappid=$vappid;")
}
}
}
# Mark vApps that no longer exist
$dbVApps = ExecuteMySQLQuery("SELECT vappid, name FROM vapp WHERE vcid=$VC_vcid AND exist=1;")
foreach ($vapp in $dbVApps) {
if (!($vcVApps |?{$_.Name -eq $vapp.name})) {
if (!$vapp.vappid) {
Continue
}
Log ("HIDE: " + $vapp.name + " vApp")
ExecuteMySQLNonQuery("UPDATE vapp SET exist=0 WHERE vappid=" + $vapp.vappid + ";")
ExecuteMySQLNonQuery("DELETE FROM vapp_vm WHERE vappid=" + $vapp.vappid + ";")
}
}
}
}
# Vyatta NATs ---------------------------------------------------------------------------------------------------------------------------------
if ($UpdateVyNAT) {
if ($VyattaVM) {
# Load SSH library
. .\lib\Renci-SSH-v1.ps1
# $router = "" | Select Name, Net1addr, Net1id, Net1name, Net2id, Net2name
foreach ($router in $VyattaVM) {
Log("Get NATs from " + $router.Name + "...")
# Connect to router and get NAT rules
try {
$SshClient = New-SshSession -server $router.Net1addr -user $VyattaUser -pass $VyattaPass
$SshCommand = New-SshCommand -SshClient $SshClient -Command "exec vbash -i -c 'show nat rules'"
$NatText = $SshCommand.Result.Split("`n")
$SshCommand.Dispose()
$SshClient.Disconnect()
$SshClient.Dispose()
} catch {
Log-Error("SSH fail " + $router.Name + ": " + $_)
Continue
}
# Extract NATs from returned data
$nats = @()
foreach ($line in $NatText) {
if (!$line.length) {
Continue
}
#Write-Host ("Parsing line [$line]")
$text = ([regex]::matches($line, "^\d{2,4}\s+(SRC|DST)\s+eth\d\s+(s|d)addr\s\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b\sto\s\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b"))[0].Value
$text = [regex]::replace($text, "\s{2,}", " ")
if ($text.length) {
$nat = "" | Select RealIP, NatIP
$words = $text.split(" ")
if ($words[1] -eq "DST") {
$nat.RealIP = $words[6]
$nat.NatIP = $words[4]
} elseif ($words[1] -eq "SRC") {
$nat.RealIP = $words[4]
$nat.NatIP = $words[6]
} else {
#Write-Host "Ignored invalid line [$text]"
Continue
}
#Write-Host ("Adding " + $nat.RealIP + " - " + $nat.NatIP)
$nats += $nat
}
}
$nats = $nats | Sort-Object -Property RealIP -Unique
# Get list of VM's on router's inside network
$query = "SELECT vm_nic.vmid, CONVERT(INET_NTOA(MIN(ip)), CHAR) AS ip FROM vm_nic JOIN vm USING (vmid) JOIN vm2ip USING (vnicid) WHERE nwid=" + $router.Net2id + " AND exist=1 GROUP BY vmid;"
$InsideVMs = ExecuteMySQLQuery($query)
# Match up NATs with VMs and add translated address to db
foreach ($nat in $nats) {
$NATedVmid = $InsideVMs |?{$_.ip -eq $nat.RealIP} | Select -ExpandProperty vmid
if (!$NATedVmid) {
Log ("No VM found for NAT " + $nat.RealIP + " -> " + $nat.NatIP)
Continue
}
if ($NATedVmid.length -gt 1) {
Log-Error ("Too many VM's found for NAT " + $nat.RealIP + " -> " + $nat.NatIP + ", VMID's $NATedVmid")
Continue
}
# Create dummy VM NIC
ExecuteMySQLNonQuery("INSERT INTO vm_nic (vmid, num, nwid, type) VALUES ($NATedVmid, 0, " + $router.Net1id + ", 'Vyatta NAT') ON DUPLICATE KEY UPDATE nwid=" + $router.Net1id + ", vnicid=LAST_INSERT_ID(vnicid);")
# Get vnicid from last query
$vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
$query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($NATedVmid, $vnicid, INET_ATON('" + $nat.NatIP + "')) "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $nat.NatIP + "');"
ExecuteMySQLNonQuery($query)
}
}
} else {
Log("No Vyatta VM's found to process for NATs")
}
}
# Sanity checks -------------------------------------------------------------------------------------------------------------------------------
Log ("Perfoming sanity checks for " + $VC_name)
$SanityOK = 1
if ($UpdateCL) {
$dbCLs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM cluster WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($vcClusters -and -not $vcClusters.Count) {
$vcClusterCount = 1
} else {
$vcClusterCount = $vcClusters.Count
}
if ($dbCLs -ne $vcClusterCount) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of Clusters [VC: " + $vcClusterCount + " DB: " + $dbCLs + "]")
$SanityOK = 0
}
}
if ($UpdateESX) {
$dbESXs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM esx WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($dbESXs -ne $vcESXs.Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of ESXs [VC: " + $vcESXs.Count + " DB: " + $dbESXs + "]")
$SanityOK = 0
}
}
if ($UpdateVM) {
$dbVMs = ExecuteMySQLQuery("SELECT COUNT(*) AS total, SUM(IF(is_on=1,1,0)) AS is_on FROM vm WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($dbVMs[1].total -ne $vcVMs.Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of VMs [VC: " + $vcVMs.Count + " DB: " + $dbVMs[1].total + "]")
$SanityOK = 0
}
if ($dbVMs[1].is_on -ne ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of powered on VMs [VC: " + ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count + " DB: " + $dbVMs[1].is_on + "]")
$SanityOK = 0
}
}
if ($SanityOK) {
Log ("Sanity checks completed, no errors found")
} else {
Log-Error -text ("Sanity check problems found with data for " + $VC_name) -sev 2
}
# Add finished record to db so parent script knows all completed (didn't bomb out)
Log-Error -text ($VC_name + " - completed") -sev 0
if ($UpdateScriptRun) {
# Update VC info
ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=$VC_vcid;")
}
Log("Diconnecting from " + $VC_name)
Disconnect-VIServer -Server $VCconn -Confirm:$false
Log("Disconnecting from database")
DisconnectMySQL
Log("Elvis has left the building")