Inventory and Fault collection data to MS SQL DB

Document created by chratkin on Apr 7, 2014Last modified by chratkin on Apr 7, 2014
Version 3Show Document
  • View in full screen mode

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

Outcomes