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)

Database

CREATE DATABASE vi;

# 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
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));

# VM tables
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));

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

# ESX tables
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));

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

CREATE TABLE esx2ds (esxid SMALLINT UNSIGNED, dsid SMALLINT UNSIGNED, PRIMARY KEY (esxid, dsid));
CREATE TABLE esx2nw (esxid SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, PRIMARY KEY (esxid, nwid));

# Datastore and VMDK tables
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));

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

# Networking tables
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));

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

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

# Other tables
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();

Data Collection

Main Daily Script

db-base-data-v1.0.ps1

Intra-Day Status

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