VI-DB db-base-data-v2.3.ps1
Jump to navigation
Jump to search
<# ========================================================================================================================================
Database Base Data Getter
=========================================================================================================================================
Simon Strutt Jan 2011
=========================================================================================================================================
Version 1
- Initial creation
Version 1.1 - Mar 2011
- 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
Version 1.2 - Mar 2011
- 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
Version 1.2.1 - Mar 2011
- 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)
- Bugfix: ESX name wrong when known by VC with IP Address (name now extracted with regex to handle name being IP)
Version 1.2.2 - Apr 2011
- 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 - Jun 2011
- Changed to Async/PS Jobs in order to run against multiple vCentre's simultaneously
- vCentre data mining moved to sub-script
- 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 ****
Version 2.1 - Sep 2011
- Added check for sub-script fail
Version 2.2 - Nov 2011
- Allow db override via command line param
Version 2.3 - Dec 2011
- Bugfix: Sub-script fail check didn't catch single VC run fails (SQL query error)
Future improvements
- Add daily capacity report
- Bugfix: ESX's moving clusters should be handled by main INSERT UPDATE, remove/rework ESX duplicate bugfix - maybe catch as major error?
- Add HA settings (isolation response, admin control, etc)
- Add VM CBT
- Calculate total VM disk usage - not sure really required
- Add state change datetime stamp
- Catch all Powershell execptions in order to increment counter (add errors to db at later date)
- Add who did what (event log extracting)
==========================================================================================================================================#>
$start = Get-Date
$UserFile = "User.fil"
$PassFile = "Pass.fil" # Encrypted file to store password in
$Logfile = "db-base-data.log"
$LogStamp = "Time" # Log timestamp format (Duration or Time (stamp))
$UpdateScriptRun = 1 # Log to database
$Debug = 0 # Run in debug mode (use test db etc)
# Include library files
. .\lib\Standard-v4.ps1
#. .\lib\PowerCLI-helper-v1.1.ps1
#. .\temp\DS-LUN-v4.ps1
#. .\lib\getsnapshotsize-1.ps1
. .\lib\MySQL-v1.ps1
$SubScriptName = "db-base-data-sub-v2.9.ps1"
# DB connection stuff...
$DBuser = 'powercli'
$DBpass = 'powercli'
$database = 'vi'
$MySQLHost = '192.168.196.128'
$MaxRunningJobs = 4
$LaunchDelay = 15 # Pause following a job launch (launched jobs tend to hammer the CPU initially)
$JobPollThrottle = 1 # Period of sleep between job status polls
$LabMgrSvr = "ukb-sr-lbmgr-10"
$UpdateLM = 1
$Major_Error = 0 # Major error counter
$ErrorLog = @() # Minor error tracker
if ($Debug) {
$database = 'vi_test'
$Logfile = "db-base-data-debug.log"
$LogNonQueries = 1
}
Start-Transcript -Path $Logfile
Log "Started script run at $start"
$ScriptVer = [regex]::matches(($MyInvocation.MyCommand.Name), "(?<=v)[0-9]+(\.[0-9])+(?=.)")
$SubScriptVer = [regex]::matches($SubScriptName, "(?<=v)[0-9]+(\.[0-9])+(?=.)")
Log ("Script version is $ScriptVer, sub-script ver $SubScriptVer (" + (Get-PowerCLIversion).UserFriendlyVersion + " / Powershell " + (get-host).Version.ToString() + ")")
if ($args[0]) {
Log ("Database changed from [$database] to [" + $args[0] + "] by command line param.")
$database = $args[0]
}
Log ("Database : $database")
Log ("Max running jobs : $MaxRunningJobs")
Log ("Launch delay : $LaunchDelay secs")
Log ("Job poll interval: $JobPollThrottle secs")
# =============================================================================================
# 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
}
# =============================================================================================
# Other funcs
function Log-Error($text, $vcid='NULL', $sev=3) {
# if (!$ErrorLog) {
# $ErrorLog = @()
# }
$script:ErrorLog += $text
Log ("ERROR: " + $text)
Log ("Error count now " + $ErrorLog.Length)
if ($UpdateScriptRun) {
ExecuteMySQLNonQuery("INSERT INTO script_run_error (runid, sev, vcid, text) VALUES ($runid, $sev, $vcid, '" + (EscQuote $text) + "');")
}
}
# ========================================================================================================================================
# Pre Amble ==============================================================================================================================
# Check running in x86 environment
if ($env:PROCESSOR_ARCHITECTURE -notmatch 'x86') {
Log-Error -text "Script in not running in 32-bit mode, Powershell will crash if running jobs against vSphere VC's, exiting..." -sev 1
Stop-Transcript
Exit
}
# Connect to MySQL database
$SQLconn = ConnectMySQL $DBuser $DBpass $MySQLHost $database
# Create script_run entry
if ($UpdateScriptRun) {
ExecuteMySQLNonQuery("INSERT INTO script_run (start, version) VALUES (NOW(), '$ScriptVer ($SubScriptVer)');")
$runid = ExecuteMySQLScalar("SELECT LAST_INSERT_ID();")
Log("Script run ID is $runid")
}
# Get list of VC's (checks the SQL connection is in good working order as well, additional columns for job management)
$VCs = @()
$SQLres = ExecuteMySQLQuery("SELECT vcid, name FROM vc;")
foreach ($res in $SQLres) {
if (!$res.vcid) {
Continue
}
$vc = "" | Select vcid, name, job, state
$vc.vcid = $res.vcid
$vc.name = $res.name
$vc.state = "To Start"
$VCs += $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)
# Connect to VC's in order to confirm user/pass is correct
foreach ($vc in $VCs) {
# Connect to vCentre
try {
Log("Testing credentials - 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.")
Log-Error -text "vCentre invalid login error, script halt to prevent potential account lockout." -vcid $vc.vcid -sev 1
ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=99 WHERE runid=$runid;")
DisconnectMySQL
Stop-Transcript
Exit
} catch {
Log-Error -text (EscQuote ("Unable to connect to vCentre - " + $_)) -vcid $vc.vcid -sev 2
$Major_Error += 1
Continue
}
Log("Credentials validated successfully")
Break
}
# Disconnect any existing VI Server sessions (otherwise you can end up with duplicate data)
if ($DefaultVIServers.Count) {
Log("Disconnect all existing vCentre server connections...")
Disconnect-VIServer -Server * -Force -Confirm:$false
}
# Business Loop ============================================================================================================================
# Get current directory to pass to jobs
$pwd = Get-Location
$JobsRunning = 0
While (1) {
# Launch control
if ($VCs |?{$_.state -eq "To Start"}) { # Anymore jobs to start?
if ($JobsRunning -lt $MaxRunningJobs) { # Capacity to start a another job?
#Log("Jobs still to start, Jobs running: $JobsRunning, Max Running Jobs: $MaxRunningJobs")
foreach ($vc in $VCs) {
# Find next job to start
if ($vc.state -eq "To Start") {
$vars = ($vc.name, $vc.vcid, $cred, $DBuser, $DBpass, $database, $MySQLHost, $runid, $pwd)
Log ("Starting job for " + $vc.name)
$vc.job = Start-Job -FilePath $SubScriptName -InitializationScript $JobScript -Name $vc.name -InputObject $vars
$vc.state = $vc.job.JobStateInfo.state
$JobsRunning += 1
Start-Sleep $LaunchDelay
Break
}
}
}
}
# Landing handling
foreach ($vc in $VCs) {
if (($vc.state -ne "To Start") -and ($vc.state -ne $vc.job.JobStateInfo.state)) {
Log ($vc.name + " job state now " + $vc.job.JobStateInfo.state)
$vc.state = $vc.job.JobStateInfo.state
$JobsRunning -= 1
if ($vc.state.ToString() -eq "Completed") {
Log ($vc.name + " writing log to db-base-data-" + $vc.name + ".log")
# Nasty logging handling (Receive-Job StdOut to console only, can't redirect to file, can only catch StdErr to file)
Stop-Transcript
Start-Transcript -Path ("db-base-data-" + $vc.name + ".log")
Receive-Job -Id $vc.job.Id
Stop-Transcript
Start-Transcript -Path $Logfile -Append
} else {
Log-Error -text ($vc.name + " data collection " + $vc.state) -vcid $vc.vcid -sev 2
Log ($vc.name + " writing log to db-base-data-" + $vc.name + ".log")
Stop-Transcript
Start-Transcript -Path ("db-base-data-" + $vc.name + ".log")
Receive-Job -Id $vc.job.Id
Stop-Transcript
Start-Transcript -Path $Logfile -Append
}
$VCs | Select -Property Name, State
}
#Log ($vc.name + " job state is " + $vc.job.JobStateInfo.state)
}
#Log ("Jobs running: $JobsRunning, Max Running Jobs: $MaxRunningJobs")
# No jobs running / none still to start?
if (($JobsRunning -eq 0) -and !($VCs |?{$_.state -eq "To Start"})) {
Log ("All jobs completed")
Break
}
Start-Sleep $JobPollThrottle
}
#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 -text ("Unable to connect to Lab Manager - " + $_) -vcid 0 -sev 2
$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 -text ("Unable to find db id for Labs VM: " + $vm.ConfigName + "\" + $vm.VMname) -vcid 0
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 =================================================================================================================================
foreach ($vc in $VCs) {
if (ExecuteMySQLScalar("SELECT COUNT(*) AS fail FROM script_run_error WHERE vcid=" + $vc.vcid + " AND runid=$runid AND sev=0;")) {
Log("Script run for " + $vc.name + " exited cleanly")
} else {
Log-Error -text ("Script run on " + $vc.name + " did not exit cleanly") -vcid $vc.vcid -sev 1
}
}
# Summarise error reporting
$RunErrors = ExecuteMySQLQuery("SELECT SUM(IF(sev=1 or sev=2,1,0)) AS major, SUM(IF(sev=3,1,0)) AS minor FROM script_run_error WHERE runid=$runid;")
Log("Summary error report: " + $RunErrors[1].major + " major errors, " + $RunErrors[1].minor + " minor errors")
ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=" + $RunErrors[1].major + ", warn=" + $RunErrors[1].minor + " WHERE runid=$runid;")
DisconnectMySQL
Stop-Transcript