VI-DB db-esx-check-v6.ps1

From vWiki
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