|
|
(22 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 ===
| |
| <source lang="powershell">
| |
| # =========================================================================================================================================
| |
| # Database Base Data Getter
| |
| # =========================================================================================================================================
| |
| # Simon Strutt Jan 2011
| |
| # =========================================================================================================================================
| |
| #
| |
| # Version 1
| |
| # - Initial creation
| |
| #
| |
| # ===========================================================================================================================================
| |
| | |
| $start = Get-Date
| |
| $UserFile = "User.fil"
| |
| $PassFile = "Pass.fil" # Encrypted file to store password in
| |
| | |
| # Include library files
| |
| . .\lib\Standard.ps1
| |
| . .\lib\PowerCLI-helper-v1.1.ps1
| |
| . .\temp\DS-LUN-v4.ps1
| |
| | |
| # DB connection stuff...
| |
| $user = 'powercli'
| |
| $pass = 'powercli'
| |
| $database = 'vi'
| |
| $MySQLHost = '192.168.10.128'
| |
| | |
| $LabMgrSvr = "lbmgr-svr-a" # Lab Manager (optional)
| |
| | |
| $Major_Error = 0 # Major error counter
| |
| $ErrorLog = @() # Minor error tracker
| |
| | |
| # Flags
| |
| $UpdateScriptRun = 1 # Update script runs log in database
| |
| $LogNonQueries = 1 # Non Query (insert/update etc) logging
| |
| $UpdateCL = 1 # Clusters
| |
| $UpdateESX = 1 # ESXs
| |
| $UpdateDS = 1 # Datastores
| |
| $UpdateNW = 1 # Networks
| |
| $UpdateVM = 1 # Virtual Machines
| |
| $UpdateLM = 1 # Lab Manager (update VM external IPs)
| |
| | |
| Start-Transcript -Path db-base-data.log
| |
| Log "Started script run at $start"
| |
| | |
| if (!$UpdateCL -or !$UpdateESX -or !$UpdateDS -or !$UpdateNW -or !$UpdateVM) {
| |
| $UpdateScriptRun = 0
| |
| Log("Updating of script run info in db disabled (performing partial run)")
| |
| }
| |
| | |
| $ScriptVer = [regex]::matches(($MyInvocation.MyCommand.Name), "(?<=v)[0-9]+(\.[0-9])+(?=.)")
| |
| Log "Script version is $ScriptVer"
| |
| | |
| | |
| # 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
| |
| }
| |
| }
| |
| | |
| # =============================================================================================
| |
| # Lab Manager SOAP connection functions (adapted from POSH - http://poshcode.org/753)
| |
| | |
| function New-ObjectFromProxy {
| |
| param($proxy, $proxyAttributeName, $typeName)
| |
| | |
| # Locate the assembly for $proxy
| |
| $attribute = $proxy | gm | where { $_.Name -eq $proxyAttributeName }
| |
| $str = "`$assembly = [" + $attribute.TypeName + "].assembly"
| |
| invoke-expression $str
| |
| | |
| # Instantiate an AuthenticationHeaderValue object.
| |
| $type = $assembly.getTypes() | where { $_.Name -eq $typeName }
| |
| return $assembly.CreateInstance($type)
| |
| }
| |
| | |
| function Connect-LabManager {
| |
| param($server, $credential)
| |
|
| |
| Write-Host "Connecting to $server..."
| |
| | |
| # Log in to Lab Manager's web service.
| |
| $server = "https://" + $server + "/"
| |
| $endpoint = $server + "LabManager/SOAP/LabManager.asmx"
| |
| try {
| |
| $proxy = new-webserviceproxy -uri $endpoint -cred $credential -ErrorAction:Stop
| |
| } catch {
| |
| Write-Host $_
| |
| if ($_.ErrorDetails.Message -match "401: Unauthorized") {
| |
| Remove-Item $UserFile
| |
| Remove-Item $PassFile
| |
| Write-Host "User/pass credential cache files have been deleted"
| |
| } else {
| |
| Write-Host $_.Exception.Message
| |
| Write-Host $_.Exception.GetType().FullName
| |
| }
| |
| PressAnyKeyToExit
| |
| }
| |
| | |
| # Before continuing we need to add an Authentication Header to $proxy.
| |
| $authHeader = New-ObjectFromProxy -proxy $proxy -proxyAttributeName "AuthenticationHeaderValue" -typeName "AuthenticationHeader"
| |
| $authHeader.username = $credential.GetNetworkCredential().UserName
| |
| $authHeader.password = $credential.GetNetworkCredential().Password
| |
| $proxy.AuthenticationHeaderValue = $authHeader
| |
| return $proxy
| |
| }
| |
| | |
| function Ignore-SslErrors {
| |
| # Create a compilation environment
| |
| $Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
| |
| $Compiler=$Provider.CreateCompiler()
| |
| $Params=New-Object System.CodeDom.Compiler.CompilerParameters
| |
| $Params.GenerateExecutable=$False
| |
| $Params.GenerateInMemory=$True
| |
| $Params.IncludeDebugInformation=$False
| |
| $Params.ReferencedAssemblies.Add("System.DLL") > $null
| |
| $TASource=@'
| |
| namespace Local.ToolkitExtensions.Net.CertificatePolicy {
| |
| public class TrustAll : System.Net.ICertificatePolicy {
| |
| public TrustAll() {
| |
| }
| |
| public bool CheckValidationResult(System.Net.ServicePoint sp,
| |
| System.Security.Cryptography.X509Certificates.X509Certificate cert,
| |
| System.Net.WebRequest req, int problem) {
| |
| return true;
| |
| }
| |
| }
| |
| }
| |
| '@
| |
| $TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
| |
| $TAAssembly=$TAResults.CompiledAssembly
| |
| | |
| ## We now create an instance of the TrustAll and attach it to the ServicePointManager
| |
| $TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
| |
| [System.Net.ServicePointManager]::CertificatePolicy=$TrustAll
| |
| }
| |
| | |
| function Log-Error($text) {
| |
| # if (!$ErrorLog) {
| |
| # $ErrorLog = @()
| |
| # }
| |
| $script:ErrorLog += $text
| |
| Log ("ERROR: " + $text)
| |
| Log ("Error count now " + $ErrorLog.Length)
| |
| }
| |
| | |
| # Pre Amble ==============================================================================================================================
| |
| | |
| # Connect to MySQL database
| |
| $SQLconn = ConnectMySQL $user $pass $MySQLHost $database
| |
| | |
| # Create script_run entry
| |
| if ($UpdateScriptRun) {
| |
| ExecuteMySQLNonQuery("INSERT INTO script_run (start, version) VALUES (NOW(), '" + $ScriptVer + "');")
| |
| $runid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
| }
| |
| | |
| # Get list of VC's (checks the SQL connection is in good working order as well)
| |
| $VCs = ExecuteMySQLQuery("SELECT vcid, name, site FROM vc;")
| |
| | |
| # Disconnect any existing VI Server sessions (otherwise you can end up with duplicate VM's)
| |
| if ($DefaultVIServers.Count) {
| |
| Log("Disconnect existing vCentre server connections...")
| |
| Disconnect-VIServer -Server * -Force -Confirm:$false
| |
| }
| |
| | |
| # 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)
| |
| | |
| # Business Loop ============================================================================================================================
| |
| | |
| foreach ($vc in $VCs) {
| |
| # Check its a valid record 1st
| |
| if (!$vc.vcid) {
| |
| Continue
| |
| }
| |
|
| |
| # Connect to vCentre
| |
| try {
| |
| Log("Connecting to " + $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 ($UpdateScriptRun) {
| |
| # Get VC info
| |
| $vcinfo = (Get-View ServiceInstance).Content.About
| |
| ExecuteMySQLNonQuery("UPDATE vc SET ver='" + $vcinfo.Version + "', build=" + $vcinfo.Build + ", last_pass=NOW() WHERE vcid=" + $vc.vcid + ";")
| |
| }
| |
|
| |
| # Clusters ------------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateCL) {
| |
| # Get existing clusters from VC
| |
| $vcClusters = Get-Cluster
| |
|
| |
| foreach ($cl in $vcClusters) {
| |
| # Add/update cluster
| |
| if ($cl.DrsEnabled) {
| |
| $query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('" + $vc.vcid + "', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsAutomationLevel + "', 1) "
| |
| $query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsAutomationLevel + "', exist=1;"
| |
| } else {
| |
| $query = "INSERT INTO cluster (vcid, name, ha, drs, exist) VALUES ('" + $vc.vcid + "', '" + $cl.Name + "', '" + $cl.HAEnabled.GetHashCode() + "', '" + $cl.DrsEnabled.GetHashCode() + "', 1) "
| |
| $query += "ON DUPLICATE KEY UPDATE ha=" + $cl.HAEnabled.GetHashCode() + ", drs='" + $cl.DrsEnabled.GetHashCode() + "', exist=1;"
| |
| }
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
|
| |
| # Mark clusters that no longer exist
| |
| $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($cl in $dbClusters) {
| |
| if (!($vcClusters |?{$_.Name -eq $cl.name})) {
| |
| if (!$cl.clid) {
| |
| Continue
| |
| }
| |
| Log ("HIDE: " + $cl.name)
| |
| ExecuteMySQLNonQuery("UPDATE cluster SET exist=0 WHERE clid=" + $cl.clid + ";")
| |
| }
| |
| }
| |
|
| |
| }
| |
|
| |
| # ESX --------------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateESX) {
| |
| $vcESXs = Get-VMHost
| |
|
| |
| # Get clusters and ESXs from db
| |
| $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| #$dbESXs = ExecuteMySQLQuery("SELECT esxid, name from esx WHERE vcid = " + $vc.vcid + ";")
| |
|
| |
| # Add/update info in db
| |
| foreach ($esx in $vcESXs) {
| |
| $query = "INSERT INTO esx (vcid, clid, name, exist, ver, build, model, cpu_core, cpu_sock, mem, cpu_model) "
| |
| $query += "VALUES (" + $vc.vcid + ", " + ($dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid) + ", '" + $esx.Name.Split(".")[0] + "', 1"
| |
| $query += ", '" + $esx.Version + "', " + $esx.Build + ", '" + ($esx.Model -replace ' {2,}',' ') + "', " + $esx.NumCpu + ", " + $esx.ExtensionData.Hardware.CpuPkg.Count + ", " + $esx.MemoryTotalMB
| |
| $query += ", '" + $esx.ExtensionData.Summary.Hardware.CpuModel + "') ON DUPLICATE KEY UPDATE esxid=LAST_INSERT_ID(esxid), clid=" + ($dbClusters |?{$_.name -eq $esx.Parent} | Select -ExpandProperty clid)
| |
| $query += ", exist=1, ver='" + $esx.Version + "', build=" + $esx.Build + ", model='" + ($esx.Model -replace ' {2,}',' ')
| |
| $query += "', cpu_core=" + $esx.NumCpu + ", cpu_sock=" + $esx.ExtensionData.Hardware.CpuPkg.Count + ", mem=" + $esx.MemoryTotalMB + ", cpu_model='" + $esx.ExtensionData.Summary.Hardware.CpuModel + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| # Get esxid from last query
| |
| $esxid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
|
| |
| $query = "INSERT INTO esx_state (esxid, state) VALUES (" + $esxid + ", '" + $esx.ConnectionState + "') ON DUPLICATE KEY UPDATE state='" + $esx.ConnectionState + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
|
| |
|
| |
| # Mark ESX's that no longer exist
| |
| $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($esx in $dbESXs) {
| |
| if (!($vcESXs |?{$_.Name.Split(".")[0] -eq $esx.name})) {
| |
| if (!$esx.esxid) {
| |
| Continue
| |
| }
| |
| Log("HIDE: " + $esx.name)
| |
| ExecuteMySQLNonQuery("UPDATE esx SET exist=0 WHERE esxid=" + $esx.esxid + ";")
| |
| }
| |
| }
| |
|
| |
| }
| |
|
| |
| # Datastores ------------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateDS) {
| |
| $vcDSs = Get-Datastore
| |
|
| |
| # Get SAN model(s) and LUN ID's
| |
| if ($vcinfo.version -lt 4) {
| |
| Log("Getting v3 LUN info...")
| |
| $luns = Get-DS-LUNs-v3 (Get-VMHost)
| |
| } else {
| |
| Log("Getting v4 LUN info...")
| |
| $luns = Get-DS-LUNs-v4
| |
| }
| |
| Log("...got " + $luns.count + " LUNs")
| |
|
| |
| foreach ($ds in $vcDSs) {
| |
| $lun = $luns |?{$_.Datastore -eq $ds.Name}
| |
| if (!$lun) {
| |
| Log-Error ("Couldn't find LUN ID for datastore" + $ds.Name)
| |
| Continue
| |
| }
| |
| # Workaround for occassional duplicate LUNs retruned by Get-DS-LUN
| |
| if ($lun.count) {
| |
| $lun = $lun[0]
| |
| }
| |
| $query = "INSERT INTO ds (vcid, name, exist, size, used, san_model, lun) VALUES ('" + $vc.vcid + "', '" + $ds.Name + "', 1, " + $ds.CapacityMB + ", " + ($ds.CapacityMB - $ds.FreeSpaceMB)
| |
| $query += ", '" + $lun.Make + " " + $lun.Model + "', " + $lun.LUN + ") ON DUPLICATE KEY UPDATE "
| |
| $query += "exist=1, size=" + $ds.CapacityMB + ", used=" + ($ds.CapacityMB - $ds.FreeSpaceMB) + ", san_model='" + $lun.Make + " " + $lun.Model + "', lun=" + $lun.LUN + ";"
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
|
| |
| # Link ESX's to DS's
| |
| $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($esx in $dbESXs) {
| |
| if (!$esx.esxid) {
| |
| Continue
| |
| }
| |
| $vcESXDCs = Get-Datastore -VMHost (Get-VMHost ($esx.name + "*"))
| |
| foreach ($esxdc in $vcESXDCs) {
| |
| # Catch linking failure (prevents SQL query failure, allows script to continue)
| |
| $dsid = $dbDSs |?{$_.name -eq $esxdc.Name} | Select -ExpandProperty dsid
| |
| if (!$dsid) {
| |
| Log-Error ("Couldn't find datastore" + $esxdc.Name + " in db")
| |
| Continue
| |
| }
| |
| ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2ds (esxid, dsid) VALUES (" + $esx.esxid + ", " + $dsid +");")
| |
| }
| |
| }
| |
|
| |
| # Mark datastores that no longer exist
| |
| $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($ds in $dbDSs) {
| |
| if (!($vcDSs |?{$_.Name -eq $ds.name})) {
| |
| if (!$ds.dsid) {
| |
| Continue
| |
| }
| |
| Log ("HIDE: " + $ds.name)
| |
| ExecuteMySQLNonQuery("UPDATE ds SET exist=0 WHERE dsid=" + $ds.dsid + ";")
| |
| }
| |
| }
| |
|
| |
| }
| |
|
| |
| # Networks ------------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateNW) {
| |
| $vcNWs = Get-VirtualPortGroup
| |
|
| |
| foreach ($nw in $vcNWs) {
| |
| $query = "INSERT INTO nw (vcid, name, exist, vlan) VALUES ('" + $vc.vcid + "', '" + $nw.Name + "', 1, " + $nw.VLanId + ") "
| |
| $query += "ON DUPLICATE KEY UPDATE exist=1, vlan=" + $nw.VLanId + ";"
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
|
| |
| # Link ESX's to NW's
| |
| $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($esx in $dbESXs) {
| |
| if (!$esx.esxid) {
| |
| Continue
| |
| }
| |
| $vcESXNWs = Get-VirtualPortGroup -VMHost (Get-VMHost ($esx.name + "*"))
| |
| foreach ($esxnw in $vcESXNWs) {
| |
| # Catch linking failure (prevents SQL query failure, allows script to continue)
| |
| $nwid = $dbNWs |?{$_.name -eq $esxnw.Name} | Select -ExpandProperty nwid
| |
| if (!$nwid) {
| |
| Log-Error ("Couldn't find network " + $esxnw.Name + " (vlan " + $esxnw.VLanId + ") in db")
| |
| Continue
| |
| }
| |
| ExecuteMySQLNonQuery("INSERT IGNORE INTO esx2nw (esxid, nwid) VALUES (" + $esx.esxid + ", " + $nwid +");")
| |
| }
| |
| }
| |
|
| |
| # Mark networks that no longer exist
| |
| $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($ds in $dbNWs) {
| |
| if (!($vcNWs |?{$_.Name -eq $nw.name})) {
| |
| if (!$nw.nwid) {
| |
| Continue
| |
| }
| |
| Log ("HIDE: " + $nw.name)
| |
| ExecuteMySQLNonQuery("UPDATE nw SET exist=0 WHERE nwid=" + $nw.nwid + ";")
| |
| }
| |
| }
| |
| }
| |
|
| |
| # Virtual Machines --------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateVM) {
| |
| $vcVMs = Get-VM
| |
| $dbClusters = ExecuteMySQLQuery("SELECT clid, name FROM cluster WHERE vcid = " + $vc.vcid + ";")
| |
| $dbESXs = ExecuteMySQLQuery("SELECT esxid, name FROM esx WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| $dbNWs = ExecuteMySQLQuery("SELECT nwid, name FROM nw WHERE vcid = " + $vc.vcid + ";")
| |
| $dbDSs = ExecuteMySQLQuery("SELECT dsid, name FROM ds WHERE vcid = " + $vc.vcid + ";")
| |
| $dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
| |
|
| |
| # Add/update VM's in database
| |
| foreach ($vm in $vcVMs) {
| |
| # Add base record
| |
| # Catch linking failure (prevents SQL query failure, allows script to continue)
| |
| $clid = $dbClusters |?{$_.name -eq (Get-Cluster -VM $vm).Name} | Select -ExpandProperty clid
| |
| $vmGuest = Get-VMGuest -VM $vm
| |
| if (!$clid) {
| |
| Log-Error ("Couldn't find cluster " + (Get-Cluster -VM $vm).Name + " for VM: " + $vm.Name + " in db")
| |
| Continue
| |
| }
| |
| $esxid = $dbESXs |?{$_.name -eq $vm.VMHost.Name.Split(".")[0]} | Select -ExpandProperty esxid
| |
| if (!$clid) {
| |
| Log-Error ("Couldn't find ESX " + $vm.VMHost.Name.Split(".")[0] + " for VM: " + $vm.Name + " in db")
| |
| Continue
| |
| }
| |
| if ($vm.PowerState -eq "PoweredOn") {
| |
| $on = 1
| |
| } else {
| |
| $on = 0
| |
| }
| |
| $query = "INSERT INTO vm (uuid, pid, name, exist, is_on, vcid, clid, esxid, hostname) VALUES ('" + $vm.ExtensionData.Config.Uuid + "', '" + $vm.PersistentId + "', '" + $vm.Name
| |
| $query += "', 1, " + $on + ", " + $vc.vcid + ", " + $clid + ", " + $esxid + ", '" + $vmGuest.HostName + "') ON DUPLICATE KEY UPDATE vmid=LAST_INSERT_ID(vmid), name='" + $vm.Name
| |
| $query += "', exist=1, is_on=" + $on + ", clid=" + $clid + ", esxid=" + $esxid + ", hostname='" + $vmGuest.HostName + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| # Get vmid from last query
| |
| $vmid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
|
| |
| $vcNICs = Get-NetworkAdapter -VM $vm #(VI3 doesn't return Network Adapter x info)
| |
| #$vcNICs = $vm.Guest.Nics
| |
|
| |
| foreach ($nic in $vcNICs) {
| |
| # Add NIC
| |
| $nwid = $dbNWs |?{$_.name -eq $nic.NetworkName} | Select -ExpandProperty nwid
| |
| if (!$nwid) {
| |
| Log-Error ("Couldn't find network " + $nic.Name + " for VM: " + $vm.Name + " in db")
| |
| Continue
| |
| }
| |
| $nwno = [regex]::replace($nic.Name, "Network (?i:A)dapter ", "")
| |
| if (!([regex]::matches($nwno, "\d").Count)) {
| |
| if ($vm.NetworkAdapters.Count -eq 1) {
| |
| $nwno = 1
| |
| } else {
| |
| Log-Error ("Couldn't get NIC number from " + $nic.Name + " for VM: " + $vm.Name)
| |
| Continue
| |
| }
| |
| }
| |
| $query = "INSERT INTO vm_nic (vmid, num, nwid, type) VALUES (" + $vmid + ", " + $nwno + ", " + $nwid + ", '" + $nic.Type + "') "
| |
| $query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid), nwid=" + $nwid + ", type='" + $nic.Type + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| # Get vnicid from last query
| |
| $vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
|
| |
| # Find related IP(s)
| |
| $ips = $vm.Guest.Nics |?{$_.NetworkName -eq $nic.NetworkName} | Select -ExpandProperty IPAddress
| |
| if (!$ips) {
| |
| Log-Error ("Couldn't find IP(s) for VM: " + $vm.Name + " NIC: " + $nic.Name + " from VC")
| |
| Continue
| |
| }
| |
| foreach ($ip in $ips) {
| |
| $query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES (" + $vmid + ", " + $vnicid + ", INET_ATON('" + $ip + "')) "
| |
| $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $ip + "');"
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
|
| |
| }
| |
|
| |
| # Add VMDK record(s)
| |
| foreach ($vmdk in $vm.HardDisks) {
| |
| # Create VMDK record
| |
| $dsid = $dbDSs |?{$_.name -eq (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value)} | Select -ExpandProperty dsid
| |
| if (!$dsid) {
| |
| Log-Error ("Couldn't find datastore " + (([regex]::matches($vmdk.Filename, "(?<=\[)(.*?)(?=\])")).Item(0).Value) + " for VM: " + $vm.Name + " in db")
| |
| Continue
| |
| }
| |
| $dkno = [regex]::replace($vmdk.Name, "Hard (?i:D)isk ", "")
| |
| $dksize = [math]::Round(($vmdk.CapacityKB / 1024), 0)
| |
| if ($vmdk.StorageFormat -eq "Thin") {
| |
| $dkthin = 1
| |
| } else {
| |
| $dkthin = 0
| |
| }
| |
| $query = "INSERT INTO vmdk (dsid, vmid, num, size, thin, path) VALUES (" + $dsid + ", " + $vmid + ", " + $dkno + ", " + $dksize + ", " + $dkthin
| |
| $query += ", '" + $vmdk.Filename + "') ON DUPLICATE KEY UPDATE dsid=" + $dsid + ", size=" + $dksize + ", thin=" + $dkthin + ", path='" + $vmdk.Filename + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| }
| |
|
| |
| # Add/update extended VM info
| |
|
| |
| $vmvcpath = [regex]::replace((Get-FolderPath (Get-Folder -Id $vm.FolderId)), "\\", "\\")
| |
| #$vmvcpath = Get-FolderPath (Get-Folder -Id $vm.FolderId)
| |
| #$vmxpath = [regex]::replace($vm.ExtensionData.Config.Files.VmPathName, "\", "\\")
| |
| $vmxpath = $vm.ExtensionData.Config.Files.VmPathName
| |
| $vmscsi = ($vm.ExtensionData.Config.Hardware.Device | where {$_.DeviceInfo.Label -match "SCSI Controller"}).DeviceInfo.Summary
| |
| $vmnotes = [regex]::replace($vm.Notes, "(\s?\bvRanger.*Repository \[.*\]\s?)|(\[vRanger.*Host \[.*\]\.\]\s?)", "")
| |
| $vmnotes = [regex]::replace($vmnotes, "'", "\'")
| |
| $vmnotes = [regex]::replace($vmnotes, "`n", "\\n")
| |
|
| |
| $query = "INSERT INTO vm_ext (vmid, cpu, mem, vc_path, vmx_path, scsi_hw, notes) VALUES (" + $vmid + ", " + $vm.NumCpu + ", " + $vm.MemoryMB + ", '" + $vmvcpath
| |
| $query += "', '" + $vmxpath + "', '" + $vmscsi + "', '" + $vmnotes + "') ON DUPLICATE KEY UPDATE cpu=" + $vm.NumCpu + ", mem=" + $vm.MemoryMB
| |
| $query += ", vc_path='" + $vmvcpath + "', vmx_path='" + $vmxpath + "', scsi_hw='" + $vmscsi + "', notes='" + $vmnotes + "';"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| # Work out OSID if VM is up
| |
| if ($vm.PowerState -eq "PoweredOn") {
| |
| $osname = (Get-VMGuest -VM $vm).OSFullName
| |
| if (!$osname) {
| |
| Log-Error ("Couldn't update OS for VM: " + $vm.Name + " in db")
| |
| Continue
| |
| }
| |
| $osid = $dbOSs |?{$_.guest_name -eq $osname} | Select -ExpandProperty osid
| |
| if (!$osid) {
| |
| # No existing db record for OS, add a new one
| |
| ExecuteMySQLNonQuery("INSERT INTO os (guest_name) VALUES ('" + $osname + "');")
| |
| $osid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
| $dbOSs = ExecuteMySQLQuery("SELECT osid, guest_name FROM os;")
| |
| }
| |
| ExecuteMySQLNonQuery("UPDATE vm_ext SET osid=" + $osid + " WHERE vmid=" + $vmid + ";")
| |
| }
| |
| }
| |
|
| |
| # Mark VM's that no longer exist
| |
| $dbVMs = ExecuteMySQLQuery("SELECT vmid, name FROM vm WHERE vcid = " + $vc.vcid + " AND exist=1;")
| |
| foreach ($vm in $dbVMs) {
| |
| if (!($vcVMs |?{$_.Name -eq $vm.name})) {
| |
| if (!$vm.vmid) {
| |
| Continue
| |
| }
| |
| Log ("HIDE: " + $vm.name)
| |
| ExecuteMySQLNonQuery("UPDATE vm SET exist=0 WHERE vmid=" + $vm.vmid + ";")
| |
| }
| |
| }
| |
| }
| |
|
| |
| Disconnect-VIServer -Server $VCconn -Confirm:$false
| |
| }
| |
| | |
| #Lab Manager ---------------------------------------------------------------------------------------------------------------------------------
| |
| if ($UpdateLM) {
| |
| # Connect to Lab Manager server
| |
| try {
| |
| Log("Connecting to " + $LabMgrSvr)
| |
| Ignore-SslErrors
| |
| $labManager = Connect-LabManager -server $LabMgrSvr -credential $cred
| |
| } catch {
| |
| Log-Error("Unable to connect to Lab Manager - " + $_)
| |
| $Major_Error += 1
| |
| }
| |
| | |
| if ($labManager) {
| |
| # Get List of VM's
| |
| Log("Getting Lab Manager VM info...")
| |
| $lmVMs = @()
| |
| $lmConfigs = $labManager.ListConfigurations(1)
| |
| foreach ($config in $lmConfigs) {
| |
| if ($config.isDeployed) {
| |
| $confVMs = $labManager.ListMachines($config.id)
| |
| foreach ($vm in $confVMs) {
| |
| if ($vm.isDeployed) {
| |
| $VMrow = "" | Select Path, ConfigOrg, ConfigName, VMname, VMextIP
| |
| $VMrow.Path = $config.bucketName + "\\\\" + $config.name
| |
| $VMrow.ConfigOrg = $config.bucketName
| |
| $VMrow.ConfigName = $config.name
| |
| $VMrow.VMname = $vm.name
| |
| $VMrow.VMextIP = $vm.externalIP
| |
| $lmVMs = $lmVMs + $VMrow
| |
| }
| |
| }
| |
| }
| |
| }
| |
|
| |
| foreach ($vm in $lmVMs) {
| |
| # Get VM's db ID
| |
| $query = "SELECT vmid FROM vm JOIN vm_ext USING (vmid) WHERE vc_path LIKE('%" + $vm.ConfigOrg + "\\\\" + $vm.ConfigName + "%') AND name LIKE('%" + $vm.VMname + "');"
| |
| $vmid = ExecuteMySQLScalar($query)
| |
| if (!$vmid) {
| |
| #Log ($query)
| |
| Log-Error("Unable to find db id for Labs VM: " + $vm.ConfigName + "\" + $vm.VMname)
| |
| Continue
| |
| }
| |
|
| |
| $query = "INSERT INTO vm_nic (vmid, num, type) VALUES (" + $vmid + ", 0, 'Labs NAT') "
| |
| $query += "ON DUPLICATE KEY UPDATE vnicid=LAST_INSERT_ID(vnicid);"
| |
| ExecuteMySQLNonQuery($query)
| |
|
| |
| # Get vnicid from last query
| |
| $vnicid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
| |
|
| |
| # Find related IP(s)
| |
| $query = "INSERT INTO vm2ip (vmid, vnicid, ip) VALUES (" + $vmid + ", " + $vnicid + ", INET_ATON('" + $vm.VMextIP + "')) "
| |
| $query += "ON DUPLICATE KEY UPDATE ip=INET_ATON('" + $vm.VMextIP + "');"
| |
| ExecuteMySQLNonQuery($query)
| |
| }
| |
| }
| |
| }
| |
| | |
| | |
| # Post Amble =================================================================================================================================
| |
| | |
| if ($Major_Error) {
| |
| if ($ErrorLog.Length) {
| |
| if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=" + $Major_Error + ", warn=" + $ErrorLog.Length + " WHERE runid=$runid;")}
| |
| Log("Script encountered " + $ErrorLog.Count + " minor errors and " + $Major_Error + " severe errors (VC connect fails)!!!")
| |
| } else {
| |
| if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=" + $Major_Error + ", warn=0 WHERE runid=$runid;")}
| |
| Log("Script encountered " + $Major_Error + " severe errors (VC connect fails)!!!")
| |
| }
| |
| } else {
| |
| if ($ErrorLog.Length) {
| |
| if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=" + $Major_Error + ", warn=" + $ErrorLog.Length + " WHERE runid=$runid;")}
| |
| Log("Script encountered " + $ErrorLog.Count + " minor errors!")
| |
| } else {
| |
| if ($UpdateScriptRun) {ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=0, warn=0 WHERE runid=$runid;")}
| |
| Log("Script encountered no errors.")
| |
| }
| |
| }
| |
| | |
| DisconnectMySQL
| |
| Stop-Transcript
| |
| </source>
| |
| | |
| === 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>
| |