asked on

Batch Query Dell Service Tags using powershell

Hi there

I've got an inventory Powershell script I've been working on...getting ideas from here and other sites. One of the items it pulls for me is the Server Serial number or service tag. 99% of our servers are Dell so what i'm wonder is if theres a way i can batch process the info from the service tag (mainly the start and end dates of the warrenty) and include this info into the spread sheet the script already creates.

As you can see from the code (still work in progress) it pulls the service tag number and on line 12 and places it into column B of the spreadsheet.

I'm aware that the Dell service tag site is:

http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=SVCTAG

and you would change the SVCTAG for the actual service tag but what i'm looking for is how to get that info inot the spreadsheet...if at all possible.

$xl = New-Object -comobject Excel.Application
$xl.visible = $True

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet3"}
$s3.delete()
$ws = $wb.Worksheets.Item(1)

$ws.name = "General Info"

$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Service Tag"
$ws.Cells.Item(1,3) = "Manufacturer"
$ws.Cells.Item(1,4) = "Model"
$ws.Cells.Item(1,5) = "System Type"
$ws.Cells.Item(1,6) = "IP Address"
$ws.Cells.Item(1,7) = "Subnet"
$ws.Cells.Item(1,8) = "Default Gateway"
$ws.Cells.Item(1,9) = "MAC Address"
$ws.Cells.Item(1,10) = "Install Date"
$ws.Cells.Item(1,11) = "Operating System"
$ws.Cells.Item(1,12) = "Service Packs"
$ws.Cells.Item(1,13) = "Memory (GB)"
$ws.Cells.Item(1,14) = "Processors"
$ws.Cells.Item(1,15) = "Processor Type/Speed"
$ws.Cells.Item(1,16) = "Last Reboot Time"
$ws.Cells.Item(1,17) = "Report Time Stamp"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
$OS = get-wmiobject Win32_OperatingSystem -computername $strComputer
$Computer = get-wmiobject Win32_computerSystem -computername $strComputer
$Bios = get-wmiobject Win32_bios -computername $strComputer
$ProcType = get-wmiobject Win32_Processor -computername $strcomputer | select name
# [reflection.assembly]::loadwithpartialname('system.windows.forms');
# [system.Windows.Forms.MessageBox]::show($ProcType.name)
$NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
foreach($NIC in $NICCard){
if($NIC.ipenabled -eq $true)
{

$ws.Cells.Item($intRow,1) = $strComputer.Toupper()
$ws.Cells.Item($intRow,2) = $Bios.serialnumber
$ws.Cells.Item($intRow,3) = $Computer.Manufacturer
$ws.Cells.Item($intRow,4) = $Computer.Model
$ws.Cells.Item($intRow,5) = $Computer.SystemType
$ws.Cells.Item($intRow,6) = $NIC.IPaddress[0]
$ws.Cells.Item($intRow,7) = $NIC.IPSubnet
$ws.Cells.Item($intRow,8) = $NIC.DefaultIPGateway
$ws.Cells.Item($intRow,9) = $NIC.MACAddress
$ws.Cells.Item($intRow,10) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
$ws.Cells.Item($intRow,11) = $OS.Caption
$ws.Cells.Item($intRow,12) = $OS.CSDVersion
$ws.Cells.Item($intRow,13) = "{0:N0}" -f ($computer.TotalPhysicalMemory/1GB)
$ws.Cells.Item($intRow,14) = $Computer.NumberOfProcessors
$ws.Cells.Item($introw,15) = $ProcType.name
$ws.Cells.Item($intRow,16) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
$ws.Cells.Item($intRow,17) = Get-date

$intRow = $intRow + 1
$d.EntireColumn.AutoFit()
}
}
}

#---------Create New WorkSheet------------------------------

$ws = $wb.Worksheets.Item(2)

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Machine Name"
$ws.Cells.Item(1,2) = "Drive"
$ws.Cells.Item(1,3) = "Total size (MB)"
$ws.Cells.Item(1,4) = "Free Space (MB)"
$ws.Cells.Item(1,5) = "Free Space (%)"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$servers = get-content c:\serverlist.txt
foreach ($strcomputer in $servers)
{
$objDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3"
foreach ($objdisk in $objDisks)
{
 $ws.Cells.Item($intRow, 1) = $strComputer.ToUpper()
 $ws.Cells.Item($intRow, 2) = $objDisk.DeviceID
 $ws.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB)
 $ws.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB)
 $ws.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$intRow = $intRow + 1
}
}

Open in new window

Chris Dent

Here you go.

It should be possible to add that in as you've done with the other fields :) If Dell doesn't have an entry for it the snippet below won't return anything.

Chris

Function ConvertFrom-Html {
  Param(
    [String]$Html
  )

  $HtmlLines = $Html -Split "<tr" | Where-Object { $_ -Match '<td' }
  $Header = ($HtmlLines[0] -Split '<td') -Replace '[\w\s\d"=%:;\-]*>|</.*' | Where-Object { $_ -ne '' }

  For ($i = 1; $i -lt $HtmlLines.Count; $i++) {
    $Output = New-Object Object
    $Values = ($HtmlLines[$i] -Split '<td') -Replace '[\w\s\d"=%:;\-]*>|</.*|<a.+?>'
    For ($j = 1; $j -lt $Values.Count; $j++) {
      $Output | Add-Member NoteProperty $Header[$j - 1] -Value $Values[$j]
    }
    $Output
  }
}

$URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=abcdef"
$String = (New-Object Net.WebClient).DownloadString($URL)

If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
  ConvertFrom-Html $Matches[0]
}