Virtual Infrastructure DB Project
Jump to navigation
Jump to search
This started life as small script to collect VM info from 5 or 6 vCentre's (that were a mix of v2.5 and v4, and weren't linked together), so that VM's could be easily found without having to search through each one. There are essentially 3 components to it...
- Data collection scripts (run from a Windows desktop)
- A daily PowerCLI script that collects data from all vCenters (and updates the database)
- An intra-day PowerCLI script (say every 15mins) that collects basic cluster status data (and updates the database)
- MySQL database (run on an Ubuntu VM)
- PHP front end (delivered by Apache running on same Ubuntu VM)
Database
CREATE DATABASE vi;
# VC table - this is the only table that must be manually populated
CREATE TABLE vc (vcid TINYINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32), site VARCHAR(3), ver VARCHAR(7), build INT UNSIGNED, last_pass DATETIME, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vcid), UNIQUE KEY (name));
# Cluster table
CREATE TABLE cluster (clid TINYINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, ha BOOL, drs VARCHAR(18), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid), UNIQUE KEY (vcid, name));
# VM tables
CREATE TABLE vm (vmid INT UNSIGNED AUTO_INCREMENT, uuid VARCHAR(37), vmvcid SMALLINT UNSIGNED, pid VARCHAR(37), name VARCHAR(32), exist BOOL, is_on BOOL, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, esxid SMALLINT UNSIGNED, hostname VARCHAR(255), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid), UNIQUE KEY (uuid, pid), UNIQUE KEY (vmvcid, vcid));
CREATE TABLE vm_ext (vmid INT UNSIGNED, cpu TINYINT UNSIGNED, mem INT UNSIGNED, vc_path VARCHAR(512), vmx_path VARCHAR(512), osid SMALLINT UNSIGNED, scsi_hw VARCHAR(16), notes VARCHAR(512), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid));
# ESX tables
CREATE TABLE esx (esxid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, state VARCHAR(16), ver VARCHAR(7), build INT UNSIGNED, model VARCHAR(32), cpu_core TINYINT UNSIGNED, cpu_sock TINYINT UNSIGNED, mem INT UNSIGNED COMMENT 'MB', cpu_model VARCHAR(128), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid), UNIQUE KEY (vcid, clid, name));
CREATE TABLE esx_state (esxid SMALLINT UNSIGNED, state VARCHAR(16), status VARCHAR(6), cpu_pc TINYINT, mem_pc TINYINT, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid));
CREATE TABLE esx2ds (esxid SMALLINT UNSIGNED, dsid SMALLINT UNSIGNED, PRIMARY KEY (esxid, dsid));
CREATE TABLE esx2nw (esxid SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, PRIMARY KEY (esxid, nwid));
# Datastore and VMDK tables
CREATE TABLE ds (dsid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, size INT UNSIGNED, used INT UNSIGNED, san_model VARCHAR(32), lun TINYINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dsid), UNIQUE KEY (vcid, name));
CREATE TABLE vmdk (dkid INT UNSIGNED AUTO_INCREMENT, dsid SMALLINT UNSIGNED, vmid INT UNSIGNED, num SMALLINT UNSIGNED, size INT UNSIGNED, thin BOOL, path VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dkid), UNIQUE KEY (vmid, num));
# Networking tables
CREATE TABLE nw (nwid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(64), exist BOOL, vlan SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nwid), UNIQUE KEY (vcid, name));
CREATE TABLE vm_nic (vnicid INT UNSIGNED AUTO_INCREMENT, vmid INT UNSIGNED, num SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, type VARCHAR(16), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vnicid), UNIQUE KEY (vmid, num));
CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
# Other tables
CREATE TABLE os (osid TINYINT UNSIGNED AUTO_INCREMENT, guest_name VARCHAR(128), short_name VARCHAR(32), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (osid), UNIQUE KEY (guest_name));
CREATE TABLE script_run (runid INT UNSIGNED AUTO_INCREMENT, start DATETIME, end DATETIME, version VARCHAR(8), error INT UNSIGNED, warn INT UNSIGNED, PRIMARY KEY (runid));
CREATE TABLE cluster_stats (clid TINYINT UNSIGNED, dated DATE, esxs TINYINT UNSIGNED, vms_exist SMALLINT UNSIGNED, vms_on SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid, dated));
CREATE TABLE snap (vmid INT UNSIGNED, sid INT UNSIGNED, vcid TINYINT UNSIGNED, name VARCHAR(256), descr VARCHAR(1024), created DATETIME, quiesced BOOL, vm_on BOOL, size MEDIUMINT UNSIGNED, parent_sid INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, sid), UNIQUE KEY (vcid, sid));
# Eventing
CREATE DEFINER=`root`@`192.168.196.1` PROCEDURE `update_cluster_stats`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE clust_id INT;
DECLARE clust CURSOR FOR SELECT clid FROM cluster;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN clust;
read_loop: LOOP
FETCH clust INTO clust_id;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO cluster_stats (clid, dated, esxs, vms_exist, vms_on)
SELECT clust_id, NOW(),
(SELECT COUNT(*) FROM esx WHERE clid=clust_id AND exist=1) AS esxs,
COUNT(*) AS vms_exist, SUM(IF(is_on=1,1,0)) AS vms_on
FROM vm WHERE clid=clust_id AND exist=1;
END LOOP;
CLOSE clust;
END
CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();
Data Collection
Main Daily Script
# =========================================================================================================================================
# Database Base Data Getter
# =========================================================================================================================================
# Simon Strutt Jan 2011
# =========================================================================================================================================
#
# Version 1
# - Initial creation
#
# ===========================================================================================================================================
$start = Get-Date
$UserFile = "User.fil"
$PassFile = "Pass.fil" # Encrypted file to store password in
# Include library files
. .\lib\Standard.ps1
. .\lib\PowerCLI-helper-v1.1.ps1
. .\temp\DS-LUN-v4.ps1
# DB connection stuff...
$user = 'powercli'
$pass = 'powercli'
$database = 'vi'
$MySQLHost = '192.168.10.128'
$LabMgrSvr = "lbmgr-svr-a" # Lab Manager (optional)
$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
$UpdateCL = 1 # Clusters
$UpdateESX = 1 # ESXs
$UpdateDS = 1 # Datastores
$UpdateNW = 1 # Networks
$UpdateVM = 1 # Virtual Machines
$UpdateLM = 1 # Lab Manager (update VM external IPs)
Start-Transcript -Path db-base-data.log
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
}
}
# =============================================================================================
# 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();")
$query = "INSERT INTO esx_state (esxid, state) VALUES (" + $esxid + ", '" + $esx.ConnectionState + "') ON DUPLICATE KEY UPDATE state='" + $esx.ConnectionState + "';"
ExecuteMySQLNonQuery($query)
}
# 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 (!($vcESXs |?{$_.Name.Split(".")[0] -eq $esx.name})) {
if (!$esx.esxid) {
Continue
}
Log("HIDE: " + $esx.name)
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)
$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 (!$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, pid, name, exist, is_on, vcid, clid, esxid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', '" + $vm.PersistentId + "', '" + $vm.Name
$query += "', 1, " + $on + ", " + $vc.vcid + ", " + $clid + ", " + $esxid + ", '" + $vmGuest.HostName + "') ON DUPLICATE KEY UPDATE vmid=LAST_INSERT_ID(vmid), name='" + $vm.Name
$query += "', 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 -ExpandProperty IPAddress
if (!$ips) {
Log-Error ("Couldn't find IP(s) for VM: " + $vm.Name + " NIC: " + $nic.Name + " from VC")
Continue
}
foreach ($ip in $ips) {
$query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES (" + $vmid + ", " + $vnicid + ", INET_ATON('" + $ip + "')) "
$query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $ip + "');"
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
$vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
#$vmvcpath = Get-FolderPath (Get-Folder -Id $vm.FolderId)
#$vmxpath = [regex]::replace($vm.ExtensionData.Config.Files.VmPathName, "\", "\\")
$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, 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 + ";")
}
}
}
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
Intra-Day Status
# ==========================================================
# ESX Status updater
# ==========================================================
# Simon Strutt Feb 2011
# ==========================================================
#
# Version 1
# - Initial creation
#
# Version 2 - Feb 2011
# - Script now manages intra-day checks, expects to started each AM
# - Added log rollover
#
# Version 3 - Feb 2011
# - Allow for ESX state data move to esx_state db table
# - Add % used to data collection
# - Add $DoSingleRun to bypass scheduling
#
# ==========================================================
$start = Get-Date
$UserFile = "User.fil"
$PassFile = "Pass.fil" # Encrypted file to store password in
# DB connection stuff...
$user = 'powercli'
$pass = 'powercli'
$database = 'vi'
$MySQLHost = '192.168.10.1'
# Scheduler stuff...
$DoSingleRun = 0 # Ignore scheduling and just run once
$IntervalMins = 15 # Should fit into an hour exactly
$End = "18:50" # Time of day that script should cease (should be just after last required run time)
$CheckThrottle = 250 # Idle throttle / time check interval (msec)
# Include library files
. .\lib\Standard.ps1
# 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
}
}
# Check function =================================================================================
function Perform-ESX-Check {
# Disconnect any existing VI Server sessions
if ($DefaultVIServers.Count) {
Log("Disconnect existing vCentre server connections...")
Disconnect-VIServer -Server * -Force -Confirm:$false
}
foreach ($vc in $VCs) {
# Check its a valid record 1st
if (!$vc.vcid) {
Continue
}
# Connect to vCentre
try {
Log("Checking " + $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
}
$dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
foreach ($esx in $dbESXs) {
if (!$esx.esxid) {
Continue
}
$esxobj = Get-VMHost ($esx.name + "*")
$query = "UPDATE esx_state SET state='" + $esxobj.ConnectionState + "', status='" + $esxobj.ExtensionData.OverallStatus + "', cpu_pc=" + [Math]::Round(($esxobj.CpuUsageMhz / $esxobj.CpuTotalMhz * 100))
$query += ", mem_pc=" + [Math]::Round(($esxobj.MemoryUsageMB / $esxobj.MemoryTotalMB * 100)) + " WHERE esxid=" + $esx.esxid + ";"
ExecuteMySQLNonQuery($query)
}
ExecuteMySQLNonQuery("UPDATE vc SET last_esx_check=NOW() WHERE vcid=" + $vc.vcid + ";")
Disconnect-VIServer -Server $VCconn -Confirm:$false
}
}
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))
}
# MAIN SCRIPT ====================================================================================
# Initial prep -----------------------------------------------------------------------------------
if ($DoSingleRun) {
Start-Transcript -Path db-base-check-test.log -Append
} else {
Rename-Item -Path db-esx-check.log -NewName db-esx-check-1.log -Force
Start-Transcript -Path db-base-check.log -Append
}
Log "Started script run at $start"
# Get process for this script
$proc = [System.Diagnostics.Process]::GetCurrentProcess()
# Check scheduler variables
if ((60 % $IntervalMins) -ne 0) {
Log "Interval error - $IntervalMins mins doesn't fit into an hour!"
Exit
}
try {
$EndTime = Get-Date $End
} catch {
Log "Invalid end time: $End hrs"
Log $_.Exception.GetType().FullName
Log $_.Exception.Message
Exit
}
# Set dummy last run time (aligned to nice start time), and next run time
$LastRunTime = Get-Date
$offset = ($IntervalMins + ($LastRunTime.Minute % $IntervalMins))
$LastRunTime = $LastRunTime.AddMinutes(-$offset)
$LastRunTime = $LastRunTime.AddSeconds(-$LastRunTime.Second)
$NextRunTime = $LastRunTime.AddMinutes($IntervalMins)
#Write-Host "Offset is " $offset
Log ("Last runtime is " + $LastRunTime)
Log ("Next runtime is " + $NextRunTime)
Log ("Script cease at " + $EndTime)
# Connect to MySQL database
$SQLconn = ConnectMySQL $user $pass $MySQLHost $database
# Get list of VC's (checks the SQL connection is in good working order as well)
$VCs = ExecuteMySQLQuery("SELECT vcid, name, site FROM vc;")
# 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)
# Main loop -------------------------------------------------------------------------------------
if ($DoSingleRun) {
Log ("Doing single run...")
Perform-ESX-Check
} else {
While (1) {
if ($NextRunTime -lt (Get-Date)) {
Log-Perf
Log ("Starting run at " + (Get-Date))
Perform-ESX-Check
Log ("Completed run at " + (Get-Date))
$LastRunTime = $LastRunTime.AddMinutes($IntervalMins)
$NextRunTime = $LastRunTime.AddMinutes($IntervalMins)
if ($NextRunTime -lt (Get-Date)) {
Log "WARNING: Next run is going to be late!"
}
}
if ($EndTime -lt (Get-Date)) {
Log ("Script ending at " + (Get-Date))
Break
}
Start-Sleep -Milliseconds $CheckThrottle
}
}
DisconnectMySQL
Stop-Transcript