Note: This has been superseded by version v1.1, which you can download from here. Please feel free to walk through the posts to learn how to build it yourself.
In my last blog, post I gave an introduction to Working with SQL Databases using PowerShell. What I’d now like to do is share an example of a cool use case for combining PowerShell, SQL, and PowerCLI.
After drinking a few beers (this blog is called virtually sober for a reason!) I started to think about different problems that I could solve by logging information into a SQL database. What info could I pull and store that would be useful to a virtualization admin? Then the answer hit me. Every setting in the vCenter from VMs to port groups, clusters, hosts to datastores! Why? Because the vCenter database only stores the latest setting.
Want to know what settings a VM had 1 month ago and how much storage it was using? Want to know what settings changed on a host or cluster? Edited a VM or cluster and can’t remember what the old settings were if you need to revert? Somebody renamed a VM and you can’t find it? Today, with just a vCenter, once the change is made the data has gone and you can’t see what it was before.
To solve all these challenges I’m going to take you through creating your own vSphere Change Management Database for free. Using PowerCLI and the SqlServer module we can log all the vSphere information into a separate SQL database on an hourly or daily basis. You can then easily see all configuration items and data from any point in time desired. Pretty useful huh?
So where do we start? With a database of course. We need to create a SQL database in which we can log the data into respective tables. This needs to be easy to use, scalable, and we need to use the correct data types to store the information. Using PowerCLI I’m going to pull data from the following commands:
Get-VM | Select * Get-VM | Get-VMGuest | Select * Get-VM | Get-HardDisk | Select * Get-VM | Get-NetworkAdapter | Select * Get-Datastore | Select * Get-VirtualPortGroup | Select * Get-VMHost | Select * Get-Cluster | Select * Get-Datacenter | Select *
If you have additional cmdlets you want to pull data from you can easily copy my examples and extend them to your use case. For each we will configure a table to store the data (apart from VMGuest which I will combine with Get-VM | Select *), plus one more for VMGuestDiskInfo so we can size usage per OS disk. In each table, we need the ability to select entries for pruning the data so I’m going to use an auto-incrementing primary key. Secondly, we need a datetime to show when the record was inserted. To build the SQL database and table structure required, change the variables at the top and run the script below:
##############################################
# Setting the variables for connecting to the SQL database
##############################################
$SQLInstance = ".\SQLEXPRESS"
$SQLDatabase = "vSphereChangeControlv1"
$SQLUsername = "sa"
$SQLPassword = "Password123!"
############################################################################################
# Nothing to change below this line, comments provided if you need/want to change anything
############################################################################################
##############################################
# Checking 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
##############################################
# Creating the vSphere CMDB Database
##############################################
$SQLCreateDB = "USE master;
GO
CREATE DATABASE $SQLDatabase
GO"
invoke-sqlcmd -query $SQLCreateDB -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-VM | select * + Get-VM | Get-VMGuest | select *
#######################################
$SQLCREATEVMs = "USE $SQLDatabase
CREATE TABLE VMs (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
VMID varchar(50),
Name varchar(255),
PowerState varchar(20),
Notes varchar(max),
Guest varchar(255),
NumCpu tinyint,
CoresPerSocket tinyint,
MemoryGB int,
VMHostId varchar(25),
VMHost varchar(255),
VApp varchar(255),
FolderId varchar(255),
Folder varchar(255),
ResourcePoolId varchar(255),
ResourcePool varchar(255),
HARestartPriority varchar(50),
HAIsolationResponse varchar(50),
DrsAutomationLevel varchar(50),
VMSwapfilePolicy varchar(50),
VMResourceConfiguration varchar(50),
Version varchar(10),
UsedSpaceGB int,
ProvisionedSpaceGB int,
DatastoreIdList varchar(255),
ExtensionData varchar(50),
CustomFields varchar(255),
Uid varchar(255),
PersistentId varchar(50),
OSFullName varchar(100),
IPAddress varchar(255),
State varchar(50),
Hostname varchar(255),
Nics varchar(max),
GuestId varchar(255),
RuntimeGuestId varchar(255),
ToolsVersion varchar (100),
ToolsVersionStatus varchar (100),
GuestFamily varchar(255)
);"
invoke-sqlcmd -query $SQLCREATEVMs -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-VM | Get-harddisk | select *
#######################################
$SQLCREATEVMDisks = "USE $SQLDatabase
CREATE TABLE VMDisks (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
VMID varchar(255),
Parent varchar(255),
DiskID varchar(100),
Name varchar(100),
Filename varchar(max),
CapacityGB int,
Persistence varchar(25),
DiskType varchar(25),
StorageFormat varchar(25)
);"
invoke-sqlcmd -query $SQLCREATEVMDisks -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-View -ViewType VirtualMachine To Get Disk Info
#######################################
$SQLCREATEVMDiskUsage = "USE $SQLDatabase
CREATE TABLE VMDiskUsage (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
VMID varchar(255),
Name varchar(255),
DiskNum int,
DiskPath varchar(255),
DiskCapacityGB int,
DiskFreeSpaceGB int,
DiskCapacityMB int,
DiskFreeSpaceMB int
);"
invoke-sqlcmd -query $SQLCREATEVMDiskUsage -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-VM | Get-networkadapter | select *
#######################################
$SQLCREATEVMNICs = "USE $SQLDatabase
CREATE TABLE VMNICs (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
VMID varchar(255),
Parent varchar(255),
NICID varchar (100),
Name varchar(100),
MacAddress varchar(17),
NetworkName varchar(255),
ConnectionState varchar(255),
WakeOnLanEnabled varchar(10),
Type varchar(25)
);"
invoke-sqlcmd -query $SQLCREATEVMNICs -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-datastore | select *
#######################################
$SQLCREATEDatastores = "USE $SQLDatabase
CREATE TABLE Datastores (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
DatastoreID varchar(100),
Name varchar(255),
CapacityGB int,
FreeSpaceGB int,
State varchar(25),
Type varchar(25),
FileSystemVersion int,
Accessible varchar(25),
StorageIOControlEnabled varchar(25),
CongestionThresholdMillisecond int,
ParentFolderId varchar(100),
ParentFolder varchar(100),
DatacenterId varchar(100),
Datacenter varchar(100),
Uid varchar(255)
);"
invoke-sqlcmd -query $SQLCREATEDatastores -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-virtualportgroup | select *
#######################################
$SQLCREATEPortGroups = "USE $SQLDatabase
CREATE TABLE PortGroups (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
VirtualSwitchId varchar(100),
Name varchar(100),
VirtualSwitch varchar(100),
VirtualSwitchName varchar(100),
PortGroupKey varchar(100),
VLanId int,
VMHostId varchar(100),
VMHostUid varchar(255),
Uid varchar(255)
);"
invoke-sqlcmd -query $SQLCREATEPortGroups -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-vmhost | select *
#######################################
$SQLCREATEHosts = "USE $SQLDatabase
CREATE TABLE Hosts (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
HostID varchar(100),
Name varchar(255),
State varchar(50),
ConnectionState varchar(100),
PowerState varchar(100),
NumCpu tinyint,
CpuTotalMhz int,
CpuUsageMhz int,
MemoryTotalGB int,
MemoryUsageGB int,
ProcessorType varchar(100),
HyperthreadingActive varchar(100),
TimeZone varchar(25),
Version int,
Build int,
Parent varchar(100),
IsStandalone varchar(20),
VMSwapfileDatastore varchar(255),
StorageInfo varchar(100),
NetworkInfo int,
DiagnosticPartition varchar(100),
FirewallDefaultPolicy varchar(100),
ApiVersion int,
MaxEVCMode varchar(100),
Manufacturer varchar(255),
Model varchar(255),
Uid varchar(255),
DatastoreIdList varchar(255)
);"
invoke-sqlcmd -query $SQLCREATEHosts -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-cluster | select *
#######################################
$SQLCREATEClusters = "USE $SQLDatabase
CREATE TABLE Clusters (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
ClusterID varchar(100),
Name varchar(100),
DrsEnabled varchar(100),
DrsMode varchar(100),
DrsAutomationLevel varchar(100),
HAEnabled varchar(100),
HAAdmissionControlEnabled varchar(100),
HAFailoverLevel int,
HARestartPriority varchar(100),
HAIsolationResponse varchar(100),
HATotalSlots int,
HAUsedSlots int,
HAAvailableSlots int,
HASlotCpuMHz int,
HASlotMemoryGB int,
HASlotNumVCpus int,
ParentId varchar(100),
ParentFolder varchar(100),
VMSwapfilePolicy varchar(100),
VsanEnabled varchar(100),
VsanDiskClaimMode varchar(100),
EVCMode varchar(100),
CustomFields varchar(MAX),
Uid varchar(255)
);"
invoke-sqlcmd -query $SQLCREATEClusters -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################################
# Creating SQL Table for Get-datacenter | select *
#######################################
$SQLCREATEDatacenters = "USE $SQLDatabase
CREATE TABLE Datacenters (
RecordID int IDENTITY(1,1) PRIMARY KEY,
LastUpdated datetime,
DatacenterID varchar(100),
Name varchar(100),
Clusters int,
Hosts int,
Datastores int,
PortGroups int,
VMs int,
VMDisks int,
VMNICs int,
CustomFields varchar(max),
ParentFolderId varchar(100),
ParentFolder varchar(100),
Uid varchar(255),
DatastoreFolderId varchar (255)
);"
invoke-sqlcmd -query $SQLCREATEDatacenters -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
#######################
# End of script
#######################
You can also download the script in .ps1 format from the zip file below:
vSphereChangeControlDBv1.1.zip
With the database created the next step is to insert some data into it! In the second blog post in this series, I will take you through exactly that, which you can access here:
Creating a vSphere Change Management DB for free – Step 2 of 4
Happy scripting,
Joshua
[…] Step 1 – Creating the SQL Database […]
[…] Step 1 – Creating the SQL Database Step 2 – Inserting data into the SQL Database […]
[…] Step 1 – Creating the SQL Database Step 2 – Inserting data into the SQL Database Step 3 – Querying the SQL Database […]