VI-DB db-base-data-sub-v2.2.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 - in prog
- Improve removed VM clearup (vmdk, vm_nic
TO DO
- Add basic process perf stats
- Improve efficiency in VM data gathering
========================================================================================================================================#>
# Get/handle job parameters
$var = $Input.‘<>4__this’.Read()
$VC_name = $var[0]
$VC_vcid = $var[1]
$cred = $var[2]
$DBuser = $var[3]
$DBpass = $var[4]
$database = $var[5]
$MySQLHost = $var[6]
$runid = $var[7]
Set-Location $var[8]
# Include library files
. .\lib\Standard-v4.ps1
. .\lib\PowerCLI-helper-v1.1.ps1
. .\temp\DS-LUN-v4.ps1
. .\lib\getsnapshotsize-1.ps1
. .\lib\MySQL-v1.ps1
$start = Get-Date
$LogStamp = "Time" # Log timestamp format (Duration or Time (stamp))
# Load VMware PS Snapin
Log("Loading VMware PowerShell Snapin...")
Add-PsSnapin *VMware*
# Flags
$UpdateScriptRun = 1 # Update script runs log in database
$LogNonQueries = 1 # Non Query (insert/update etc) logging (gets forced if in debug mode)
$UpdateCL = 1 # Clusters
$UpdateESX = 1 # ESXs
$UpdateDS = 1 # Datastores
$UpdateNW = 1 # Networks
$UpdateVM = 1 # Virtual Machines
$UpdateLM = 1 # Lab Manager (update VM external IPs)
if (!$runid) {
$UpdateScriptRun = 0
}
Log "Started sub-script run at $start"
# Connect to MySQL database
$SQLconn = ConnectMySQL $DBuser $DBpass $MySQLHost $database
if (!$UpdateCL -or !$UpdateESX -or !$UpdateDS -or !$UpdateNW -or !$UpdateVM) {
$UpdateScriptRun = 0
Log("Updating of script run info in db disabled (performing partial run)")
}
function Log-Error($text, $vcid=$VC_vcid, $sev=3) {
$script:ErrorLog += $text
Log ("ERROR: " + $text)
Log ("Error count now " + $ErrorLog.Length)
if ($UpdateScriptRun) {
ExecuteMySQLNonQuery("INSERT INTO script_run_error (runid, sev, vcid, text) VALUES ($runid, $sev, $vcid, '" + (EscQuote $text) + "');")
}
}
function Log-Perf {
Log ("Perf CPU(sec): " + [Math]::Round($proc.cpu, 0) + ", Paged Mem (MB): " + [Math]::Round(($proc.pm/1024), 0) + ", WrkSet Mem (MB): " + [Math]::Round(($proc.ws/1024), 0))
}
# Connect to vCentre
try {
Log("Connecting to " + $VC_name)
$VCconn = Connect-VIServer -Server $VC_name -Credential $cred -WarningAction SilentlyContinue -ErrorAction "Stop"
} catch [VMware.VimAutomation.ViCore.Types.V1.ErrorHandling.InvalidLogin] {
Log("Unable to connect to vCentre, invalid logon error !!")
Log("Abandoning further script processing in order to prevent potential account lockout.")
Log-Error -text "vCentre invalid login error, script halt to prevent potential account lockout." -sev 2
$Major_Error = 99
Break
} catch {
Log-Error -text ("Unable to connect to vCentre - " + $_) -sev 2
$Major_Error += 1
Continue
}
if ($UpdateScriptRun) {
# Get VC info
$vcinfo = (Get-View ServiceInstance).Content.About
ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=$VC_vcid;")
}
# Clusters ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateCL) {
# Get existing clusters from VC
$vcClusters = Get-Cluster
foreach ($cl in $vcClusters) {
# Add/update cluster
if ($cl.DrsEnabled) {
$query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsAutomationLevel + "', 1) "
$query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsAutomationLevel + "', exist=1;"
} else {
$query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('$VC_vcid', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsEnabled.GetHashCode() + "', 1) "
$query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsEnabled.GetHashCode() + "', exist=1;"
}
ExecuteMySQLNonQuery($query)
}
# Mark clusters that no longer exist
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid=$VC_vcid AND exist=1;")
foreach ($cl in $dbClusters) {
if (!($vcClusters |?{$_.Name -eq $cl.name})) {
if (!$cl.clid) {
Continue
}
Log ("HIDE: " + $cl.name)
ExecuteMySQLNonQuery("UPDATE cluster SET exist=0 WHERE clid=" + $cl.clid + ";")
}
}
}
# ESX --------------------------------------------------------------------------------------------------------------------------------
if ($UpdateESX) {
$vcESXs = Get-VMHost
# Get clusters and ESXs from db
$dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $VC_vcid + " AND exist=1;")
# Add/update info in db
foreach ($esx in $vcESXs) {
$clid = $dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid
if (!$clid) {
$clid = "NULL"
}
$name = [regex]::matches($esx.Name, "(^\b((25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\.){3}(25[0-5]|2[0-4]\d|[01]\d\d|\d?\d)\b)|(\A(\w|-)+)")
$query = "INSERT INTO esx (vcid, clid, name, exist, ver, build, model, cpu_core, cpu_sock, mem, cpu_model) "
$query += "VALUES (" + $VC_vcid + ", $clid, '" + $name + "', 1"
$query += ", '" + $esx.Version + "', " + $esx.Build + ", '" + ($esx.Model -replace ' {2,}',' ') + "', " + $esx.NumCpu + ", " + $esx.ExtensionData.Hardware.CpuPkg.Count + ", " + $esx.MemoryTotalMB
$query += ", '" + $esx.ExtensionData.Summary.Hardware.CpuModel + "') ON DUPLICATE KEY UPDATE esxid=LAST_INSERT_ID(esxid), clid=$clid"
$query += ", exist=1, ver='" + $esx.Version + "', build=" + $esx.Build + ", model='" + ($esx.Model -replace ' {2,}',' ')
$query += "', cpu_core=" + $esx.NumCpu + ", cpu_sock=" + $esx.ExtensionData.Hardware.CpuPkg.Count + ", mem=" + $esx.MemoryTotalMB + ", cpu_model='" + $esx.ExtensionData.Summary.Hardware.CpuModel + "';"
ExecuteMySQLNonQuery($query)
# Get esxid from last query
$esxid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
#Debug($esx.Name.Split(".")[0] + " esxid is $esxid")
$query = "INSERT INTO esx_state (esxid, state) VALUES (" + $esxid + ", '" + $esx.ConnectionState + "') ON DUPLICATE KEY UPDATE state='" + $esx.ConnectionState + "';"
ExecuteMySQLNonQuery($query)
# Add/update info in extended db tables
if ($esx.ConnectionState.ToString() -notmatch "Connected" -and $esx.ConnectionState.ToString() -notmatch "Maintenance") {
Log-Error ("ESX not connected so unable to get NIC / HBA info")
Continue
}
# Store objects which get re-used for efficiency
$ESXview = Get-View -VIObject $ESX
$VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMHost $esx
# Extended data
$ip = ($VMHostNetworkAdapter | Where {$_.ManagementTrafficEnabled -eq "True" -or $_.DeviceName -like "vswif*" -or $_.Name -eq "vmk0"}).IP
$syslogSvr = $esx.ExtensionData.Config.Option.GetEnumerator() | ?{$_.Key -eq "Syslog.Remote.Hostname"} | Select -ExpandProperty Value
if ($ESXView.Hardware.BiosInfo) { # Works on some systems
$BiosVer = $ESXview.Hardware.BiosInfo.BiosVersion + " " + $ESXview.Hardware.BiosInfo.ReleaseDate.ToString("yyyy-MM-dd") # Need date for HP servers as they use same version no for diff versions!
} else {
$BiosVer = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*BIOS*"}).Name
$BiosVer = ([regex]::Matches($BiosVer, "[A-Z]\d{2} 20\d{2}-\d{2}-\d{2}"))[0].Value # HP regex to extract "A19 2010-09-30" for example
}
$dns1 = $esx.ExtensionData.Config.Network.DnsConfig.Address[0]
$dns2 = $esx.ExtensionData.Config.Network.DnsConfig.Address[1]
$query = "INSERT INTO esx_ext (esxid, ip, domain, dns_svr1, dns_svr2, syslog, bios_ver) VALUES ($esxid, INET_ATON('$ip'), '" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName
$query += "', INET_ATON('$dns1'), INET_ATON('$dns2'), '$syslogSvr', '$BiosVer') "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('$ip'), domain='" + $esx.ExtensionData.Config.Network.DnsConfig.DomainName + "', dns_svr1=INET_ATON('$dns1'), "
$query += "dns_svr2=INET_ATON('$dns2'), syslog='$syslogSvr', bios_ver='$BiosVer';"
ExecuteMySQLNonQuery($query)
# NTP data
$idx=0
$ntp_svrs = $esx.ExtensionData.Config.DateTimeInfo.NtpConfig.Server
foreach ($ntp in $ntp_svrs) {
ExecuteMySQLNonQuery("INSERT INTO esx_ntp (esxid, idx, svr) VALUES ($esxid, $idx, '$ntp') ON DUPLICATE KEY UPDATE svr='$ntp';")
$idx += 1
}
# Remove NTP entires that no longer exist
$dbESXntps = ExecuteMySQLQuery("SELECT svr FROM esx_ntp WHERE esxid=$esxid;")
foreach ($dbESXntp in $dbESXntps) {
if (!$dbESXntp.svr -or ($ntp_svrs |?{$_ -eq $dbESXntp.svr})) {
Continue
}
Log("REMOVE: " + $esx.name + " " + $dbESXntp.svr)
ExecuteMySQLNonQuery("DELETE FROM esx_ntp WHERE esxid=$esxid AND svr='" + $dbESXntp.svr + "';")
}
# HBA data
$vmhbas = $esx.ExtensionData.Config.StorageDevice.HostBusAdapter | Where {$_.Key -like "*FibreChannel*"}
foreach ($vmhba in $vmhbas) {
$ver = ($ESXview.Runtime.HealthSystemRuntime.SystemHealthInfo.NumericSensorInfo | Where {$_.Name -like "*" + $vmhba.Driver + "*"}).Name
$ver = ([regex]::Matches($ver, "(\b\d)(.*?)(?=\s)"))[0].Value
$query = "INSERT INTO esx_hba (esxid, device, model, driver, drv_ver) VALUES ($esxid, '" + $vmhba.Device + "', '" + $vmhba.Model + "', '" + $vmhba.Driver + "', '$ver') "
$query += "ON DUPLICATE KEY UPDATE device='" + $vmhba.Device + "', model='" + $vmhba.Model + "', driver='" + $vmhba.Driver + "', drv_ver='$ver';"
ExecuteMySQLNonQuery($query)
}
# Remove HBA's that no longer exist
$dbESXhbas = ExecuteMySQLQuery("SELECT device FROM esx_hba WHERE esxid=$esxid;")
foreach ($dbESXhba in $dbESXhbas) {
if (!$dbESXhba.device -or ($vmhbas |?{$_.Device -eq $dbESXhba.device})) {
Continue
}
Log("REMOVE: " + $esx.name + " " + $dbESXhba.device)
ExecuteMySQLNonQuery("DELETE FROM esx_hba WHERE esxid=$esxid AND device='" + $dbESXhba.device + "';")
}
# NIC data
$vmnics = $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)
$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 " + $esx.name + " datastore " + $esxdc.Name + " in db")
Continue
}
ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid +");")
}
}
# Mark datastores that no longer exist
$dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($ds in $dbDSs) {
if (!($vcDSs |?{$_.Name -eq $ds.name})) {
if (!$ds.dsid) {
Continue
}
Log ("HIDE: " + $ds.name)
ExecuteMySQLNonQuery("UPDATE ds SET exist=0 WHERE dsid=" + $ds.dsid + ";")
}
}
}
# Networks ------------------------------------------------------------------------------------------------------------------------------
if ($UpdateNW) {
# Get special network types
$MgmtNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.ManagementTrafficEnabled -eq "True"} | Select -Property PortGroupName -Unique
$vMotNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.VMotionEnabled -eq "True"} | Select -Property PortGroupName -Unique
$FTolNWs = Get-VMHostNetworkAdapter -VMKernel -Console | Where {$_.FaultToleranceLoggingEnabled -eq "True"} | Select -Property PortGroupName -Unique
# Get standard (old school) port groups
$vcNWs = Get-VirtualPortGroup -Standard
foreach ($nw in $vcNWs) {
if (!$nw.VLanId) {
$vlan = "NULL"
} else {
$vlan = $nw.VLanId
}
$is_mgmt = [int][bool]($MgmtNWs |?{$_.PortGroupName -eq $nw.Name})
$is_vmk = [int][bool]($vMotNWs |?{$_.PortGroupName -eq $nw.Name})
$is_ft = [int][bool]($FTolNWs |?{$_.PortGroupName -eq $nw.Name})
$query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 0) "
$query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=0;"
ExecuteMySQLNonQuery($query)
}
if ($vcinfo.Version -ge 4) {
# Get distributed port groups
$vcNWs = Get-VirtualPortGroup -Distributed
foreach ($nw in $vcNWs) {
if ($nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId.GetType().IsArray) {
$vlan= 9999
} elseif (!$nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId) {
$vlan = "NULL"
} else {
$vlan = $nw.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId
}
[int][bool]$is_mgmt = $MgmtNWs |?{$_.PortGroupName -eq $nw.Name}
[int][bool]$is_vmk = $vMotNWs |?{$_.PortGroupName -eq $nw.Name}
[int][bool]$is_ft = $FTolNWs |?{$_.PortGroupName -eq $nw.Name}
$query = "INSERT INTO nw (vcid, name, exist, vlan, is_mgmt, is_vmk, is_ft, is_dv) VALUES ('" + $VC_vcid + "', '" + $nw.Name + "', 1, $vlan, $is_mgmt, $is_vmk, $is_ft, 1) "
$query += "ON DUPLICATE KEY UPDATE exist=1, vlan=$vlan, is_mgmt=$is_mgmt, is_vmk=$is_vmk, is_ft=$is_ft, is_dv=1;"
ExecuteMySQLNonQuery($query)
}
} else {
Log("Skipping dvSwitch gather (pre vSphere vCentre)")
}
# Link ESX's to NW's
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $VC_vcid + " AND exist=1;")
$dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($esx in $dbESXs) {
if (!$esx.esxid) {
Continue
}
$vcESXNWs = Get-VirtualPortGroup -VMHost (Get-VMHost ($esx.name + "*"))
foreach ($esxnw in $vcESXNWs) {
# Catch linking failure (prevents SQL query failure, allows script to continue)
$nwid = $dbNWs |?{$_.name -eq $esxnw.Name} | Select -ExpandProperty nwid
if (!$nwid) {
Log-Error ("Couldn't find network " + $esxnw.Name + " (vlan " + $esxnw.VLanId + ") in db")
Continue
}
ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2nw (esxid, nwid) VALUES (" + $esx.esxid + ", $nwid);")
}
}
# Mark networks that no longer exist
$vcNWs = Get-VirtualPortGroup
$dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $VC_vcid + " AND exist=1;")
foreach ($ds in $dbNWs) {
if (!($vcNWs |?{$_.Name -eq $nw.name})) {
if (!$nw.nwid) {
Continue
}
Log ("HIDE: " + $nw.name)
ExecuteMySQLNonQuery("UPDATE nw SET exist=0 WHERE nwid=" + $nw.nwid + ";")
}
}
}
# Virtual Machines --------------------------------------------------------------------------------------------------------------------------
if ($UpdateVM) {
$vcVMs = Get-VM
$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;")
$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;")
# 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.Name] = $clid
}
}
Log("...completed")
# 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-", "")
# 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.Name]
#$vmGuest = Get-VMGuest -VM $vm
if (!$clid) {
Log-Error ("Couldn't find cluster " + $cluster + " for VM: " + $vm.Name + " in db")
Continue
}
$esxid = $dbESXs |?{$_.name -eq $vm.VMHost.Name.Split(".")[0]} | Select -ExpandProperty esxid
if ($esxid.Count -gt 1) {
Log-Error ("Multiple ESX IDs (" + $esxid + ") found for VM: " + $vm.Name + " in db")
Continue
}
if (!$clid) {
Log-Error ("Couldn't find ESX " + $vm.VMHost.Name.Split(".")[0] + " for VM: " + $vm.Name + " in db")
Continue
}
if ($vm.PowerState -eq "PoweredOn") {
$on = 1
} else {
$on = 0
}
$query = "INSERT INTO vm (uuid, vmvcid, pid, name, exist, is_on, vcid, clid, esxid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', $vmvcid, '" + $vm.PersistentId
$query += "', '" + $vm.Name + "', 1, $on, " + $VC_vcid + ", $clid, $esxid, '" + $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, hostname='" + $vm.ExtensionData.Guest.HostName + "';"
ExecuteMySQLNonQuery($query)
# Get vmid from last query
$vmid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
$vcNICs = Get-NetworkAdapter -VM $vm #(VI3 doesn't return Network Adapter x info)
#$vcNICs = $vm.Guest.Nics
# Add NICs
foreach ($nic in $vcNICs) {
$nwid = $dbNWs |?{$_.name -eq $nic.NetworkName} | Select -ExpandProperty nwid
if (!$nwid) {
Log-Error ("Couldn't find network " + $nic.Name + " for VM: " + $vm.Name + " in db")
Continue
}
$nwno = [regex]::replace($nic.Name, "Network (?i:A)dapter ", "")
if (!([regex]::matches($nwno, "\d").Count)) {
if ($vm.NetworkAdapters.Count -eq 1) {
$nwno = 1
} else {
Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name)
Continue
}
}
$query = "INSERT INTO vm_nic (vmid, num, nwid, type) VALUES ($vmid, $nwno, $nwid, '" + $nic.Type + "') "
$query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid), nwid=$nwid, type='" + $nic.Type + "';"
ExecuteMySQLNonQuery($query)
# Get vnicid from last query
$vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
# Find related IP(s)
if ($vm.Guest.nics[0].NetworkName) {
# Works for VI3 hosted guests
$ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -Property NetworkName, IPAddress
} else {
# Works for VI4 hosted guests
$ips = $vm.Guest.Nics |?{$_.Device.Name -eq $nic.Name} | Select -Property Device, IPAddress
}
if (!$ips) {
Log-Error ("Couldn't match NICs to find IP for VM: " + $vm.Name + " NIC: " + $nic.Name + " in data from VC")
Continue
}
foreach ($ip in $ips) {
if (!$ip.IPAddress) {
Log ("VM: " + $vm.Name + " NIC: " + $nic.Name + " has no IP address in vCentre")
Continue
}
$query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES ($vmid, $vnicid, INET_ATON('" + $ip.IPAddress + "')) "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $ip.IPAddress + "');"
ExecuteMySQLNonQuery($query)
}
}
# Remove old NICs
$dbNICs = ExecuteMySQLQuery("SELECT vnicid, num FROM vm_nic WHERE vmid=$vmid;")
foreach ($nic in $dbNICs) {
if (!$nic.vnicid) {
Continue
}
# Catch where num of NIC's is 1 (regex fails as NIC is called "Virtual Ethernet Adapter"
if (($vcNICs -and !$vcNICs.count) -and ($dbNICs.count -eq 2)) {
Log ("VM: " + $vm.Name + " NIC count of one matches db and vc (workaround)")
Continue
}
if (!($vcNICs |?{[regex]::replace($_.Name, "Network (?i:A)dapter ", "") -eq $nic.num})) {
Log ("DELETE: " + $vm.name + " NIC num " + $nic.num)
ExecuteMySQLNonQuery("DELETE FROM vm_nic WHERE vnicid=" + $nic.vnicid + ";")
ExecuteMySQLNonQuery("DELETE FROM vm2ip WHERE vnicid=" + $nic.vnicid + ";")
}
}
# Add VMDK record(s)
foreach ($vmdk in $vm.HardDisks) {
# Create VMDK record
$dsid = $dbDSs |?{$_.name -eq (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value)} | Select -ExpandProperty dsid
if (!$dsid) {
Log-Error ("Couldn't find datastore " + (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value) + " for VM: " + $vm.Name + " in db")
Continue
}
$dkno = [regex]::replace($vmdk.Name, "Hard (?i:D)isk ", "")
$dksize = [math]::Round(($vmdk.CapacityKB / 1024), 0)
if ($vmdk.StorageFormat -eq "Thin") {
$dkthin = 1
} else {
$dkthin = 0
}
$query = "INSERT INTO vmdk (dsid, vmid, num, size, thin, path) VALUES ($dsid, $vmid, $dkno, $dksize, $dkthin"
$query += ", '" + $vmdk.Filename + "') ON DUPLICATE KEY UPDATE dsid=$dsid, size=$dksize, thin=$dkthin, path='" + $vmdk.Filename + "';"
ExecuteMySQLNonQuery($query)
}
# Remove old VMDK's
$dbVMDKs = ExecuteMySQLQuery("SELECT dkid, num FROM vmdk WHERE vmid=$vmid;")
foreach ($vmdk in $dbVMDKs) {
if (!($vm.HardDisks |?{[regex]::replace($_.Name, "Hard (?i:D)isk ", "") -eq $vmdk.num})) {
if (!$vmdk.dkid) {
Continue
}
Log ("DELETE: " + $vm.name + " VMDK num " + $vmdk.num)
ExecuteMySQLNonQuery("DELETE FROM vmdk WHERE dkid=" + $vmdk.num + ";")
}
}
# Add/update extended VM info
if (!$vm.FolderId) {
$vApp = Get-VApp -Id $vm.ExtensionData.ParentVApp
if (!$vApp) {
Log-Error ("No folder Id or vApp specified for VM: " + $vm.Name)
$vmvcpath = ""
} else {
$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vApp.ExtensionData.ParentFolder)), "\\", "\\")
}
} else {
$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
}
$vmxpath = $vm.ExtensionData.Config.Files.VmPathName
$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 + ", '" + $vapp.ExtensionData.Name
$query += "', '$vmvcpath" + "', '$vmxpath', '$vmtools', '$vmscsi', '$vm_ver', '$vmnotes') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB + ", vapp='" + $vapp.ExtensionData.Name
$query += "', vc_path='$vmvcpath', vmx_path='$vmxpath', tools='$vmtools', scsi_hw='$vmscsi', vm_ver='$vm_ver', notes='$vmnotes';"
ExecuteMySQLNonQuery($query)
# 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;")
}
}
# 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 + ";")
}
}
}
}
# Sanity checks
Log ("Perfoming sanity checks for " + $VC_name)
$SanityOK = 1
if ($UpdateCL) {
$dbCLs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM cluster WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($vcClusters -and -not $vcClusters.Count) {
$vcClusterCount = 1
} else {
$vcClusterCount = $vcClusters.Count
}
if ($dbCLs -ne $vcClusterCount) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of Clusters [VC: " + $vcClusterCount + " DB: " + $dbCLs + "]")
$SanityOK = 0
}
}
if ($UpdateESX) {
$dbESXs = ExecuteMySQLScalar("SELECT COUNT(*) AS total FROM esx WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($dbESXs -ne $vcESXs.Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of ESXs [VC: " + $vcESXs.Count + " DB: " + $dbESXs + "]")
$SanityOK = 0
}
}
if ($UpdateVM) {
$dbVMs = ExecuteMySQLQuery("SELECT COUNT(*) AS total, SUM(IF(is_on=1,1,0)) AS is_on FROM vm WHERE vcid=" + $VC_vcid + " AND exist=1;")
if ($dbVMs[1].total -ne $vcVMs.Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of VMs [VC: " + $vcVMs.Count + " DB: " + $dbVMs[1].total + "]")
$SanityOK = 0
}
if ($dbVMs[1].is_on -ne ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count) {
Log-Error("Sanity check fail " + $VC_name + " - inconsistent number of powered on VMs [VC: " + ($vcVMs | Where {$_.PowerState -eq "PoweredOn"}).Count + " DB: " + $dbVMs[1].is_on + "]")
$SanityOK = 0
}
}
if ($SanityOK) {
Log ("Sanity checks completed, no errors found")
} else {
Log-Error -text ("Sanity check problems found with data for " + $VC_name) -sev 2
}
Log("Diconnecting from " + $VC_name)
Disconnect-VIServer -Server $VCconn -Confirm:$false
Log("Disconnecting from database")
DisconnectMySQL
Log("Elvis has left the building")