|
|
(21 intermediate revisions by the same user not shown) |
Line 7: |
Line 7: |
| # PHP front end (delivered by Apache running on same Ubuntu VM) | | # PHP front end (delivered by Apache running on same Ubuntu VM) |
|
| |
|
| == Database == | | == Releases == |
| <source lang="mysql">
| | === Matrix === |
| CREATE DATABASE vi;
| | {|cellpadding="4" cellspacing="0" border="1" |
| | |- style="background-color:#bbddff;" |
| | ! Release |
| | ! Database |
| | !colspan="3"| Data Collection |
| | !colspan="3"| Presentation (Main) |
| | ! Presentation (Other) |
| | |- |
| | ! [[#Version 1|Version 1]] |
| | |rowspan="2"| [[VI-DB_vi-db-initial|initial]] |
| | |colspan="2"| [[VI-DB_db-base-data-v1.0.ps1|db-base-data-v1.0.ps1]] |
| | |rowspan="4"| [[VI-DB_db-esx-check-v4.ps1|db-esx-check-v4.ps1]] |
| | |rowspan="2"| [[VI-DB_index.php_v1|index.php v1]] |
| | |rowspan="15"| [[VI-DB_status.php_v1|status.php v1]] |
| | |rowspan="9"| [[VI-DB_alpha.css_v1|alpha.css v1]] |
| | |rowspan="2"| [[VI-DB_vc-detail.php_v1|vc-detail.php v1]], [[VI-DB_cluster-detail.php_v1|cluster-detail.php v1]], [[VI-DB_vm-detail.php_v1|vm-detail.php v1]] |
| | |- |
| | ! [[#Version 1.1|Version 1.1]] |
| | |colspan="2"| [[VI-DB_db-base-data-v1.1.ps1|db-base-data-v1.1.ps1]] |
| | |- |
| | ! [[#Version 1.2|Version 1.2]] |
| | |rowspan="3"| [[VI-DB_vi-db-update-v1.2|update v1.2]] |
| | |colspan="2"| [[VI-DB_db-base-data-v1.2.ps1|db-base-data-v1.2.ps1]] |
| | |rowspan="3"| [[VI-DB_index.php_v1.1|index.php v1.1]] |
| | |rowspan="3"| [[VI-DB_snaps.php_v1|snaps.php v1]] |
| | |- |
| | ! [[#Version 1.2.1|Version 1.2.1]] |
| | |colspan="2"| [[VI-DB_db-base-data-v1.2.1.ps1|db-base-data-v1.2.1.ps1]] |
| | |- |
| | ! [[#Version 1.2.2|Version 1.2.2]] |
| | |colspan="2"| [[VI-DB_db-base-data-v1.2.2.ps1|db-base-data-v1.2.2.ps1]] |
| | |rowspan="6"| [[VI-DB_db-esx-check-v5.ps1|db-esx-check-v5.ps1]] |
| | |- |
| | ! [[#Version 2.0|Version 2.0]] |
| | |rowspan="2"| [[VI-DB_vi-db-update-v2.0|update v2.0]] |
| | |rowspan="5"| [[VI-DB_db-base-data-v2.0.ps1|db-base-data-v2.0.ps1]] |
| | | [[VI-DB_db-base-data-sub-v2.0.ps1|db-base-data-sub-v2.0.ps1]] |
| | |rowspan="2"| [[VI-DB_index.php_v2.0|index.php v2.0]] |
| | |rowspan="2"| [[VI-DB_run-errors.php_v2|run-errors.php v2]], [[VI-DB_vm-detail.php_v2|vm-detail.php v2]], [[VI-DB_snaps.php_v2|snaps.php v2]] |
| | |- |
| | ! [[#Version 2.1|Version 2.1]] |
| | | [[VI-DB_db-base-data-sub-v2.1.ps1|db-base-data-sub-v2.1.ps1]] |
| | |- |
| | ! [[#Version 2.2|Version 2.2]] |
| | |rowspan="2"| [[VI-DB_vi-db-update-v2.2|update v2.2]] |
| | | [[VI-DB_db-base-data-sub-v2.2.ps1|db-base-data-sub-v2.2.ps1]] |
| | | [[VI-DB_index.php_v2.2|index.php v2.2]] |
| | | [[VI-DB_vmtools.php_v2|vmtools.php v2]], [[VI-DB_vnics.php_v2|vnics.php v2]] |
| | |- |
| | ! [[#Version 2.3|Version 2.3]] |
| | | [[VI-DB_db-base-data-sub-v2.3.ps1|db-base-data-sub-v2.3.ps1]] |
| | | [[VI-DB_index.php_v2.3|index.php v2.3]] |
| | | [[VI-DB_esxs.php_v2|esxs.php v2]] |
| | |- |
| | ! [[#Version 2.4|Version 2.4]] |
| | |rowspan="3"| [[VI-DB_vi-db-update-v2.4|update v2.4]] |
| | | [[VI-DB_db-base-data-sub-v2.4.ps1|db-base-data-sub-v2.4.ps1]] |
| | |rowspan="6"| [[VI-DB_index.php_v2.4|index.php v2.4]] |
| | |rowspan="5"| [[VI-DB_alpha.css_v2|alpha.css v2]] |
| | | [[VI-DB_vapps.php_v2|vapps.php v2]] |
| | |- |
| | ! [[#Version 2.5|Version 2.5]] |
| | |rowspan="3"| [[VI-DB_db-base-data-v2.1.ps1|db-base-data-v2.1.ps1]] |
| | | [[VI-DB_db-base-data-sub-v2.5.ps1|db-base-data-sub-v2.5.ps1]] |
| | |rowspan="5"| [[VI-DB_db-esx-check-v6.ps1|db-esx-check-v6.ps1]] |
| | | [[VI-DB_run-errors.php_v2.1|run-errors.php v2.1]], [[VI-DB_vm-detail.php_v2.2|vm-detail.php v2.2]], [[VI-DB_vmtools.php_v2.1|vmtools.php v2.1]] |
| | |- |
| | ! [[#Version 2.6|Version 2.6]] |
| | | [[VI-DB_db-base-data-sub-v2.6.ps1|db-base-data-sub-v2.6.ps1]] |
| | | [[VI-DB_capman.php_v2.0|capman.php v2.0]] |
| | |- |
| | ! [[#Version 2.7|Version 2.7]] |
| | | [[VI-DB_vi-db-update-v2.7|update v2.7]] |
| | | [[VI-DB_db-base-data-sub-v2.7.ps1|db-base-data-sub-v2.7.ps1]] |
| | | [[VI-DB_capman.php_v2.1|capman.php v2.1]] |
| | |- |
| | ! [[#Version 2.8|Version 2.8]] |
| | | [[VI-DB_vi-db-update-v2.8|update v2.8]] |
| | | [[VI-DB_db-base-data-v2.2.ps1|db-base-data-v2.2.ps1]] |
| | | [[VI-DB_db-base-data-sub-v2.8.ps1|db-base-data-sub-v2.8.ps1]] |
| | | [[VI-DB_vc-detail.php_v2.2|vc-detail.php v2.2]], [[VI-DB_run-errors.php_v2.2|run-errors.php v2.2]] |
| | |- |
| | ! [[#Version 2.9|Version 2.9]] |
| | | [[VI-DB_vi-db-update-v2.9|update v2.9]] |
| | | [[VI-DB_db-base-data-v2.3.ps1|db-base-data-v2.3.ps1]] |
| | | [[VI-DB_db-base-data-sub-v2.9.ps1|db-base-data-sub-v2.9.ps1]] |
| | | [[VI-DB_alpha.css_v3|alpha.css v3]] |
| | | [[VI-DB_vm-detail.php_v2.3|vm-detail.php v2.3]], [[VI-DB_ds-info.php_v2.1|ds-info.php v2.1]] |
| | |} |
|
| |
|
| # VC table - this is the only table that must be manually populated
| |
| CREATE TABLE vc (vcid TINYINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32), site VARCHAR(3), ver VARCHAR(7), build INT UNSIGNED, last_pass DATETIME, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vcid), UNIQUE KEY (name));
| |
|
| |
|
| # Cluster table
| | * Standard include files... |
| CREATE TABLE cluster (clid TINYINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, ha BOOL, drs VARCHAR(18), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid), UNIQUE KEY (vcid, name));
| | ** [[PS_Standard.ps1|Standard.ps1]] - Basic PowerShell helper (logging etc) |
| | ** [[PS_PowerCLI-helper-v1.1.ps1|PowerCLI-helper-v1.1.ps1]] - PowerCLI helper (provides API bug workarounds) |
| | ** [[PS_DS-LUN-v4.ps1|DS-LUN-v4.ps1]] - PowerCLI Datastore helper (LUN's etc) |
| | ** [[PS_getsnapshotsize-1.ps1|getsnapshotsize-1.ps1]] - PowerCLI Get_Snapshot bug workaround |
|
| |
|
| # VM tables
| | === Change Log === |
| CREATE TABLE vm (vmid INT UNSIGNED AUTO_INCREMENT, uuid VARCHAR(37), vmvcid SMALLINT UNSIGNED, pid VARCHAR(37), name VARCHAR(32), exist BOOL, is_on BOOL, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, esxid SMALLINT UNSIGNED, hostname VARCHAR(255), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid), UNIQUE KEY (uuid, pid), UNIQUE KEY (vmvcid, vcid));
| | ==== Version 1.1 ==== |
| | * Data Collection (db-base-data) |
| | ** Bugfix: Where VC provides no VM FolderID, VM is assigned incorrect VC folder path - now catch as error |
| | ** Bugfix: VM's having no IPAddress in vCentre not handled properly, causing errors - now handled silently |
| | ** Bugfix: Added vmvcid to better handle Lab Manager v4 VM's with duplicate UUIDs |
| | ** Bugfix: ESX's moving clusters causes duplicate ESX entries - old ESX/cluster entry now gets expired |
| | ** Added snapshot info (data collection only, db table already existed) |
|
| |
|
| CREATE TABLE vm_ext (vmid INT UNSIGNED, cpu TINYINT UNSIGNED, mem INT UNSIGNED, vc_path VARCHAR(512), vmx_path VARCHAR(512), osid SMALLINT UNSIGNED, scsi_hw VARCHAR(16), notes VARCHAR(512), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid));
| | ==== Version 1.2 ==== |
| | * '''Data Collection (db-base-data)''' |
| | ** Bugfix: Snapshot sizes not right (known PowerCLI bug in Get-Snapshot that I'd wrongly assumed to be fixed) |
| | ** Added population of snap.vrange (is snapshot a vRanger snapshot) |
| | ** Added ESX extended data (inc IP - needs further work), ntp, hba and nic info |
| | ** Consolidated SQL query strings |
| | * '''Presentation (index)''' |
| | ** Bugfix: VM's exist and On options ignored in non-fuzzy hostname searches |
| | ** Added "VMs On" to vCentre summary |
| | * '''Presentation (status''') |
| | ** Bugfix: Expired clusters showing (cluster.exist being ignored) |
| | ** Bugfix: Standalone ESX's ignored, now shown as member of sudo-cluster "Standalone" |
| | ** Improved footer links |
| | * '''Presentation (snaps)''' |
| | ** Initial - list of VM snapshots |
| | * '''Presentation (vm-detail)''' |
| | ** Added datastore usage summary |
|
| |
|
| # ESX tables
| | ==== Version 1.2.1 ==== |
| CREATE TABLE esx (esxid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, state VARCHAR(16), ver VARCHAR(7), build INT UNSIGNED, model VARCHAR(32), cpu_core TINYINT UNSIGNED, cpu_sock TINYINT UNSIGNED, mem INT UNSIGNED COMMENT 'MB', cpu_model VARCHAR(128), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid), UNIQUE KEY (vcid, clid, name));
| | * '''Data Collection (db-base-data)''' |
| | ** Bugfix: Standalone ESX's cause SQL INSERT error (as clid isn't defined) |
| | ** Bugfix: Script crash when unable to get ESX NIC/HBA info (happens when ESX not connected) |
|
| |
|
| CREATE TABLE esx_state (esxid SMALLINT UNSIGNED, state VARCHAR(16), status VARCHAR(6), cpu_pc TINYINT, mem_pc TINYINT, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid));
| | ==== Version 1.2.2 ==== |
| | * '''Data Collection (db-base-data)''' |
| | ** Workaround: DV switch doesn't have VLAN ID in same place as Port Group causing SQL INSERT fail, caught and set to NULL |
|
| |
|
| CREATE TABLE esx2ds (esxid SMALLINT UNSIGNED, dsid SMALLINT UNSIGNED, PRIMARY KEY (esxid, dsid));
| | ==== Version 2.0 ==== |
| CREATE TABLE esx2nw (esxid SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, PRIMARY KEY (esxid, nwid));
| | * '''Data Collection (db-base-data)''' |
| | ** Changed to Async/PS Jobs in order to run against multiple vCentre's simultaneously |
| | *** vCentre data mining moved to sub-script (db-base-data-sub) |
| | *** Moved MySQL functions out to library file (shared between main and sub-script) |
| | *** Enforce run in 32 bit environment (PowerCLI bug workaround, vSphere scripts crash Powershell when launched as background jobs in 64bit) |
| | ** Added basic support for vApp VM's (name add to vm_ext, vm folder now supported) |
| | ** Added logging of errors to database |
| | ** Added sanity checks (make sure total no of clusters, ESXs, VMs in db matches VC) |
| | ** Added basic support for distributed switches, and added special network types (mgmt, vmk, ft) |
| | ** Improved deleted VM handling |
| | ** Change logging timestamp from xxx secs to hh:mm:ss.msec |
| | ** Bugfix: Old VMDK's / vNIC's don't get removed from VM's - additional now deleted NOT WORKING YET, NIC's get added and deleted on some VM's **** TO FIX **** |
| | * '''Presentation (index)''' |
| | ** Added links to run-errors and ds-waste |
| | * '''Presentation (run-errors)''' |
| | ** Initial creation |
| | * '''Presentation (vm-detail)''' |
| | ** Added vApp and UUID |
| | ** Added datastore usage summary |
| | * '''Presentation (snaps)''' |
| | ** Added basic sorting |
|
| |
|
| # Datastore and VMDK tables
| | ==== Version 2.1 ==== |
| CREATE TABLE ds (dsid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, size INT UNSIGNED, used INT UNSIGNED, san_model VARCHAR(32), lun TINYINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dsid), UNIQUE KEY (vcid, name));
| | * '''Data Collection (db-base-data-sub)''' |
| | ** Performance improvements (reduce's runtime by about 60%) |
| | *** Improved VM Cluster get to use lookup table |
| | *** Changed VM Guest OS get to use existing VM object |
| | ** Bugfix: Lab Manager VM's not being removed after deletion (include checking of Persistent ID) |
|
| |
|
| CREATE TABLE vmdk (dkid INT UNSIGNED AUTO_INCREMENT, dsid SMALLINT UNSIGNED, vmid INT UNSIGNED, num SMALLINT UNSIGNED, size INT UNSIGNED, thin BOOL, path VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dkid), UNIQUE KEY (vmid, num));
| | ==== Version 2.2 ==== |
| | * '''Data Collection (db-base-data-sub)''' |
| | ** Minor improvements to error messages |
| | ** Performance improvements |
| | *** Caching of Get-VMHostNetworkAdapter during ESX info gather |
| | ** Added VMTools state and VM version |
| | * '''Presentation (index)''' |
| | ** Added links |
| | * '''Presentation (vm-detail)''' |
| | ** Added VMTools state |
| | * '''Presentation (vmtools)''' |
| | ** Initial |
| | * '''Presentation (vnics)''' |
| | ** Initial |
|
| |
|
| # Networking tables
| | ==== Version 2.3 ==== |
| CREATE TABLE nw (nwid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(64), exist BOOL, vlan SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nwid), UNIQUE KEY (vcid, name));
| | * '''Data Collection (db-base-data-sub)''' |
| | ** Added ESX NIC firmware version |
| | * '''Presentation (index)''' |
| | ** Added links |
| | * '''Presentation (esxs)''' |
| | ** Initial |
|
| |
|
| CREATE TABLE vm_nic (vnicid INT UNSIGNED AUTO_INCREMENT, vmid INT UNSIGNED, num SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, type VARCHAR(16), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vnicid), UNIQUE KEY (vmid, num));
| | ==== Version 2.4 ==== |
| CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
| | * '''Data Collection (db-base-data-sub)''' |
| | ** Bugfix: VM's not in cluster don't get added to db (introduced in v2.1) |
| | ** Bugfix: "Couldn't find ESX ID for VM" error catch not working |
| | ** Added VM custom attributes |
| | ** Added vApps |
| | * '''Presentation (index)''' |
| | ** Added link to vApps |
| | * '''Presentation (vapps)''' |
| | ** Initial |
| | * '''Presentation (css)''' |
| | ** Added additional table cell formats |
|
| |
|
| CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
| | ==== Version 2.5 ==== |
| | * '''Data Collection (db-base-data)''' |
| | ** Added check for sub-script fail |
| | * '''Data Collection (db-base-data-sub)''' |
| | ** Bugfix: Remove old VMDK's for VMs didn't work (SQL query incorrect) |
| | ** Bugfix: VM NICs with multiple IPs not handled correctly |
| | ** Improved error logging (DS inaccessible, VMs not in cluster) |
| | ** Added sev0 error log at completion to signal parent script that script exited cleanly |
| | * '''Data Collection (db-esx-check)''' |
| | ** Bugfix: Log rollover didn't work |
| | ** Performance optimisation - changed data gather to use single Get-View |
| | * '''Presentation (vm-detail)''' |
| | ** Added conditional formatting to Powered, VM Tools, vmnic version |
| | ** Added link to vApp |
| | ** Added VM Attributes |
| | * '''Presentation (run-errors)''' |
| | ** Updated for 'note' severity category |
| | * '''Presentation (vmtools)''' |
| | ** Updated Tools Status handling |
|
| |
|
| # Other tables
| | [[Category:VMware]] |
| CREATE TABLE os (osid TINYINT UNSIGNED AUTO_INCREMENT, guest_name VARCHAR(128), short_name VARCHAR(32), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (osid), UNIQUE KEY (guest_name));
| |
| | |
| CREATE TABLE script_run (runid INT UNSIGNED AUTO_INCREMENT, start DATETIME, end DATETIME, version VARCHAR(8), error INT UNSIGNED, warn INT UNSIGNED, PRIMARY KEY (runid));
| |
| | |
| CREATE TABLE cluster_stats (clid TINYINT UNSIGNED, dated DATE, esxs TINYINT UNSIGNED, vms_exist SMALLINT UNSIGNED, vms_on SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid, dated));
| |
| | |
| CREATE TABLE snap (vmid INT UNSIGNED, sid INT UNSIGNED, vcid TINYINT UNSIGNED, name VARCHAR(256), descr VARCHAR(1024), created DATETIME, quiesced BOOL, vm_on BOOL, size MEDIUMINT UNSIGNED, parent_sid INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, sid), UNIQUE KEY (vcid, sid));
| |
| | |
| # Eventing
| |
| CREATE DEFINER=`root`@`192.168.196.1` PROCEDURE `update_cluster_stats`()
| |
| BEGIN
| |
| DECLARE done INT DEFAULT 0;
| |
| DECLARE clust_id INT;
| |
| DECLARE clust CURSOR FOR SELECT clid FROM cluster;
| |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
| |
|
| |
| OPEN clust;
| |
|
| |
| read_loop: LOOP
| |
| FETCH clust INTO clust_id;
| |
| IF done THEN
| |
| LEAVE read_loop;
| |
| END IF;
| |
|
| |
| INSERT INTO cluster_stats (clid, dated, esxs, vms_exist, vms_on)
| |
| SELECT clust_id, NOW(),
| |
| (SELECT COUNT(*) FROM esx WHERE clid=clust_id AND exist=1) AS esxs,
| |
| COUNT(*) AS vms_exist, SUM(IF(is_on=1,1,0)) AS vms_on
| |
| FROM vm WHERE clid=clust_id AND exist=1;
| |
|
| |
| END LOOP;
| |
|
| |
| CLOSE clust;
| |
| END
| |
| | |
| CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();
| |
| </source>
| |
| | |
| == Data Collection ==
| |
| === Main Daily Script ===
| |
| | |
| [[VI-DB_db-base-data-v1.0.ps1|db-base-data-v1.0.ps1]]
| |
| | |
| === Intra-Day Status ===
| |
| <source lang="powershell">
| |
| # ==========================================================
| |
| # 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>
| |