Getting the CORE/CPU count with PowerShell

With SQL Server 2012's new licensing model, it becomes important to know how many cores you have on a given server, however if you are still in planning phase for 2012 and you have a large inventory of SQL Servers, you might be interested in knowing how the average number of cores per server you have, or the total number of servers with x cores. This is the situation I was in, so I turned to PowerShell to help me out. The "Hey, Scripting Guy! blog" helped me get a start on figuring this out. It showed me that I can do the following:

$property = "systemname","maxclockspeed","addressWidth","numberOfCores", "NumberOfLogicalProcessors"
Get-WmiObject -class win32_processor -Property  $property | Select-Object -Property $property

And get returned the information I want. But this has a lot of room for some improvement. First I started by creating a datatable to dump the results in when I loop over every server in my inventory, next I added my progress meter logic (this is handy if you have multiple locations across the world that you will be connecting to, or if you have a large inventory), I then added logic to loop over the inventory and store the data, then I display it in a Grid. At that point you can view it, or copy it to excel or do something else with it. Note that my logic assumes you have some kind of inventory database which contains the hostname of your SQL Servers; if you have this in another format (or don't have it), you will have to remove the SQL Query logic and replace it with logic matching the way you store this data. Let me know if you find this useful, or if you have any questions:

<###################################
 ##   DEFINE INVENTORY DATABASE   ##
 ###################################>
CLS
$server = "[SERVER\INSTANCE]"
$dbname = "[DB Name]"
$LookupServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList localhost
CLS
Write-Host 'Querying Server...'

<#########################
 ##   CLEAR VARIABLES   ##
 #########################>
$SystemName = ""
$MaxclockSpeed = ""
$AddressWidth = ""
$NumberOfCores = 0
$NumberOfLogicalProcessors = 0
$CPUCount = 0

<######################################
 ##   CREATE DATATABLE FOR RESULTS   ##
 ######################################>
$table = New-Object system.Data.DataTable

$col1 = New-Object system.Data.DataColumn Server,([string])
$col2 = New-Object system.Data.DataColumn maxclockspeed,([string])
$col3 = New-Object system.Data.DataColumn addressWidth,([string])
$col4 = New-Object system.Data.DataColumn numberOfCores,([string])
$col5 = New-Object system.Data.DataColumn NumberOfLogicalProcessors,([string])
$col6 = New-Object system.Data.DataColumn CPUCount,([string])


$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)

<######################################
 ## QUERY TO GET INSTANCES TO CHECK  ##
 ######################################>
$query += "
 WRITE YOUR QUERY HERE TO GET YOUR SERVER LIST.  
 THIS LIST SHOULD NOT CONTAIN AN INSTANCE NAME AND SHOULD NAME THE SERVERNAME FIELD [ServerName]
 i.e. SELECT xxx AS ServerName
"

$results = Invoke-Sqlcmd -Query $query -ServerInstance $server -Database $dbname;
#$ResultsLen = $results.Length


$resultsLen = 0
if ($results -is [system.array])
{
 $ResultsLen += $results.Length
}
else
{
 if ($results)
 {
  $ResultsLen += 1
 }
 else
 {
  Write-Host "No servers found in the inventory to inspect"
  break
 }
}


$currentRow = 0
$LastPercentComplete = 0
$sd = Get-Date -format G
[datetime]$DateStart = Get-Date -format G
Write-Host "Starting: " $sd
Write-Host "Total Server Count = $ResultsLen"

<#########################################
 ## LOOP OVER RECORDS AND WRITE TO FILE ##
 #########################################>
foreach ($result in $results) 
{
 <############################
  ##    DISPLAY PROGRESS    ##
  ############################>
 $currentRow = $currentRow +1
 $PercentComplete = [math]::floor(($CurrentRow/$ResultsLen)*100)
 if($PercentComplete % 10 -eq 0 -and $LastPercentComplete -ne $PercentComplete -and $PercentComplete -ne 100)
 {
  [datetime]$DateNow = Get-Date -format G

  $diff = $DateNow-$DateStart
  $DiffSeconds = $diff.TotalSeconds
  
  $SecondsPerRecord = $DiffSeconds/$currentRow
  $RemainingSeconds = $SecondsPerRecord*($ResultsLen-$currentRow)
  $RemainingMinutes = new-timespan -seconds $RemainingSeconds
  $SecondsPerRecord = [Math]::Round($SecondsPerRecord,2)
  Write-Host "Progress: $PercentComplete %.  Current Row: $currentRow.   Estimated Time Remaining: $RemainingMinutes   Seconds/Record: $SecondsPerRecord   Run Time (seconds): $DiffSeconds"

  Remove-Variable DateNow
  Remove-Variable DiffSeconds
  Remove-Variable SecondsPerRecord
  Remove-Variable RemainingSeconds
  Remove-Variable RemainingMinutes
  
 }
 ##Write-Host "Current Row: $currentRow"
 $LastPercentComplete = $PercentComplete

 <################################
  ## ASSIGN THE SERVER TO CHECK ##
  ################################>
 $server = $result.ServerName  #Do not put an instance here...this is just the root server

 try
 {
  try
  {
   <#################################################################
    ##  WE ARE DOING A TRY CATCH HERE BECAUSE ON SOME OLD SYSTEMS  ## 
    ##  THE numberOfCores AND NumberOfLogicalProcessors FIELDS     ##
    ##  DO NOT EXIST, WE WILL CATCH THESE AND RESOLVE THEM BELOW   ##
    #################################################################>
   $property = "systemname","maxclockspeed","addressWidth", "numberOfCores", "NumberOfLogicalProcessors"
   $CPUresults = Get-WmiObject -ComputerName $server -class win32_processor -Property $property -ErrorAction Stop | Select-Object -Property $property

   <###############################################################
    ##  IF MULTIPLE CPUs EXIST THEY WILL BE PLACED IN AN ARRAY.  ##
    ##  WE WILL NEED TO LOOP OVER THE ARRAY TO GET COUNTS.       ##
    ###############################################################>
   if ($CPUresults -is [system.array])
   {
    $NumberOfCores = 0
    $NumberOfLogicalProcessors = 0
    $CPUCount = $CPUresults.Count
    foreach ($CPUresult in $CPUresults) 
    {
     <#####################
      ## WRITE VARIABLES ##
      #####################>
      $SystemName = $CPUresult.systemname
      $MaxclockSpeed = $CPUresult.maxclockspeed
      $AddressWidth = $CPUresult.addressWidth
      $NumberOfCores += $CPUresult.numberOfCores
      $NumberOfLogicalProcessors += $CPUresult.NumberOfLogicalProcessors
    }
    if ($NumberOfCores -eq 0)
    {
     $NumberOfCores = $CPUresults.Count
     $NumberOfLogicalProcessors = $CPUresults.Count
    }

   }
   <############################################################
    ##  IF ONLY 1 CPU EXISTS WE CAN JUST DISPLAY THE RESULTS  ##
    ############################################################>
   else 
   {
    $SystemName = $CPUresults.systemname
    $MaxclockSpeed = $CPUresults.maxclockspeed
    $AddressWidth = $CPUresults.addressWidth
    $NumberOfCores = $CPUresults.numberOfCores
    $NumberOfLogicalProcessors = $CPUresults.NumberOfLogicalProcessors
    $CPUCount = 1

   }
  }
  catch
  {
   <###################################################################
    ##  MOST LIKELY THE numberOfCores AND NumberOfLogicalProcessors  ## 
    ##  FIELDS WERE MISSING, TRYING AGAIN WITHOUT THEM               ##
    ##  (ASSUME 1 CORE PER CPU)                                      ##
    ###################################################################>
   $property = "systemname","maxclockspeed","addressWidth", "SocketDesignation"
   $CPUresults = Get-WmiObject -ComputerName $server -class win32_processor -Property $property -ErrorAction Stop | Select-Object -Property $property

   if ($CPUresults -is [system.array])
   {
    $SystemName = $CPUresults[0].systemname
    $MaxclockSpeed = $CPUresults[0].maxclockspeed
    $AddressWidth = $CPUresults[0].addressWidth
    $NumberOfCores = $CPUresults.Count
    $NumberOfLogicalProcessors = $CPUresults.Count
    $CPUCount = $CPUresults.Count
   }
   else 
   {
    $SystemName = $CPUresults.systemname
    $MaxclockSpeed = $CPUresults.maxclockspeed
    $AddressWidth = $CPUresults.addressWidth
    $NumberOfCores = "1"
    $NumberOfLogicalProcessors = "1"
    $CPUCount = "1"
   }  
  }

  <######################
   ## WRITE DATA ROWS ##
   ######################>
  $row = $table.NewRow()
  $row.Server = $SystemName
  $row.maxclockspeed = $MaxclockSpeed
  $row.addressWidth = $AddressWidth
  $row.numberOfCores = $NumberOfCores
  $row.NumberOfLogicalProcessors = $NumberOfLogicalProcessors
  $row.CPUCount = $CPUCount
  $table.Rows.Add($row)
  
  Remove-Variable SystemName
  Remove-Variable MaxclockSpeed
  Remove-Variable AddressWidth
  Remove-Variable NumberOfCores
  Remove-Variable NumberOfLogicalProcessors
  Remove-Variable CPUCount
    
 }
 catch 
 {
  <######################
   ## WRITE ERROR ROWS ##
   ######################>
  $row = $table.NewRow()
  $row.Server = $server
  $row.maxclockspeed = "ERROR"
  $row.addressWidth = ""
  $row.numberOfCores = ""
  $row.NumberOfLogicalProcessors = ""
  $row.CPUCount = ""
  $table.Rows.Add($row)
  Write-Host $_.Exception.Message
 }
}

$table | Out-GridView

Remove-Variable result
Remove-Variable results
Remove-Variable CPUresult
Remove-Variable CPUresults
Remove-Variable property
Remove-Variable row
Remove-Variable table