Solved

Batch Query Dell Service Tags using powershell

Posted on 2010-09-21
68
2,077 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Colchester_Institute
  • 34
  • 31
  • 3
68 Comments
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33724593

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

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33724783
hey hey thank you....I assume i can copy and paste this on the script I've already got and just tap it on the end? although I will need to assign it a column within the Spreadsheet?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33724859

It should fit in like this.

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

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

  # Construct the URL:

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

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

    $Support = ConvertFrom-Html $Matches[0]

    # Write the fields to Excel here:
    #
    # $Support.Description
    # $Support.Provider
    # $Support."Warranty Extension Notice *"
    # $Support."Start Date"
    # $Support."End Date"
    # $Support."Days Left"

  }

# End of Computer Loop
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33725287
ok i've tried that...but nothing new appeared within the spreadsheet...or is that because i'm being a tad stupid and at lines 134-141 i have to specify the columns like i did earlier for the various bits of info.

In which case I could specify another worksheet within the spread like ive done for "Drive Info" so the spreadsheet would have Three Worksheets, the first containing the General Info, The second sheet containing the Drive Info and the third containing the Warranty info? or is that over complicating it?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33725350

> i have to specify the columns like i did earlier for the various bits of info.

Yep, sorry, I didn't put it in a fully working state :)

> The second sheet containing the Drive Info and the third containing the Warranty info? or is that over complicating it?

That's fine, however you prefer to see the information really. I kind of thought you might end up with it on another tab :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33725400
ok coolio i'll see if i can get me head around getting it on to another sheet and let you know how i get on
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33726100
nope i dont seem to get any out into sheet 3 (to be fair its prob how i'm writing it as no i only seem to get a couple of servers in the "drive Info" sheet and then a couple in the "Warranty"  but showing the drive info and not Warranty......Yes i'm having a bad day)

The out I'd be looking for is:
Servername (like i have been doing)

and then the warranty info:
Description
Provider
Start Date
End Date
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33726171

Can you re-post your attempt and we can go from there? :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33726246
Haha yeah good luck.....As i know i've made a big-ish error somewhere??


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

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$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) = "Server 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
}

  # Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

$intRow = 2
{
  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

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

    $Support = ConvertFrom-Html $Matches[0]

    # Write the fields to Excel here:
    
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

$intRow = 2
{
    $ws.Cells.Item($intRow,1) = $strComputer.ToUpper()
    $ws.Cells.Item($intRow,2) = $Support.Description
    $ws.Cells.Item($intRow,3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($intRow,4) = $Support."Start Date"
    $ws.Cells.Items($intRow,5) = $Support."End Date"
    # $Support."Days Left"
$intRow = $intRow + 1
  }

# End of Computer Loop
}
}
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33726267
Oh and be patient i'm a relative Noob to powershell only been doing very basic stuff till now (i want to get more adventours and figured this idea was the way forward!) I'm slowly regretting that!
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33726315

Give this a shot?

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

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$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) = "Server 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
  }

  # Construct the Warranty URL:
  $ws = $wb.Worksheets.Item(3)

  $ws.Name = "Warranty"
  $ws.Cells.Item(1,1) = "Server Name"
  $ws.Cells.Item(1,2) = "Description"
  $ws.Cells.item(1,3) = "Provider"
  $ws.Cells.Item(1,4) = "Start Date"
  $ws.Cells.Item(1,5) = "End Date"
  #$ws.Cells.Item(1,5) = "Days Left(%)"

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

  $intRow = 2
  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

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

    $Support = ConvertFrom-Html $Matches[0]

    # Write the fields to Excel here:
    
    $ws = $wb.Worksheets.Item(3)

    $ws.Name = "Warranty"
    $ws.Cells.Item(1,1) = "Server Name"
    $ws.Cells.Item(1,2) = "Description"
    $ws.Cells.item(1,3) = "Provider"
    $ws.Cells.Item(1,4) = "Start Date"
    $ws.Cells.Item(1,5) = "End Date"
    #$ws.Cells.Item(1,5) = "Days Left(%)"

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

    $intRow = 2
    $ws.Cells.Item($intRow,1) = $strComputer.ToUpper()
    $ws.Cells.Item($intRow,2) = $Support.Description
    $ws.Cells.Item($intRow,3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($intRow,4) = $Support."Start Date"
    $ws.Cells.Items($intRow,5) = $Support."End Date"
    # $Support."Days Left"

    $intRow = $intRow + 1

    # End of Computer Loop
  }
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33726450
hmmm same thing happens...

When it opens sheet 2 (Drive Info) it inputs the first two servers that are in C:\Serverlist.txt but then the others it lists in the warranty section but over writing each one in turn (still with drive info and not warranty info) until it gets to the end of the text file
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33726576

Oh okay, sorry, you're resetting the row count too often :)

This should work a little better, although I haven't tested it.

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

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

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

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

    $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
    $ws.Cells.Item($WarrantyRow, 2) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 4) = $Support."Start Date"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."End Date"
    # $Support."Days Left"

    # Increment the Warranty row
    $WarrantyRow++
  }

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33726667
ok thats sorted out the 3 sheets nicely but unfortunately no data on sheet 3 (the warranty one).  Headings are all there just no info...not even the server name

0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33726788

It's possible it's not getting anything back. Lets see..

Debugging version. It'll tell you whether or not it's getting any, and the URL it's trying. That needs checking :)

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

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

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

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

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
    $ws.Cells.Item($WarrantyRow, 2) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 4) = $Support."Start Date"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."End Date"
    # $Support."Days Left"

    # Increment the Warranty row
    $WarrantyRow++
  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33732923
Nope it doesnt pull any info back.....However if i then copy and paste the URL into IE it does bring up the support page for that Service tag (obviously in the serverlist.txt i've only one server at the mo while testing but eventually it will be populated)
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33732935
Okay, I assumed the format you see would be the same as mine. This may be erroneous. I'm afraid I can't test that unless you were to give me a valid service tag :-\

Chris
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33732952

Actually, you can break it down.

Copy out the service tag, then run this bit:


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

$String


That should show you the stuff it picked up, we need to ensure that you have something there first. It'll be a massive pile of raw HTML, but anything is a good start, nothing at all is bad :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33733023
Well in that case its bad!

Ive copied what you just put replacing the final part of hte URL to a valid service tag nothing showed.

I tried with two different service tags
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733027

Do you use a proxy server? I assumed the ability to connect directly.

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33733117
yeah we do.....i've tried both different ones?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733130

Ahh okay, the web client won't be using your settings from Internet Explorer.

I need to dig into the documentation, see how a proxy can be defined.

Chris
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733139

Oh okay, I take that back, it's supposed to use settings from IE.

How do you define your proxy? Configuration script? Static name / IP?

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33733213
the setting are set in IE via GP so for example in IE under tool > Internet Options > Connections > Lan Settings....we have the IP address and the port

so 10.10.23.2 port 8080

by pass for local is also ticked
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733218

Requires authentication?

I confess I've never tried to use WebClient with a proxy, "should" work springs to mind :)

Does the proxy require authentication?

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33733231
you'd be authenticated when logging in.....Plus the account i'm testing this all with is a Domain Admin account
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33733249
i've found this

http://forums.asp.net/t/930270.aspx

any use to ya?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733256

So it doesn't prompt for a username / password when you open IE?

It's very likely it has problems with the proxy, but that's not necessarily very helpful :)

Chris
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33733276

Cool, lets try it :)

Chris
$URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=abcdef"

$WebProxy = New-Object Net.WebProxy
$WebProxy.UseDefaultCredentials = $True

$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString($URL)

$String

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33734561
nope still nothing, I'm running this using the Powershell ISE.  It looks like it runs but nothing produces :-(
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33734728

Can we try hard-setting the proxy?

Chris
$URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=abcdef"

$WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
$WebProxy.UseDefaultCredentials = $True

$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString($URL)

$String

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33735815
Whoop i think you may be onto a winner :-) loads of html code :-) which means nowt to me! LOL
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33735885

Fantastic :)

Lets plug that back into the main script.

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

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

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
    $ws.Cells.Item($WarrantyRow, 2) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 4) = $Support."Start Date"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."End Date"
    # $Support."Days Left"

    # Increment the Warranty row
    $WarrantyRow++
  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33736029
nearly.....
I get on the Warranty sheet:
Server Name
Description
Provider

But no start or end date

And the columns on the 3 sheets arent autoformating width?

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 70

Expert Comment

by:Chris Dent
ID: 33736754

The start and end date are in the web page I guess? I might need you to post me the contents of this so I can see where it's breaking down.

Obviously check to see if that contains anything sensitive before posting, but I'm pretty sure it's clean. It should be a snippet of raw HTML, it's the contents of the table from the website.

The autoformatting... I don't know much about that to be honest, the Excel object isn't my favourite nor my area of expertise. It "should" work in the same way as the others, we're doing the same thing, but there's no harm in checking through that.

Chris
$URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=abcdef"

# Download the page
$WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
$WebProxy.UseDefaultCredentials = $True

$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString($URL)

$String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>'
$Matches[0]

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33741884
OK heres the info:

<table cellpadding="0" cellspacing="0" width="100%" class="contract_table"><tr><td class="contract_header" width="26%">Description</td><td class="contract_h
eader" width="14%">Provider</td><td class="contract_header" width="15%">Warranty Extension Notice *</td><td class="contract_header" width="14%">Start Date</
td><td class="contract_header" width="14%">End Date</td><td class="contract_header" width="14%" style="border-right-width:0px;">Days Left</td></tr><tr><td c
lass="contract_oddrow"><a style="color:black;">Next Business Day</a></td><td class="contract_oddrow">DELL</td><td class="contract_oddrow"><a title="System i
s not enrolled to receive notification prior to service contract expiration.">No</a></td><td class="contract_oddrow">1/4/2006</td><td class="contract_oddrow
">1/4/2010</td><td class="contract_oddrow"><font color="red"><b>0</b></font></td></tr></table>
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33741921
Good morning,

It messes up the Days Left field a bit, but the rest work for me. Running this fails to return the right information for the dates?

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"

# Download the page
$WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
$WebProxy.UseDefaultCredentials = $True

$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString($URL)

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

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33742035
That looks like its worked...
I'm not worried about the Days left as it will be going into a spreadsheet and its the dates that matter :-)
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33742049

Hmm okay, I haven't changed anything, that "should" work as part of the main script. It fails when you run it for lots of computers?

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33742063
hmm not tried with yets with lots...

How should the whole code look now
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33742082

I think we're still on this version.

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.item(1,3) = "Provider"
$ws.Cells.Item(1,4) = "Start Date"
$ws.Cells.Item(1,5) = "End Date"
#$ws.Cells.Item(1,5) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

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

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
    $ws.Cells.Item($WarrantyRow, 2) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 3) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 4) = $Support."Start Date"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."End Date"
    # $Support."Days Left"

    # Increment the Warranty row
    $WarrantyRow++
  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33742122
grrrr Nothing in the spreadsheet for Start and End date

However if this is just run in the Powershell ISE:

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"

# Download the page
$WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
$WebProxy.UseDefaultCredentials = $True

$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString($URL)

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


Then the out is correct....So its getting the start date and end date to Move to the spreedsheet?
and have noticed that could still do with the Name and Service tag of a server to be inputted into the warranty sheet even if no warranty info....so we can easily spot that......
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33742153

Okay, I've shifted it arouund so it writes the server name and asset tag to warranty regardless of whether it finds details or not.

I've also added a Write-Host statement so we can see if it's returning the dates properly as it's running. The idea is to see if it's failing on writing that information to Excel, or failing to get it.

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33742166
ok that sounds good......

Care to share that with me ;-) haha
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33742178

Hmm attachment failure :)

Here you go.

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Asset Tag"
$ws.Cells.Item(1,3) = "Description"
$ws.Cells.item(1,4) = "Provider"
$ws.Cells.Item(1,5) = "Start Date"
$ws.Cells.Item(1,6) = "End Date"
#$ws.Cells.Item(1,7) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

  $ws = $wb.Worksheets.Item(3)
  $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
  $ws.Cells.Item($WarrantyRow, 2) = $Bios.SerialNumber

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 3) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 4) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."Start Date"
    $ws.Cells.Items($WarrantyRow, 6) = $Support."End Date"
    # $Support."Days Left"

    Write-Host "Start Date $($Support.'Start Date'); End date $($Support.'End Date')"

  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  # Increment the Warranty row
  $WarrantyRow++

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33742256
its always the attachments fault!

OK firstly, i now get the server written into the spread sheet even if no Warrenty info so Tick in that box

Secondly the Start and End Date still dont appear in the spreadsheet, however heres the info I get from the powershell Screen

Warranty: Support information for ABCDEF
Warranty: Trying to write from URL: http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=ABCDEF
Start Date 1/5/2006; End date 1/4/2010

So one success and one mini success as at least you can see its pulling the data down, its just not putting it into the Spreadsheet
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33742268

hehe, always :)

See this is why I avoid working with Excel ;) Hmmm... It doesn't throw any big red error messages while it's writing the sheet does it? :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744381
Nope no errors at all?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33744415

Hmmm okay, I don't know then I'm afraid. I can't see why it wouldn't write to Excel. We could write it in a different format (replace the / characters with - for example), but it shouldn't make any difference.

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744515
gotta be worth a try?
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744525
its either that or output to another type of file? But dont know what?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33744586

Modified format :)

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Asset Tag"
$ws.Cells.Item(1,3) = "Description"
$ws.Cells.item(1,4) = "Provider"
$ws.Cells.Item(1,5) = "Start Date"
$ws.Cells.Item(1,6) = "End Date"
#$ws.Cells.Item(1,7) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

  $ws = $wb.Worksheets.Item(3)
  $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
  $ws.Cells.Item($WarrantyRow, 2) = $Bios.SerialNumber

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 3) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 4) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Items($WarrantyRow, 5) = $Support."Start Date" -Replace '/', '-'
    $ws.Cells.Items($WarrantyRow, 6) = $Support."End Date" -Replace '/', '-'
    # $Support."Days Left"

    Write-Host "Start Date $($Support.'Start Date'); End date $($Support.'End Date')"

  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  # Increment the Warranty row
  $WarrantyRow++

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744774
still no joy dude
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
ID: 33744903

Oh no... I've just seen why... Or at least I hope I have.

Items rather than Item. Try this?

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Asset Tag"
$ws.Cells.Item(1,3) = "Description"
$ws.Cells.item(1,4) = "Provider"
$ws.Cells.Item(1,5) = "Start Date"
$ws.Cells.Item(1,6) = "End Date"
#$ws.Cells.Item(1,7) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

  $ws = $wb.Worksheets.Item(3)
  $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
  $ws.Cells.Item($WarrantyRow, 2) = $Bios.SerialNumber

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 3) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 4) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Item($WarrantyRow, 5) = $Support."Start Date"
    $ws.Cells.Item($WarrantyRow, 6) = $Support."End Date"
    # $Support."Days Left"

    Write-Host "Start Date $($Support.'Start Date'); End date $($Support.'End Date')"

  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  # Increment the Warranty row
  $WarrantyRow++

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744976
Guess What.............WE HAVE A WINNER!!!!!!!!! Well spotted Fella
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33744987

Thank goodness for that :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33744997
indeed thank you ever so much for all your help and your patience!
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 33745011
i can now do some slight tidying, auto width the columns and i'm done...Fantastic
0
 
LVL 1

Author Closing Comment

by:Colchester_Institute
ID: 33745031
Chris was fantastic in all his help and adive and made it simple and clear of what to try and do next providing me with various scripts to try
0
 
LVL 1

Expert Comment

by:jjoz
ID: 35245681
yes Chris-Dent is a legend !

on my HP server environment, the only thing that doesn't work is the "Processor Type/Speed" column so far.
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 35253882
@ JJOZ... I had/have the smae problem but could not seem to find anything anywhere that would help. Really strange as if memory serves me correct if i run the processor command on a single server it worked fine.  But put it all together and nope nothing.  In the end i gave up as th erest of the details on the inventory script i had worked which was enough for me
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 35253941
It'll be done over by multi-processor systems, including hyper-threaded. Win32_Processor doesn't always return a single result.

Chris
0
 
LVL 1

Expert Comment

by:jjoz
ID: 35254961
@Colchester: yes most of my systems here is dual sockets or more that's why it doesn't show up ?
@Chris: yes you are right, but yes many thanks for the script it works fine, it was just coincidental that I also need the CPU type for vMotion requirement so I have posted follow up post to make the script perfect :-)
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 35312714
@JJOZ If you get any luck with that could you let me know please...Cheers
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 35322013
In theory, this should deal with it. Might be a bit messy though :)

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

#
# Create the workbook
#

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

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet4"}
$s3.delete()

#
# Write headings for General
#

$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()

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

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Server 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()

# Construct the Warranty URL:
$ws = $wb.Worksheets.Item(3)

$ws.Name = "Warranty"
$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Asset Tag"
$ws.Cells.Item(1,3) = "Description"
$ws.Cells.item(1,4) = "Provider"
$ws.Cells.Item(1,5) = "Start Date"
$ws.Cells.Item(1,6) = "End Date"
#$ws.Cells.Item(1,7) = "Days Left(%)"

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

#
# Set the starting rows
#

$GeneralRow = 2
$DriveRow = 2
$WarrantyRow = 2

#
# Loop through computers
#

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
  #
  # Get some information from this computer
  #

  $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-Object -ExpandProperty Name

  #
  # Write to the first sheet
  #

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

  $NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
  foreach($NIC in $NICCard) {
    if($NIC.ipenabled -eq $true) {

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

      # Increment the General row
      $GeneralRow++

      $d.EntireColumn.AutoFit()
    }
  }

  #
  # Write to the second sheet
  #

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

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

    # Increment the Drive row
    $DriveRow++
  }

  #
  # Write to the third sheet
  #

  $ws = $wb.Worksheets.Item(3)
  $ws.Cells.Item($WarrantyRow, 1) = $strComputer.ToUpper()
  $ws.Cells.Item($WarrantyRow, 2) = $Bios.SerialNumber

  Write-Host "Warranty: Support information for $($Bios.SerialNumber)"

  $URL = "http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=$($Bios.SerialNumber)"

  # Download the page
  $WebProxy = New-Object Net.WebProxy("yourproxy:proxyport")
  $WebProxy.UseDefaultCredentials = $True

  $WebClient = New-Object Net.WebClient
  $WebClient.Proxy = $WebProxy
  $String = $WebClient.DownloadString($URL)

  If ($String -Match '<table[\w\s\d"=%]*contract_table">.+?</table>') {
    Write-Host "Warranty: Trying to write from URL: $URL"

    $Support = ConvertFrom-Html $Matches[0]

    $ws.Cells.Item($WarrantyRow, 3) = $Support.Description
    $ws.Cells.Item($WarrantyRow, 4) = $Support.Provider
    # $Support."Warranty Extension Notice *"
    $ws.Cells.Item($WarrantyRow, 5) = $Support."Start Date"
    $ws.Cells.Item($WarrantyRow, 6) = $Support."End Date"
    # $Support."Days Left"

    Write-Host "Start Date $($Support.'Start Date'); End date $($Support.'End Date')"

  } Else {
    Write-Host "Warranty: None available at URL: $URL"
  }

  # Increment the Warranty row
  $WarrantyRow++

  #
  # End of Computer Loop
  # 
}

Open in new window

0
 
LVL 1

Expert Comment

by:jjoz
ID: 35329098
you are great Chris !

For a quick fix, i'd change line 117:
$ProcType = get-wmiobject Win32_Processor -computername $strcomputer | select name
to
$ProcType = @(get-wmiobject Win32_Processor -computername $strcomputer)

I'd change line 143:
$ws.Cells.Item($GeneralRow, 15) = $ProcType.name
to
$ws.Cells.Item($GeneralRow, 15) = $ProcType[0].name
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 35330983
It'd do this instead :)

$ProcType = Get-WmiObject Win32_Processor -ComputerName $strComputer | Select-Object -First 1 -ExpandProperty Name

Then the later line is only:

$ws.Cells.Item($GeneralRow, 15) = $ProcType

But anything goes, as long as what you have works for you :)

Chris
0
 
LVL 1

Author Comment

by:Colchester_Institute
ID: 35331447
oh i'll give that a try when i get back to the office
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now