VI-DB db-esx-check-v4.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
#
# ==========================================================
$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