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

From vwiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
# ==========================================================
# 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