Difference between revisions of "Virtual Infrastructure DB Project"

From vwiki
Jump to navigation Jump to search
(Initial creation)
 
m (→‎Matrix: Minor correction)
 
(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>

Latest revision as of 10:37, 19 December 2011

This started life as small script to collect VM info from 5 or 6 vCentre's (that were a mix of v2.5 and v4, and weren't linked together), so that VM's could be easily found without having to search through each one. There are essentially 3 components to it...

  1. Data collection scripts (run from a Windows desktop)
    • A daily PowerCLI script that collects data from all vCenters (and updates the database)
    • An intra-day PowerCLI script (say every 15mins) that collects basic cluster status data (and updates the database)
  2. MySQL database (run on an Ubuntu VM)
  3. PHP front end (delivered by Apache running on same Ubuntu VM)

Releases

Matrix

Release Database Data Collection Presentation (Main) Presentation (Other)
Version 1 initial db-base-data-v1.0.ps1 db-esx-check-v4.ps1 index.php v1 status.php v1 alpha.css v1 vc-detail.php v1, cluster-detail.php v1, vm-detail.php v1
Version 1.1 db-base-data-v1.1.ps1
Version 1.2 update v1.2 db-base-data-v1.2.ps1 index.php v1.1 snaps.php v1
Version 1.2.1 db-base-data-v1.2.1.ps1
Version 1.2.2 db-base-data-v1.2.2.ps1 db-esx-check-v5.ps1
Version 2.0 update v2.0 db-base-data-v2.0.ps1 db-base-data-sub-v2.0.ps1 index.php v2.0 run-errors.php v2, vm-detail.php v2, snaps.php v2
Version 2.1 db-base-data-sub-v2.1.ps1
Version 2.2 update v2.2 db-base-data-sub-v2.2.ps1 index.php v2.2 vmtools.php v2, vnics.php v2
Version 2.3 db-base-data-sub-v2.3.ps1 index.php v2.3 esxs.php v2
Version 2.4 update v2.4 db-base-data-sub-v2.4.ps1 index.php v2.4 alpha.css v2 vapps.php v2
Version 2.5 db-base-data-v2.1.ps1 db-base-data-sub-v2.5.ps1 db-esx-check-v6.ps1 run-errors.php v2.1, vm-detail.php v2.2, vmtools.php v2.1
Version 2.6 db-base-data-sub-v2.6.ps1 capman.php v2.0
Version 2.7 update v2.7 db-base-data-sub-v2.7.ps1 capman.php v2.1
Version 2.8 update v2.8 db-base-data-v2.2.ps1 db-base-data-sub-v2.8.ps1 vc-detail.php v2.2, run-errors.php v2.2
Version 2.9 update v2.9 db-base-data-v2.3.ps1 db-base-data-sub-v2.9.ps1 alpha.css v3 vm-detail.php v2.3, ds-info.php v2.1


Change Log

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)

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

Version 1.2.1

  • 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)

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

Version 2.0

  • 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

Version 2.1

  • 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)

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

Version 2.3

  • Data Collection (db-base-data-sub)
    • Added ESX NIC firmware version
  • Presentation (index)
    • Added links
  • Presentation (esxs)
    • Initial

Version 2.4

  • 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

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
Retrieved from ‘https://vwiki.co.uk/index.php?title=Virtual_Infrastructure_DB_Project&oldid=1591’