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

# ==========================================================
# 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