VI-DB db-base-data-v2.1.ps1
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
<# ========================================================================================================================================
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
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
- Add VM custom attributes
- Add VM resource limits / shares
- 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.5.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, which crashes jobs if too many at the same time)
$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
# Flags - most have moved to sub-script
$UpdateLM = 0 # Lab Manager (update VM external IPs)
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() + ")")
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();")
}
# 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
}
}
#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 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