Inventory and Fault collection data to MS SQL DB

Version 3

    UCS Communities Login ID: chratkin

    Twitter handle (optional):@chrisatkinson78

    Have you read the Official Rules of the Contest and do you accept the terms and conditions     Yes  [X ]    No  [   ]

    Are you a Cisco employee                                                                                                                          Yes  [ X ]    No  [   ]

    Does the script run on an emulator -                                                                                                          Yes [ X ]    No  [   ]

              If yes which version? _____2.1 and 2.2 confirmed__________

     

    Include your script here:

    #Instructions:  Download the following 3 files.  1) Modify the podlist.csv with your UCS domains. 2) Modify CollectUCSInventoryforSQL.ps1

    # with your variables (E-mail info, path info, DB info) 3) Execute CollectUCSInventoryforSQL.ps1

    # See video attached for demonstration



    ##FILE1)  CollectUCSInventoryforSQL.ps1

    ###################################################################

    ##  CollectUCSInventoryforSQL.ps1 by Chris Atkinson Cisco Systems

    ##

    ## This script is used to collect Cisco UCS Inventory via PowerTool

    ## and insert the collected object data into a MS SQL database.  This script

    ## does it's best to convert the hierarchical data from UCS into a

    ## relational model.  This allows the user to easily join fields from the database.

    ##

    ## This is made more apparent by the creation of a new object called BladeDn which can be used

    ## to join field data.

    ##

    ## ex. SQL

    ##  select distinct b.ucs,bc.name,sp.name,b.operstate,b.dn,b.totalmemory / 1024 as 'Total Memory in GB', kvm.extip as 'CIMC IP'

    ##  from blade b left join [catalog] bc on b.model = bc.pid

    ##  left join serviceprofile sp on sp.uuid = b.uuid

    ##  left join kvm kvm on b.ucs+ b.dn = kvm.ucs + kvm.bladedn

    ##  where extip != '0.0.0.0'

    ##

    ##

    #########--Modify Variables--#####################################################################

    $strTo = "someone@somewhere.com"  #Error Email Address To

    $strFrom = "PowerToolRocks@donotreply.com"  #Error From Address (use a no reply)

    $strSMTPServer = "somesmtpserver.somewhere.com"  #SMTP Server address

    $pathtoscriptfiles = "E:\InventoryV3\"

    $dbInstance = ".\Atkinson"  #A valid MS SQL Exist must exist here, you can use "." for local named instance

    $dbDB = "UCS_DB"    #A valid empty DB should exist here

     

    #######Change Path or use a Variable to load PowerTool Module#####################################

    Import-Module "C:\Program Files (x86)\Cisco\Cisco UCS PowerTool\Modules\CiscoUcsPS\CiscoUcsPS.psd1"

    ##################################################################################################

     

    Set-UcsPowerToolConfiguration -SupportMultipleDefaultUcs 1

    $import = import-csv ($pathtoscriptfiles + "podlist.csv")

    try {

    foreach ($f in ($import)) {

    $error.clear()

    $pass = $f.password |convertTo-SecureString -AsPlainText -Force

    $cred = New-Object system.Management.Automation.PSCredential($f.Username,$pass)

    connect-ucs $f.url -credential $cred

    }

    ##################################

    #3rd party function to handle taking Powershell object and inserting object data into MS SQL DB. Note the -SqlServer arguments requires a valid SQL Server Instance, and -Database needs a valid DB.  The first time you run the script the DB should be empty

    $loadSQLmod = $pathtoscriptfiles + "out-sql2.ps1"

    . $loadSQLmod

    ##################################

    $podsconnected = get-ucspssession   #who did we connect to

    if (($podsconnected).count -eq ($import).count) {    #if we failed to connect to a UCS domain fail IF statement and send e-mail to admin

     

    write-output "Getting Status"

    Get-UcsStatus | out-sql  -SqlServer $dbInstance -Database $dbDB -table PodStatus -DropExisting $true

    write-output "Getting FI Info"

    Get-UcsNetworkElement | out-sql  -SqlServer $dbInstance -Database $dbDB -table FabricIc -DropExisting $true

    write-output "Getting Adaptor Info"

    Get-UcsAdaptorUnit |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},* | out-sql -SqlServer $dbInstance -Database $dbDB -table Adaptor -DropExisting $true

    write-output "Getting vHBA Info"

    Get-UcsVhba |select-object @{Name="BladeDn";Expression={$_.EquipmentDn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql -SqlServer $dbInstance -Database $dbDB -table vHBA -DropExisting $true

    write-output "Getting Slot Info..."

    Get-UcsFabricComputeslotep | out-sql  -SqlServer $dbInstance -Database $dbDB -table Chassis -DropExisting $true

    write-output "Getting Mgmt If Info"

    Get-UcsMgmtIf |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql -SqlServer $dbInstance -Database $dbDB -table KVM -DropExisting $true

    write-output "Getting Faults"

    Get-UcsFault |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},@{Name="MemDn";Expression={$_.dn.substring(0,46)+ "//" -replace "///","" -replace "//","" -replace "/f",""}},*|  out-sql  -SqlServer $dbInstance -Database $dbDB -table Fault -DropExisting $true

    write-output "Getting Blade Info"

    Get-Ucsblade | out-sql -SqlServer $dbInstance -Database $dbDB -table Blade -DropExisting $true

    write-output "Getting SP Info"

    Get-UcsServiceProfile| out-sql  -SqlServer $dbInstance -Database $dbDB -table ServiceProfile -DropExisting $true

    write-output "Getting vNIC Info"

    Get-UcsVnic |select-object @{Name="BladeDn";Expression={$_.EquipmentDn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql  -SqlServer $dbInstance -Database $dbDB -table vNIC -DropExisting $true

    write-output "Getting VLAN Info"

    Get-Ucsvlan | out-sql -SqlServer $dbInstance -Database $dbDB -table VLAN -DropExisting $true

    write-output "Getting Memory Info"

    get-ucsmemoryunit |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql  -SqlServer $dbInstance -Database $dbDB -table Memory -DropExisting $true

    write-output "Getting Firmware Info"

    Get-UcsFirmwareRunning |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql -SqlServer $dbInstance -Database $dbDB -table Firmware -DropExisting $true

    write-output "Getting Local Disk Info"

    Get-ucsstoragelocaldisk |select-object @{Name="BladeDn";Expression={$_.dn.substring(0,22)+ "//" -replace "///","" -replace "//",""}},*| out-sql -SqlServer $dbInstance -Database $dbDB -table LocalDisk -DropExisting $true

    write-output "Getting WWN Initiators"

    get-ucswwninitiator | out-sql -SqlServer $dbInstance -Database $dbDB -table WWNInitiators -DropExisting $true

    get-date | out-sql -SqlServer $dbInstance -Database $dbDB -table Runtime -DropExisting $true

     

    }

    else

    {write-host "Did not connect to all pods."

    $success = $podsconnected | select ucs

    $list = $import | select pod

     

    # Define and create the message

    $strSubject = "Error during UCS Inventory collection"

    $strBody = "My attempt to connect to all pods failed.  Please check my podlist for errors.  I tried to connect to:"

    foreach ($f in ($import)) {

    $strBody += "</br>"

    $strBody += $f.pod

    }

    $strBody += "</br>"

    $strBody += "</br>"

    $strBody += "BUT ONLY CONNECTED TO: "

    foreach ($f in ($podsconnected)) {

    $strBody += "</br>"

    $strBody += $f.ucs

    }

    $objMessage = New-Object System.Net.Mail.MailMessage –ArgumentList $strFrom, $strTo, $strSubject, $strBody

     

    # Define the SMTP server

    write "Sending E-mail"

    $objSMTP = New-Object System.Net.Mail.SMTPClient –ArgumentList $strSMTPServer

     

    # Send the message

    Send-MailMessage -To $strTo -From $strFrom -Subject $strSubject -Body $strBody -BodyAsHTML -SmtpServer $strSMTPServer

    }

    ####################################

    disconnect-ucs

    #Get capability catalog from just ONE of the domains, probably best to put latest firmware domain in top of podlist spreadsheet

    foreach ($f in ($import[0])){

    $pass = $f.password |convertTo-SecureString -AsPlainText -Force

    $cred = New-Object system.Management.Automation.PSCredential($f.Username,$pass)

    connect-ucs $f.url -credential $cred

    write "Getting Capability Catalog Data"

    Get-ucsmo capabilities -Hierarchy |where {$_.Rn -eq "manufacturing"}|select name,manufacturer,model,dn,rn,description,pid,oemname,partnumber,sku,Caption,ucs | out-sql -SqlServer $dbInstance -Database $dbDB -table Catalog -DropExisting $True

    }

    disconnect-ucs

    ########EOS if no errors caught######

     

    }catch   #if any rogue exceptions are found they are caught here and emailed.

    {disconnect-ucs

     

    # Define and create the message

     

    $strSubject = "Error was caught during UCS Inventory collection"

    $strBody = $error[0].ToString() + $error[0].InvocationInfo.PositionMessage

    $objMessage = New-Object System.Net.Mail.MailMessage –ArgumentList $strFrom, $strTo, $strSubject, $strBody

     

    # Define the SMTP server

    $objSMTP = New-Object System.Net.Mail.SMTPClient –ArgumentList $strSMTPServer

     

    # Send the message

    write "Sending Error E-mail"

    Send-MailMessage -To $strTo -From $strFrom -Subject $strSubject -Body $strBody -BodyAsHTML -SmtpServer $strSMTPServer

     

    Write-Host "Error occurred in script:"

         Write-Host $error[0].ToString() + $error[0].InvocationInfo.PositionMessage

         exit

    }

    ################################################################################

    ################################################################################

     

    ##EOF

     

     

     

     

     

    FILE 2)  out_sql2.ps1

    ##############################################################################

    ##

    ## out-sql.ps1

    ##

    ## by Alexey Yeltsov, Microsoft Corp.

    ## Revised by Chris Atkinson, Cisco Systems to use SQLClient instead of OLEdb.  Added brackets for property names to allow reserved words as property names for a table.

    ## Export pipeline contents into a new SQL table

    ##

    ## Parameters:

    ##    $SqlServer        - SQL Server

    ##    $Database      - Database name

    ##    $Table         - Table name

    ##    $DropExisting  - Drop $Table if it already exists and recreate it

    ##                    (default $false)

    ##    $RowId         - Add identity column named $RowId and make it a primary key.

    ##                    (default "RowID". Can pass $null if identity is not needed)

    ##

    ##

    ## Examples:

    ##

    ##    #First, load the function

    ##    . .\out-sql2.ps1

    ##

    ##    #Export processes to table Process in database Scratch on local sql server

    ##    get-process | out-sql -SqlServer . -database Scratch -table Process -dropexisting $true

    ##

    ##    #Export volume details from 4 servers into a table

    ##    @("Server1","Server2","Server3","Server4") `

    ##    | % {$Server = $_ ; Get-WMIObject Win32_Volume -computer $Server } `

    ##    | Select-Object `

    ##        SystemName, `

    ##        Name, `

    ##        @{Name="CapacityGb";Expression={[math]::truncate($_.Capacity / 1Gb)}}, `

    ##        @{Name="FreeGb";Expression={[math]::truncate($_.FreeSpace / 1Gb)}} `

    ##    | out-sql -sqlserver . -database Scratch -table DiskVolume -dropexisting $true

    ##

    ##

    ##

    ##############################################################################

    function Out-Sql($SqlServer=$null,$Database=$null,$Table=$null,$DropExisting=$false,$RowId="RowID") {

      begin

      {

           $Line = 0

           [string]$CreateTable = ""

           if(-not $SqlServer) { throw 'Out-Sql expects $SqlServer parameter' }

           if(-not $Database) { throw 'Out-Sql expects $Database parameter' }

           if(-not $Table) { throw 'Out-Sql expects $Table parameter' }

           if($DropExisting) { write-debug "Note: If the table exists, it WILL be dropped and re-created."}

           $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

           $SqlConnection.ConnectionString = "Data Source=$SqlServer;" +

                        "  Initial Catalog=$Database;" +

                        "  Integrated Security=SSPI;"

       

           write-debug "Will open connection to SQL server ""$SqlServer"" and will populate table ""$Table."""

           write-debug "Connection string: `n$SqlConnectionString"

          # $SqlConnection = New-Object System.Data.OleDb.OleDbConnection $SqlConnectionString

          # $SqlCommand = New-Object System.Data.OleDb.OleDbCommand "",$SqlConnection

           $SqlCommand = New-Object System.Data.SqlClient.SqlCommand

           $SqlCommand.connection = $SqlConnection   

       

           $SqlConnection.Open()

      }

      process

      {

           $Line ++

           $Properties = $_.PSObject.Properties

           if (-not $Properties)

           {

             throw "Out-Sql expects object to be passed on the pipeline. The object must have .PSObject.Properties collection."

           }

           #if we're at the first line, initialize the table

           if ($Line -eq 1)

           {

                  #initialize SQL connection and create table

                  if($DropExisting) { $CreateTable += "IF OBJECT_ID('$Table') IS NOT NULL DROP TABLE $Table;`n"}

                  $CreateTable +="CREATE TABLE $Table ( `n"

                  $col = 0

                  if ($RowId)

                  {

                         $col++;

                         $CreateTable +="$RowId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED `n"

                  }

                  foreach($Property in $Properties)

                  {

                         $col++;

                         if ($col -gt 1) { $CreateTable +="," }

                         # In below, why not use "if ($Property.Value -is [datetime])"?

                         # Because access can be denied to the value, but Property.TypeNameOfValue would still be accessible!

                         if ($Property.TypeNameOfValue -eq "System.DateTime")

                         {

                               $CreateTable +=" [$($Property.Name)] DATETIME NULL `n"

                           #        $CreateTable +="[$($Property.Name)] NVARCHAR(MAX) NULL `n"

                         }

                         elseif ($Property.TypeNameOfValue -eq "System.Int64")

                         {

                          $CreateTable +=" [$($Property.Name)]  bigint NULL `n"

                         }

                         elseif ($Property.TypeNameOfValue -eq "System.UInt64")

                         {

                          $CreateTable +=" [$($Property.Name)]  decimal(28,2) NULL `n"

                         }

                         elseif ($Property.Name -eq "TimeCollected") #-or ($Property.Name -like '*time*'))

                         {

                          $CreateTable +=" [$($Property.Name)] DATETIME NULL `n"

                         }

                          elseif ($Property.TypeNameOfValue -eq "System.Int32" -or ($Property.TypeNameOfValue -eq "System.UInt32"))

                         {

                          $CreateTable +=" [$($Property.Name)] int NULL `n"

                         }

                     

                         else

                         {

                               $CreateTable +=" [$($Property.Name)] NVARCHAR(MAX) NULL `n"

                         }

                  }

                  $CreateTable +=")"

                  write-debug "Will execute SQL to create table: `n$CreateTable"

                  if ($DropExisting -eq $True)

                  {

                  $SqlCommand.CommandText = $CreateTable

                   $rows = $SqlCommand.ExecuteNonQuery()

                  }

                         

           }

           #Prepare SQL insert statement and execute it

           $InsertStatement = "INSERT $Table VALUES("

           $col = 0

           foreach($Property in $Properties)

           {

                  $col++;

                  if ($col -gt 1) { $InsertStatement += "," }

                  #In the INSERT statement, do speacial tratment for Nulls, Dates and XML. Other special cases can be added as needed.

                  if (-not $Property.Value)

                  {

                         $InsertStatement += "null `n"

                  }

                  elseif ($Property.Value -is [datetime])

                  {

                         $InsertStatement += "'" + $Property.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'`n"

                  }

                  elseif ($Property.Value -is [System.Xml.XmlNode] -or $Property.Value -is [System.Xml.XmlElement])

                  {

                         $InsertStatement += "'" + ([string]$($Property.Value.Get_OuterXml())).Replace("'","''") + "'`n"

                  }

                  else

                  {

                         $InsertStatement += "'" + ([string]$($Property.Value)).Replace("'","''") + "'`n"

                  }

           }

           $InsertStatement +=")"

           write-debug "Running insert statement: `n $InsertStatement"

           $SqlCommand.CommandText = $InsertStatement

           $rows = $SqlCommand.ExecuteNonQuery()

      }

      end

      {

           write-debug "closing SQL connection..."

           $SqlConnection.Close()

      }

    }

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    FILE 3)  podlist.csv

     

    pod,url,Username,password

    UCS1,10.0.1.132,powertool,cisco123

    UCS2,10.0.1.135,powertool,cisco123