Creating a vSphere Change Management DB for free – Step 2 of 4

In the last blog we created a SQL database in which to store data from PowerCLI in order to create a vSphere Change Management Database for free. If you missed it, check it out here:

Step 1 – Creating the SQL Database

In this second post we are now going to insert data into the database and configure it to run on a schedule. To do this we are first going to use a PowerCLI cmdlet with variables to store the vSphere data, then we will use ForEach to insert each row of data into a corresponding SQL table row.

Here is an example of inserting the data from Get-Datastore into SQL:

$Datastores = Get-Datastore | Select *
# Inserting each row
ForEach ($Datastore in $Datastores)
{
$DatastoreID = $Datastore.Id
$Name = $Datastore.Name
$CapacityGB = $Datastore.CapacityGB -as [int]
$FreeSpaceGB = $Datastore.FreeSpaceGB -as [int]
$State = $Datastore.State
$Type = $Datastore.Type
$FileSystemVersion = $Datastore.FileSystemVersion -as [int]
$Accessible = $Datastore.Accessible
$StorageIOControlEnabled = $Datastore.StorageIOControlEnabled
$CongestionThresholdMillisecond = $Datastore.CongestionThresholdMillisecond -as [int]
$ParentFolderId = $Datastore.ParentFolderId
$ParentFolder = $Datastore.ParentFolder
$DatacenterId = $Datastore.DatacenterId
$Datacenter = $Datastore.Datacenter
$Uid = $Datastore.Uid
# Creating SQL INSERT
$SQLDatastoreInsert = "USE $SQLDatabase
INSERT INTO Datastores (LastUpdated, DatastoreID, Name, CapacityGB, FreeSpaceGB, State, Type, FileSystemVersion, Accessible, 
StorageIOControlEnabled, CongestionThresholdMillisecond, ParentFolderId, ParentFolder, DatacenterId, Datacenter, Uid)
VALUES('$LastUpdated', '$DatastoreID', '$Name', '$CapacityGB', '$FreeSpaceGB', '$State', '$Type', '$FileSystemVersion', '$Accessible', 
'$StorageIOControlEnabled', '$CongestionThresholdMillisecond', '$ParentFolderId', '$ParentFolder', '$DatacenterId', '$Datacenter', '$Uid');"
# Running the INSERT query
invoke-sqlcmd -query $SQLDatastoreInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
} 

The tricky part is ensuring the SQL query is in the correct order and variable defined as the correct data type. I also have to define the variables first before creating the SQL INSERT statement, otherwise, it doesn’t work. You can see that I force the variable to an integer as needed and I break the SQL query up into multiple lines to make it easier to manage. But this is just 1 example, how do we insert the data for all the major settings? Copy and paste the below script to do just that:

##############################################
# Setting the variables for connecting to the SQL database & vCenter
##############################################
# vSphere server & authentication
$vCenterServer = "192.168.0.61"
$vCenterUsername = "administrator@lab.local"
$vCenterPassword = "Password123!"
# SQL server, database & authentication
$SQLInstance = ".\SQLEXPRESS"
$SQLDatabase = "vSphereChangeControlv1"
$SQLUsername = "sa"
$SQLPassword = "Password123!"
############################################################################################
# Nothing to change below this line, comments provided if you need/want to change anything
############################################################################################
#######################
# Checking to see if the PowerCLI module is already installed, if not installing it
#######################
$PowerCLIModuleCheck = Get-Module -ListAvailable VMware.PowerCLI
if ($PowerCLIModuleCheck -eq $null)
{
write-host "PowerCLI Module Not Found - Installing"
# Trusting PS Gallery to remove prompt on install
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
# Installing module
Install-Module -Name VMware.PowerCLI –Scope AllUsers -Confirm:$false
}
#######################
# Importing the PowerCLI module
#######################
Import-Module VMware.PowerCLI
##############################################
# Checking to see if the SqlServer module is already installed, if not installing it
##############################################
$SQLModuleCheck = Get-Module -ListAvailable SqlServer
if ($SQLModuleCheck -eq $null)
{
write-host "SqlServer Module Not Found - Installing"
# Not installed, trusting PS Gallery to remove prompt on install
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
# Installing module
Install-Module -Name SqlServer –Scope AllUsers -Confirm:$false -AllowClobber
}
##############################################
# Importing the SqlServer module
##############################################
Import-Module SqlServer
##############################################
# Connecting to vCenter
##############################################
Try 
{
connect-viserver -Server $vCenterServer -User $vCenterUsername -Password $vCenterPassword 
$vCenterAuthentication = "PASS"
}
Catch 
{
Write-Host $_.Exception.ToString()
$error[0] | Format-List -Force
$vCenterAuthentication = "FAIL"
}
##############################################
# Setting the current dates (for use in each SQL row inserted)
##############################################
$LastUpdated = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date)
##############################################
# Inserting into VMs SQL Table
##############################################
# Thanks to Alan Renouf for how to get toolsstatus @ https://blogs.vmware.com/PowerCLI/2011/11/vm-tools-and-virtual-hardware-versions.html
New-VIProperty -Name ToolsVersion -ObjectType VirtualMachine -ValueFromExtensionProperty 'Config.tools.ToolsVersion'  -Force 
New-VIProperty -Name ToolsVersionStatus -ObjectType VirtualMachine -ValueFromExtensionProperty 'Guest.ToolsVersionStatus' -Force
$VMs = Get-VM | Select *
$VMGuestInfo = Get-VM | Get-VMGuest | Select *
# Inserting each row
ForEach ($VM in $VMs)
{
$VMID = $VM.Id
$Name = $VM.Name
$PowerState = $VM.PowerState
$Notes = $VM.Notes
$Guest = $VM.Guest
$NumCpu = $VM.NumCpu -as [int]
$CoresPerSocket = $VM.CoresPerSocket -as [int]
$MemoryGB = $VM.MemoryGB -as [int]
$VMHostId = $VM.VMHostId
$VMHost = $VM.VMHost
$VApp = $VM.VApp
$FolderId = $VM.FolderId
$Folder = $VM.Folder
$ResourcePoolId = $VM.ResourcePoolId
$ResourcePool = $VM.ResourcePool
$HARestartPriority = $VM.HARestartPriority
$HAIsolationResponse = $VM.HAIsolationResponse
$DrsAutomationLevel = $VM.DrsAutomationLevel
$VMSwapfilePolicy = $VM.VMSwapfilePolicy
$VMResourceConfiguration = $VM.VMResourceConfiguration
$Version = $VM.Version
$UsedSpaceGB = $VM.UsedSpaceGB -as [int]
$ProvisionedSpaceGB = $VM.ProvisionedSpaceGB -as [int]
$DatastoreIdList = $VM.DatastoreIdList
$ExtensionData = $VM.ExtensionData
$CustomFields = $VM.CustomFields
$Uid = $VM.Uid
$PersistentId = $VM.PersistentId
$ToolsVersion = $VM.ToolsVersion
$ToolsVersionStatus = $VM.ToolsVersionStatus
# Pulling fields from VMGuestInfo to consolidate into a single table
$VMGuest = $VMGuestInfo | Where-Object {$_.Vmid -eq $VMID}
$OSFullName = $VMGuest.OSFullName
$IPAddress = $VMGuest.IPAddress
$State = $VMGuest.State
$Hostname = $VMGuest.HostName
$Nics = $VMGuest.Nics
$GuestId = $VMGuest.GuestId
$RuntimeGuestId = $VMGuest.RuntimeGuestId
$GuestFamily = $VMGuest.GuestFamily
# Diagnostic output
# write-host "SQL Insert for VM:$Name"
# Creating SQL INSERT
$SQLVMInsert = "USE $SQLDatabase
INSERT INTO VMs (LastUpdated, VMID, Name, PowerState, Notes, Guest, NumCpu, CoresPerSocket, MemoryGB, VMHostId, VMHost, VApp, FolderId, 
Folder, ResourcePoolId, ResourcePool, HARestartPriority, HAIsolationResponse, DrsAutomationLevel, VMSwapfilePolicy, VMResourceConfiguration, 
Version, UsedSpaceGB, ProvisionedSpaceGB, DatastoreIdList, ExtensionData, CustomFields, Uid, PersistentId, OSFullName, 
IPAddress, State, Hostname, Nics, GuestID, RuntimeGuestId, ToolsVersion, ToolsVersionStatus, GuestFamily)
VALUES('$LastUpdated', '$VMID', '$Name', '$PowerState', '$Notes', '$Guest', '$NumCpu', '$CoresPerSocket', '$MemoryGB', '$VMHostId', '$VMHost', '$VApp', '$FolderId', 
'$Folder', '$ResourcePoolId', '$ResourcePool', '$HARestartPriority', '$HAIsolationResponse', '$DrsAutomationLevel', '$VMSwapfilePolicy', '$VMResourceConfiguration', 
'$Version', '$UsedSpaceGB', '$ProvisionedSpaceGB', '$DatastoreIdList', '$ExtensionData', '$CustomFields', '$Uid', '$PersistentId', '$OSFullName', 
'$IPAddress', '$State', '$Hostname', '$Nics', '$GuestId', '$RuntimeGuestId', '$ToolsVersion', '$ToolsVersionStatus', '$GuestFamily');"
# Running the INSERT query
invoke-sqlcmd -query $SQLVMInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# End of per VM action below
}
# End of per VM action above
##############################################
# Inserting into VMDisks SQL Table
##############################################
$VMHardDisks = Get-VM | Get-HardDisk | Select *
# Inserting each row
ForEach ($VMHardDisk in $VMHardDisks)
{
$VMID = $VMHardDisk.ParentId
$Parent = $VMHardDisk.Parent
$DiskID = $VMHardDisk.Id
$Name = $VMHardDisk.Name
$Filename = $VMHardDisk.Filename
$CapacityGB = $VMHardDisk.CapacityGB -as [int]
$Persistence = $VMHardDisk.Persistence
$DiskType = $VMHardDisk.DiskType
$StorageFormat = $VMHardDisk.StorageFormat
# Creating SQL INSERT
$SQLDiskInsert = "USE $SQLDatabase
INSERT INTO VMDisks (LastUpdated, VMID, Parent, DiskID, Name, Filename, CapacityGB, Persistence, DiskType, StorageFormat)
VALUES('$LastUpdated', '$VMID', '$Parent', '$DiskID', '$Name', '$Filename', '$CapacityGB', '$Persistence', '$DiskType', '$StorageFormat');"
# Running the INSERT query
invoke-sqlcmd -query $SQLDiskInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into VMDiskUsage SQL Table
##############################################
# Thanks to Alan Renouf for the example script which I used as a base: http://www.virtu-al.net/2010/01/27/powercli-virtual-machine-disk-usage/
$AllVMsView = Get-View -ViewType VirtualMachine | Where {-not $_.Config.Template}
$VMGuestDiskInfo = $AllVMsView | Select *, @{N="NumDisks";E={@($_.Guest.Disk.Length)}} | Sort-Object -Descending NumDisks
# Populating the array
ForEach ($VM in $VMGuestDiskInfo){
# Starting disk number at 0 for each VM
 $DiskNum = 0
 Foreach ($Disk in $VM.Guest.Disk){
    $VMID = $VM.MoRef
    $Name = $VM.name
    $DiskNum = $DiskNum -as [INT]
    $DiskPath = $Disk.DiskPath
    $DiskCapacityGB = ([math]::Round($disk.Capacity/ 1GB)) -as [INT]
    $DiskFreeSpaceGB = ([math]::Round($disk.FreeSpace / 1GB)) -as [INT]
    $DiskCapacityMB = ([math]::Round($disk.Capacity/ 1MB)) -as [INT]
    $DiskFreeSpaceMB = ([math]::Round($disk.FreeSpace / 1MB)) -as [INT]
# Creating SQL INSERT
$SQLVMGuestDiskInsert = "USE $SQLDatabase
INSERT INTO VMDiskUsage (LastUpdated, VMID, Name, DiskNum, DiskPath, DiskCapacityGB, DiskFreeSpaceGB, DiskCapacityMB, DiskFreeSpaceMB)
VALUES('$LastUpdated', '$VMID', '$Name', '$DiskNum', '$DiskPath', '$DiskCapacityGB', '$DiskFreeSpaceGB', '$DiskCapacityMB', '$DiskFreeSpaceMB');"
# Running the INSERT query
invoke-sqlcmd -query $SQLVMGuestDiskInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# Incrementing disk number
$DiskNum++
 } 
}
##############################################
# Inserting into VMNICs SQL Table
##############################################
$VMNetworkAdapters = Get-VM | Get-NetworkAdapter | Select *
# Inserting each row
ForEach ($VMNetworkAdapter in $VMNetworkAdapters)
{
$VMID = $VMNetworkAdapter.ParentId
$Parent = $VMNetworkAdapter.Parent
$NICID = $VMNetworkAdapter.Id
$Name = $VMNetworkAdapter.Name
$MacAddress = $VMNetworkAdapter.MacAddress
$NetworkName = $VMNetworkAdapter.NetworkName
$ConnectionState = $VMNetworkAdapter.ConnectionState
$WakeOnLanEnabled = $VMNetworkAdapter.WakeOnLanEnabled
$Type = $VMNetworkAdapter.Type
# Creating SQL INSERT
$SQLNICInsert = "USE $SQLDatabase
INSERT INTO VMNICs (LastUpdated, VMID, Parent, NICID, Name, MacAddress, NetworkName, ConnectionState, WakeOnLanEnabled, Type)
VALUES('$LastUpdated', '$VMID', '$Parent', '$NICID', '$Name', '$MacAddress', '$NetworkName', '$ConnectionState', '$WakeOnLanEnabled', '$Type');"
# Running the INSERT query
invoke-sqlcmd -query $SQLNICInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into Datastores SQL Table
##############################################
$Datastores = Get-Datastore | Select *
# Inserting each row
ForEach ($Datastore in $Datastores)
{
$DatastoreID = $Datastore.Id
$Name = $Datastore.Name
$CapacityGB = $Datastore.CapacityGB -as [int]
$FreeSpaceGB = $Datastore.FreeSpaceGB -as [int]
$State = $Datastore.State
$Type = $Datastore.Type
$FileSystemVersion = $Datastore.FileSystemVersion -as [int]
$Accessible = $Datastore.Accessible
$StorageIOControlEnabled = $Datastore.StorageIOControlEnabled
$CongestionThresholdMillisecond = $Datastore.CongestionThresholdMillisecond -as [int]
$ParentFolderId = $Datastore.ParentFolderId
$ParentFolder = $Datastore.ParentFolder
$DatacenterId = $Datastore.DatacenterId
$Datacenter = $Datastore.Datacenter
$Uid = $Datastore.Uid
# Creating SQL INSERT
$SQLDatastoreInsert = "USE $SQLDatabase
INSERT INTO Datastores (LastUpdated, DatastoreID, Name, CapacityGB, FreeSpaceGB, State, Type, FileSystemVersion, Accessible, 
StorageIOControlEnabled, CongestionThresholdMillisecond, ParentFolderId, ParentFolder, DatacenterId, Datacenter, Uid)
VALUES('$LastUpdated', '$DatastoreID', '$Name', '$CapacityGB', '$FreeSpaceGB', '$State', '$Type', '$FileSystemVersion', '$Accessible', 
'$StorageIOControlEnabled', '$CongestionThresholdMillisecond', '$ParentFolderId', '$ParentFolder', '$DatacenterId', '$Datacenter', '$Uid');"
# Running the INSERT query
invoke-sqlcmd -query $SQLDatastoreInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into PortGroups SQL Table
##############################################
$VirtualPortGroups = Get-VirtualPortGroup | Select *
# Inserting each row
ForEach ($VirtualPortGroup in $VirtualPortGroups)
{
$VirtualSwitchId = $VirtualPortGroup.VirtualSwitchId
$Name = $VirtualPortGroup.Name
$VirtualSwitch = $VirtualPortGroup.VirtualSwitch
$VirtualSwitchName = $VirtualPortGroup.VirtualSwitchName
$PortGroupKey = $VirtualPortGroup.Key
$VLanId = $VirtualPortGroup.VLanId -as [int]
$VMHostId = $VirtualPortGroup.VMHostId
$VMHostUid = $VirtualPortGroup.VMHostUid
$Uid = $VirtualPortGroup.Uid
# Creating SQL INSERT
$SQLPortGroupInsert = "USE $SQLDatabase
INSERT INTO PortGroups (LastUpdated, VirtualSwitchId, Name, VirtualSwitch, VirtualSwitchName, PortGroupKey, VLanId, VMHostId, VMHostUid, Uid)
VALUES('$LastUpdated', '$VirtualSwitchId', '$Name', '$VirtualSwitch', 'VirtualSwitchName', '$PortGroupKey', '$VLanId', '$VMHostId', '$VMHostUid', '$Uid');"
# Running the INSERT query
invoke-sqlcmd -query $SQLPortGroupInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into Hosts SQL Table 
##############################################
$Hosts = Get-VMHost | Select Id,Name,State,ConnectionState,PowerState,NumCpu,CpuTotalMhz,CpuUsageMhz,MemoryTotalGB,MemoryUsageGB,ProcessorType,HyperthreadingActive,TimeZone,Version,Build
# Inserting each row
ForEach ($ESXiHost in $Hosts)
{
$HostID = $ESXiHost.Id
$Name = $ESXiHost.Name
$State = $ESXiHost.State
$ConnectionState = $ESXiHost.ConnectionState
$PowerState = $ESXiHost.PowerState
$NumCpu = $ESXiHost.NumCpu -as [int]
$CpuTotalMhz = $ESXiHost.CpuTotalMhz -as [int]
$CpuUsageMhz = $ESXiHost.CpuUsageMhz -as [int]
$MemoryTotalGB = $ESXiHost.MemoryTotalGB -as [int]
$MemoryUsageGB = $ESXiHost.MemoryUsageGB -as [int]
$ProcessorType = $ESXiHost.ProcessorType
$HyperthreadingActive = $ESXiHost.HyperthreadingActive
$TimeZone = $ESXiHost.TimeZone
$Version = $ESXiHost.Version -as [int]
$Build = $ESXiHost.Build -as [int]
$Parent = $ESXiHost.Parent
$IsStandalone = $ESXiHost.IsStandalone
$VMSwapfileDatastore = $ESXiHost.VMSwapfileDatastore
$StorageInfo = $ESXiHost.StorageInfo
$NetworkInfo = $ESXiHost.NetworkInfo -as [int]
$DiagnosticPartition = $ESXiHost.DiagnosticPartition
$FirewallDefaultPolicy = $ESXiHost.FirewallDefaultPolicy
$ApiVersion = $ESXiHost.ApiVersion -as [int]
$MaxEVCMode = $ESXiHost.MaxEVCMode
$Manufacturer = $ESXiHost.Manufacturer
$Model = $ESXiHost.Model
$DatastoreIdList = $ESXiHost.DatastoreIdList
$Uid = $ESXiHost.Uid
# Creating SQL INSERT
$SQLHostInsert = "USE $SQLDatabase
INSERT INTO Hosts (LastUpdated, HostID, Name, State, ConnectionState, PowerState, NumCpu, CpuTotalMhz, CpuUsageMhz, MemoryTotalGB, 
MemoryUsageGB, ProcessorType, HyperthreadingActive, TimeZone, Version, Build, Parent, IsStandalone, VMSwapfileDatastore, StorageInfo, NetworkInfo, 
DiagnosticPartition, FirewallDefaultPolicy, ApiVersion, MaxEVCMode, Manufacturer, Model, DatastoreIdList, Uid)
VALUES('$LastUpdated', '$HostID', '$Name', '$State', '$ConnectionState', '$PowerState', '$NumCpu', '$CpuTotalMhz', '$CpuUsageMhz', '$MemoryTotalGB', 
'$MemoryUsageGB', '$ProcessorType', '$HyperthreadingActive', '$TimeZone', '$Version', '$Build', '$Parent', '$IsStandalone', '$VMSwapfileDatastore', '$StorageInfo', '$NetworkInfo',
'$DiagnosticPartition', '$FirewallDefaultPolicy', '$ApiVersion', '$MaxEVCMode', '$Manufacturer', '$Model', '$DatastoreIdList', '$Uid');"
# Running the INSERT query
invoke-sqlcmd -query $SQLHostInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into Clusters SQL Table 
##############################################
$Clusters = Get-Cluster | Select *
# Inserting each row
ForEach ($Cluster in $Clusters)
{
$ClusterID = $Cluster.Id
$Name = $Cluster.Name
$DrsEnabled = $Cluster.DrsEnabled
$DrsMode = $Cluster.DrsMode
$DrsAutomationLevel = $Cluster.DrsAutomationLevel
$HAEnabled = $Cluster.HAEnabled
$HAAdmissionControlEnabled = $Cluster.HAAdmissionControlEnabled
$HAFailoverLevel = $Cluster.HAFailoverLevel
$HARestartPriority = $Cluster.HARestartPriority
$HAIsolationResponse = $Cluster.HAIsolationResponse
$HATotalSlots = $Cluster.HATotalSlots
$HAUsedSlots = $Cluster.HAUsedSlots
$HAAvailableSlots = $Cluster.HAAvailableSlots
$HASlotCpuMHz = $Cluster.HASlotCpuMHz
$HASlotMemoryGB = $Cluster.HASlotMemoryGB
$HASlotNumVCpus = $Cluster.HASlotNumVCpus
$ParentId = $Cluster.ParentId
$ParentFolder = $Cluster.ParentFolder
$VMSwapfilePolicy = $Cluster.VMSwapfilePolicy
$VsanEnabled = $Cluster.VsanEnabled
$VsanDiskClaimMode = $Cluster.VsanDiskClaimMode
$EVCMode = $Cluster.EVCMode
$CustomFields = $Cluster.CustomFields
$Uid = $Cluster.Uid
# Creating SQL INSERT
$SQLClusterInsert = "USE $SQLDatabase
INSERT INTO Clusters (LastUpdated, ClusterID, Name, DrsEnabled, DrsMode, DrsAutomationLevel, HAEnabled, HAAdmissionControlEnabled, HAFailoverLevel, HARestartPriority, 
HAIsolationResponse, HATotalSlots, HAUsedSlots, HAAvailableSlots, HASlotCpuMHz, HASlotMemoryGB, HASlotNumVCpus, ParentId, ParentFolder, VMSwapfilePolicy, 
VsanEnabled, VsanDiskClaimMode, EVCMode, CustomFields, Uid)
VALUES('$LastUpdated', '$ClusterID', '$Name', '$DrsEnabled', '$DrsMode', '$DrsAutomationLevel', '$HAEnabled', '$HAAdmissionControlEnabled', '$HAFailoverLevel', '$HARestartPriority', 
'$HAIsolationResponse', '$HATotalSlots', '$HAUsedSlots', '$HAAvailableSlots', '$HASlotCpuMHz', '$HASlotMemoryGB', '$HASlotNumVCpus', '$ParentId', '$ParentFolder', '$VMSwapfilePolicy',  
'$VsanEnabled', '$VsanDiskClaimMode', '$EVCMode', '$CustomFields', '$Uid');"
# Running the INSERT query
invoke-sqlcmd -query $SQLClusterInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
##############################################
# Inserting into Datacenters SQL Table 
##############################################
$Datacenters = Get-Datacenter | Select *
# Inserting each row
ForEach ($Datacenter in $Datacenters)
{
$DatacenterID = $Datacenter.Id
$Name = $Datacenter.Name
$CustomFields = $Datacenter.CustomFields
$ParentFolderId = $Datacenter.ParentFolderId
$ParentFolder = $Datacenter.ParentFolder
$Uid = $Datacenter.Uid
$DatastoreFolderId = $Datacenter.DatastoreFolderId
# Getting totals for the datacenter (makes this table useful)
$DCClusterCount = Get-Datacenter -Name $Name | Get-Cluster
$DCClusters = $DCClusterCount.Count
$DCHostCount = Get-Datacenter -Name $Name | Get-VMHost
$DCHosts = $DCHostCount.Count
$DCDatastoreCount = Get-Datacenter -Name $Name | Get-Datastore
$DCDatastores = $DCDatastoreCount.Count
$DCPortGroupCount = Get-Datacenter -Name $Name | Get-VirtualPortGroup
$DCPortGroups = $DCHostCount.Count
$DCVMCount = Get-Datacenter -Name $Name | Get-VM
$DCVMs = $DCVMCount.Count
$DCVMDiskCount = Get-Datacenter -Name $Name | Get-VM | Get-HardDisk
$DCVMDisks = $DCVMDiskCount.Count
$DCVMNICCount = Get-Datacenter -Name $Name | Get-VM | Get-NetworkAdapter
$DCVMNICs = $DCVMNICCount.Count
# Creating SQL INSERT
$SQLDatacenterInsert = "USE $SQLDatabase
INSERT INTO Datacenters (LastUpdated, DatacenterID, Name, Clusters, Hosts, Datastores, PortGroups, VMs, VMDisks, VMNICs, CustomFields, ParentFolderId, ParentFolder, Uid, DatastoreFolderId)
VALUES('$LastUpdated', '$DatacenterID', '$Name', '$DCClusters', '$DCHosts', '$DCDatastores', '$DCPortGroups', '$DCVMs', '$DCVMDisks', '$DCVMNICs', '$CustomFields', '$ParentFolderId', '$ParentFolder', '$Uid', '$DatastoreFolderId');"
# Running the INSERT query
invoke-sqlcmd -query $SQLDatacenterInsert -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
}
#######################
# Disconnecting from the vCenter
#######################
Disconnect-VIServer -Force -Confirm:$false
#######################
# End of script
#######################

You can also download a copy of the script in .ps1 format from here:

vSphereChangeControlDBv1.1.zip

Don’t forget to change the variables at the start for your environment. I recommend running the script the first couple times using PowerShell ISE and SQL Server Management Studio to verify the data is successfully being inserted. I’ve done my best to make sure each data type has sufficient size for the data to be inserted without being too wasteful, but you may find you need to tweak it in your environment. To troubleshoot add “write-host $ObjectName and $SQLInsertQuery” at the end of ForEach so you can see what it failed to insert if the data type isn’t sufficient.

With the script successfully running we now need to schedule it to run automatically. Using task scheduler create the below:

I recommend running on a schedule of 1 to 24 hours, but it’s completely up to you how frequently you want to log the data. Now that we have data being regularly inserted into the database the next step is to run some queries on it and get some useful information.

In the next blog post I will cover example queries, which you can access here:

Creating a vSphere Change Management DB for free – Step 3 of 4

Happy scripting,

Joshua