Virtual Infrastructure DB Project

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

• Standard include files...
  • Standard.ps1 - Basic PowerShell helper (logging etc)
  • PowerCLI-helper-v1.1.ps1 - PowerCLI helper (provides API bug workarounds)
  • DS-LUN-v4.ps1 - PowerCLI Datastore helper (LUN's etc)
  • getsnapshotsize-1.ps1 - PowerCLI Get_Snapshot bug workaround

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