PostgreSQL + PowerShell
I’ve been absent for quite a while. The home lab automation has taken a back seat to unpacking and setting up IKEA flat-pack furniture.
That is still ongoing, but let’s focus on some more fun things. I need to get details from our UCS Environment into an inventory database - this database will be used to compare to the Cisco Compatibility Matrix and determine the potential impact of future upgrades. This will expand beyond UCS (and into VMware) - so leveraging my past Excel reports will not be powerful enough.
Initially, I’m looking at leveraging PostgreSQL because:
- Plethora of information on the inter-web-nets
- It’s new to me (and who doesn’t like learning about new stuff?)
- I’ve been told I have to use PostgreSQL by our DB team (rendering the prior three points somewhat moot).
You need to install the PostgreSQL ODBC driver from here - but apparently don’t need to actually configure it for anything. PowerShell will leverage .NET to handle the connectivity details.
My data is pulled from the Cisco UCS HealthCheck v2.3. Note that the script will connect to multiple UCS Managers (woo!), and will output an HTML (boo!). I realized that I didn’t need all of the data the script gathers - there’s quite a bit of performance metrics that take a ton of time to gather - so I stripped out the bits I didn’t need/want and dumped the result prior to the script converting everything to HTML.
My (somewhat embarrassing) hack job is available on Github - note that once I get this database stuff figured out, I’ll be reworking that gist to make it better.
The object that is generated from the hacked together gist is a nested hash (several layers deep) Note that each of the inventory items (blades, IOMS, FIs, etc) are arrays. Additionally, when more than one UCS Manager is evaluated,
$ucsdatadump has one key per UCSM.
The data structure looks a little like this:
$ucsdatadump["UCS Manager1"]["Inventory"]["Blades"][0..n] [Dn] [Serial] [CIMC] [Memory] [Model] [Adapter] [Serial] [Name] [Model] ... ... ["IOMs"][0..n] ["Dn"] ["Fabric_Id"] ["Backup_FW"] ["Running_FW"] ... ["FIs"][0..n] ["Dn"] ["Kernel"] ... ["Chassis"][0..n]
Originally, I was put off by the hash(hash(hash(hash))), but it actually works quite well for programmatically dumping into a database.
At this point, I’m only running the code via PowerShell ISE - not as a standalone ps1 file (although there’s probably no good reason not to make this into a standalone script).
In order to create the database table’s columns, I’ll need to convert a passed object’s PowerShell type to the corresponding postgreSQL type. The function below can be expanded as necessary, but for my reporting generally a text/int is sufficient.
Next I need to interact with the database - functionality that can generally be broken into two categories: querying to get data, and querying to impact the database without expecting anything in return. I’m using two additional helper functions for this fundamental interaction - both leverage the newly installed ODBC driver. Note the default values provided for the db connection details (those would need to be updated)
There are two key things that need to be accomplished - the table needs to be created (if it isn’t already), and the table needs to be populated/updated.
Assuming our hash table layout specified above, we know that our various UCS Managers are specified as keys in
$ucsfulldump.keys - we cycle through each UCS Manager with a foreach loop. We also know that there are various tables that we’d like to dump into the database (Blades, IOMs, FIs, etc) - specified as keys in
$ucsfulldump[$ucs].Inventory.keys - so we loop through those too.
At this point in the script we are now at a level something similar to
$ucsfulldump[UCSM1].Inventory.[Blades], below which is an array of UCS Blade hashes. This information will be captured in a database table “UCS_Blades” that will be created if it doesn’t exist already.
Below, we first query the database to see if the table exists, if it doesn’t - we need to create it. Pull the first object off of the array
$myObj (which is in itself a hash), and begin to craft the create table PostgreSQL query - retrieving the column type from our helper function based on the
$myObj[$key] PowerShell object type. Lastly, we try to create the table.
On a side note, I’m stoked about this script - but I’m super proud of this particular bit of logic. If an additional inventory hash is added, the code does not need to be altered in order to create a new table. Likewise, I can use this code for other hashtable objects (if properly created) to create new tables - all without hardcoding practically anything - save for the “UCSM” column and “UCS_” table prefix.
Also for the purposes of this script, UCS object databases will have a compound primary key - UCSM and Dn. I’m manually creating this constraint for now, but will probably add something to the script to deal with primary keys in the future.
Populating the Table
I discovered an interesting problem at this point - as the hash object may contain key-value pairs that are not text or numbers, we can’t just dump everything in the object into the database. To determine which key-value pairs will be pushed to the database, we query the database’s schema and parse the resulting column names.
As those names were derived from the first object’s hash keys we should be able to easily pull the information and pull only the
$props from each inventory object (Blades, etc).
Now that we know which properties correspond to the database table’s columns, we can loop through each item and do one of three actions: Insert, Update, or Nothing. If the item doesn’t exist in the database, we craft an insert statement. Alternatively, if the item exists already in the database (again, based on that compound primary key), we check to see if the database matches the item. If it does, nothing happens. If there are differences, only the differences are updated.
At this point, we have a pretty solid script that works well and is relatively generic - at least generic enough that we can take it and massage it to take in VMware inventory (see a future update). Unfortunately, I need more information to complete my inventory database - specifically the Blade VIC information. In the next post, I’ll show how I handle this wrinkle. In the meanwhile, the script up to this point is available as a gist on my github.