|
|
Line 88: |
Line 88: |
|
| |
|
| === Intra-Day Status === | | === Intra-Day Status === |
| <source lang="powershell">
| | [[VI-DB_db-esx-check-v4.ps1|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
| |
| </source>
| |