VI-DB db-base-data-v2.0.ps1

<# ========================================================================================================================================
  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)
 - 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 ****
 

 Future improvements
 - Also check vyata VM's (NICs not showing!!)
 - 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 = 1                              # 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.0.ps1"

# DB connection stuff...
$DBuser = 'powercli' 
$DBpass = 'powercli' 
$database = 'vi'
$MySQLHost = '192.168.196.128' 

$MaxRunningJobs = 4
$LaunchDelay = 20                      # Pause following a job launch (launched jobs tend to hammer the CPU initially, which crashes jobs if too many at the same time)
$JobPollThrottle = 5                   # Period of sleep between job status polls

$LabMgrSvr = "labmanager"

$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 =================================================================================================================================

# Summarise error reporting
$RunErrors = ExecuteMySQLQuery("SELECT SUM(IF(sev=2,1,0)) AS sev2, SUM(IF(sev=3,1,0)) AS sev3 FROM script_run_error WHERE runid=$runid;")
Log("Summary error report: " + $RunErrors[1].sev2 + " major errors, " + $RunErrors[1].sev3 + " minor errors")
ExecuteMySQLNonQuery("UPDATE script_run SET end=NOW(), error=" + $RunErrors[1].sev2 + ", warn=" + $RunErrors[1].sev3 + " WHERE runid=$runid;")

DisconnectMySQL
Stop-Transcript