Powershell to Database.ps1

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

        }
    }
}