VI-DB db-esx-check-v6.ps1
Jump to navigation
Jump to search
# ==========================================================
# 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
#
# Version 4 - Mar 2011
# - Improved ESX search string
# - Handle ESX being removed from vCentre
#
# Version 5 - Apr 2011
# - Update ESX cluster membership
#
# Version 6 - Sep 2011
# - Bugfix: Log rollover didn't work
# - Performance optimisation - changed data gather to use single Get-View
#
# ==========================================================
$Debug = 0
$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.196.128'
$LogNonQueries = 0
$LogStamp = "Time"
# Scheduler stuff...
$DoSingleRun = 0 # Ignore scheduling and just run once
$IntervalMins = 5 # 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)
if ($Debug) {
$database = 'vi_test'
$LogNonQueries = 1
}
# Include library files
. .\lib\Standard-v4.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
}
# Get list of clusters in order to be able to update ESX cluster membership on the fly
$dbClusters = ExecuteMySQLQuery("SELECT vcid, clid, name FROM cluster WHERE exist=1;")
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
}
if ($Debug) {Log "Connected to vCenter"}
$dbESXs = ExecuteMySQLQuery("SELECT esxid, clid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
$vcESXs = Get-View -ViewType HostSystem -Property Name, Parent, Runtime.ConnectionState, Summary.Runtime.InMaintenanceMode, OverallStatus, Summary.Hardware.CpuMhz, Summary.Hardware.NumCpuCores, Summary.Hardware.MemorySize, Summary.QuickStats.OverallCpuUsage, Summary.QuickStats.OverallMemoryUsage
if ($Debug) {Log ("Got ESX View data")}
$vcClusters = Get-View -ViewType ClusterComputeResource -Property Name
if ($Debug) {Log ("Got Cluster View data, processsing...")}
#$dbESXs.gettype()
#$dbESXs
#$dbESXs[1].esxid.gettype()
foreach ($esx in $vcESXs) {
# Match to ESX db record
$esxName = [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|-)+)")
$dbESX = $dbESXs |?{$_.name -match $esxName} #| Select -ExpandProperty esxid, clid
# Get cluster name, and then db ID
$cluster = $vcClusters |?{$_.Moref -eq $esx.Parent} | Select -ExpandProperty Name
$clid = $dbClusters |?{$_.name -eq $cluster} | Select -ExpandProperty clid
if (!$clid) {
$clid = "NULL"
}
# Update ESX's clid if its changed
if ($clid -ne $dbESX.clid) {
ExecuteMySQLNonQuery("UPDATE esx SET clid=$clid WHERE esxid=" + $dbESX.esxid + ";")
Log($esx.name + " changed cluster ID " + $dbESX.clid + " --> $clid")
}
# Update ESX state
# Could add http://www.vmware.com/support/developer/vc-sdk/visdk400pubs/ReferenceGuide/vim.HostSystem.PowerState.html in later version
if ($esx.Runtime.ConnectionState -eq "connected") {
if ($esx.Summary.Runtime.InMaintenanceMode) {
$state = "Maintenance"
} else {
$state = "Connected"
}
} elseif ($esx.Runtime.ConnectionState -eq "disconnected") {
$state = "Disconnected"
} elseif ($esx.Runtime.ConnectionState -eq "notResponding") {
$state = "NotResponding"
} else {
$state = $esx.Runtime.ConnectionState
Log ("Unexpected ESX state " + $esx.name + ": " + $esx.Runtime.ConnectionState)
}
$query = "UPDATE esx_state SET state='" + $state + "', status='" + $esx.OverallStatus + "', "
$query += "cpu_pc=" + [Math]::Round(($esx.Summary.QuickStats.OverallCpuUsage / ($esx.Summary.Hardware.CpuMhz * $esx.Summary.Hardware.NumCpuCores) * 100)) + ", "
$query += "mem_pc=" + [Math]::Round(($esx.Summary.QuickStats.OverallMemoryUsage / ($esx.Summary.Hardware.MemorySize/1048576) * 100)) + " WHERE esxid=" + $dbESX.esxid + ";"
ExecuteMySQLNonQuery($query)
}
# Check for ESX's that have disappeared from VC
foreach ($dbESX in $dbESXs) {
$exists = $vcESXs |?{$_.name -match $dbESX.name} #| Select -ExpandProperty Summary.Hardware.NumCpuCores
if (!$exists) {
$query = "UPDATE esx_state SET state='Gone' WHERE esxid=" + $dbESX.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 -or $Debug) {
Rename-Item -Path db-base-check-test.log -NewName db-base-check-test-1.log -Force
Start-Transcript -Path db-base-check-test.log -Append
} else {
Rename-Item -Path db-base-check.log -NewName db-base-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 check run")
Perform-ESX-Check
Log ("Completed check run")
$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