Skip to content

Instantly share code, notes, and snippets.

@pezhore
Created January 14, 2016 22:54
Show Gist options
  • Save pezhore/a74c7cefc56cacd9ab67 to your computer and use it in GitHub Desktop.
Save pezhore/a74c7cefc56cacd9ab67 to your computer and use it in GitHub Desktop.
# Couple of things
# 1) Holy shit, working with hashes of hashes of hashes of hashes is painful.
# 2) This is not clean, but *is* somewhat documented.
# 3) Requires odbc configured
<# Base dataset layout
$ucs $tablename $item
$ucsdatadump["UCS Domain"]["Inventory"]["Blades"]
[Dn]
[Serial]
[CIMC]
[Memory]
[Model]
[Adapter]
[Serial]
[Name]
[Model]
...
...
["IOMs"]
["Dn"]
["Fabric_Id"]
["Backup_FW"]
["Running_FW"]
...
["FIs"]
["Dn"]
["Kernel"]
...
["Chassis"]
["Faults"]
#>
# This function returns Postgres types for the object
function Get-PostgresColumnType
{
[CmdletBinding()]
param ($obj)
# Get this object's PowerShell Type
if ($obj)
{
$TypeName = $obj.getType().Fullname
}
# Convert the PowerShell Type to PostGres Column Type
switch -regex ($TypeName)
{
".*String" { $columnType = "text" }
".*UInt32" { $columnType = "bigint" }
".*ArrayList" { Write-Verbose "ArrayList, cannot convert to Postgres Type"}
}
# Return the column type
$columnType
}
# This is for Postgres queries that return stuff
function Get-ODBCData{
param(
[string]$query,
$dbServer = "10.1.1.64", # DB Server (either IP or hostname)
$dbName = "inventorydb", # Name of the database
$dbUser = "postgres", # User we'll use to connect to the database/server
$dbPass = "postgres" # Password for the $dbUser
)
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$dbServer;Port=5432;Database=$dbName;Uid=$dbUser;Pwd=$dbPass;"
$conn.open()
$cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
$ds = New-Object system.Data.DataSet
(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
$conn.close()
$ds.Tables[0]
}
# This is for Postgres queries that don't return stuff
function Set-ODBCData{
param(
[string]$query,
$dbServer = "10.1.1.64", # DB Server (either IP or hostname)
$dbName = "inventorydb", # Name of the database
$dbUser = "postgres", # User we'll use to connect to the database/server
$dbPass = "postgres" # Password for the $dbUser
)
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString= "Driver={PostgreSQL Unicode(x64)};Server=$dbServer;Port=5432;Database=$dbName;Uid=$dbUser;Pwd=$dbPass;"
$cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)
$conn.open()
try
{
$cmd.ExecuteNonQuery()
}
catch
{
Throw "BAD QUERY: $query"
}
$conn.close()
}
foreach ($UCS in $ucsfulldump.keys)
{
Write-Progress -Activity "Analyzing Data Dump" `
-Status "UCS: $ucs $($($ucsfulldump.Keys).IndexOf($ucs)) of $($ucsfulldump.keys.Count)" `
-PercentComplete $(($($ucsfulldump.Keys).IndexOf($ucs) / $ucsfulldump.Keys.Count)*100) `
-Id 0
# Get all the keys in this particular level of things
foreach ($tableName in $ucsfulldump[$ucs].Inventory.Keys)
{
Write-Progress -Activity "Checking Tables" `
-Status "Table: $tableName $($($ucsfulldump[$ucs].Inventory.Keys).IndexOf($tableName)) of $($($ucsfulldump[$ucs].Inventory.Keys).Count)" `
-PercentComplete $(($($ucsfulldump[$ucs].Inventory.Keys).IndexOf($tableName) / ($ucsfulldump[$ucs].Inventory.Keys).Count)*100) `
-Id 1 -ParentId 0
# See if this key/val pair has a value and isn't null
if ($ucsfulldump[$ucs].Inventory["$tableName"])
{
if (!(Get-ODBCData -query "SELECT relname FROM pg_class WHERE relname='UCS_$tableName'"))
{
# Get the first object in this value's array
$myObj = $ucsfulldump[$ucs].Inventory["$tableName"] | select -first 1
# Build the CREATE TABLE query
$newTable = "CREATE TABLE `"UCS_$tableName`"(`n"
foreach ($key in $myObj.Keys)
{
$type = Get-PostgresColumnType $myObj["$key"]
if ($type)
{
$newTable += "`t`"$key`" $type,`n"
}
}
$newTable += "`t`"UCSM`" text,`n"
# Strip off the last new line and "," characters add a new line, close the parenthesis and add a semicolon
$newTable = $newTable.Substring(0, $newTable.Length -2)+"`n);"
# Create the new Table
try
{
$output = Set-ODBCData -query $newTable
}
catch
{
Write-Host -ForegroundColor Red "ERROR! $($_.Exception)"
}
}
# Query for this table's columns
$query = "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'UCS_$tableName';"
$columns = Get-ODBCData -query $query
# Dump the column names into an array
$props = $columns.column_name
Foreach ($item in $ucsfulldump[$ucs].Inventory["$tableName"])
{
Write-Progress -Activity "Evaluating Item" `
-Status "Dn: $($item["Dn"]) $($ucsfulldump[$ucs].Inventory["$tableName"].IndexOf($item)) of $($ucsfulldump[$ucs].Inventory["$tableName"].Count)" `
-PercentComplete $(($ucsfulldump[$ucs].Inventory["$tableName"].IndexOf($item) / $ucsfulldump[$ucs].Inventory["$tableName"].Count)*100) `
-Id 2 -ParentId 1
$dbQuery = $Null
# Check of this thing exists (Primary Key = Dn & UCSM)
$exists = Get-ODBCData -query "SELECT * from `"UCS_$tableName`" WHERE `"Dn`" LIKE '$($item["Dn"])' AND `"UCSM`" LIKE '$ucs'"
if ($exists)
{
# Assume it's identical
$identical = $true
$diffProps = @()
#write-host -ForegroundColor Green "This already exists"
# Go through each property in this item, and compare properties
foreach ($prop in $($exists |gm -MemberType Properties |select -ExpandProperty Name))
{
# If this item, and the existing entry are not identical AND the property is not UCSM (UCSM only exists in the database)
if (($item[$prop] | Select -First 1) -notlike $exists.$prop -and $prop -notmatch "UCSM")
{
$identical = $false
$diffProps += $prop
}
}
# If we're not identical, we need to update
if (!$identical)
{
#Update this shit.
Write-host -ForegroundColor Magenta "This item already exists, however properties are different. We need to update things for $ucs >> $tablename >> $($item["Dn"]): $diffProps"
$dbQuery = "UPDATE `"UCS_$tableName`" SET "
foreach ($diffProp in $diffProps)
{
$dbQuery += "`"$diffProp`" = '$($item["$diffProp"])',"
}
$dbQuery = $dbQuery.Substring(0,$dbQuery.Length - 1) + " WHERE `"Dn`" LIKE '$($item["Dn"])' AND `"UCSM`" LIKE '$ucs'"
}
}
# This item doesn't exist in the database, time to add it
else
{
# Start off the insert query specifying the table name and properties/columns
$dbQuery = "INSERT INTO public.`"UCS_$tableName`" (`"$($props -join '", "')`") VALUES ('"
# Go through each property and add this Item's corresponding property
Foreach ($property in $props)
{
if ($item["$property"] -match "'")
{
Write-Host -ForegroundColor Green "Ooops, found a `"'`" in: $($item["$property"])"
$dbQuery += $($item["$property"]).Replace("'","''")+"', '"
}
if ($item["$property"].count -gt 1)
{
write-host "SHIT MORE THAN ONE! $($item["$property"]), using the first one"
$dbQuery += [string]$($item["$property"] | Select -First 1)+"', '"
}
else
{
$dbQuery += [string]$($item["$property"])+"', '"
}
}
# Bad addition leads to an extraneous ", '", take that off and add the closing parenthesis
$dbQuery = $dbQuery.Substring(0,$dbQuery.Length - 6)
$dbQuery = $dbQuery + " '$UCS');"
#Double Check the insert query
#$insertQuery
}
if ($dbQuery)
{
$output = Set-ODBCData -query $dbQuery
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment