Colchester_Institute
asked on
Batch Query Dell Service Tags using powershell
Hi there
I've got an inventory Powershell script I've been working on...getting ideas from here and other sites. One of the items it pulls for me is the Server Serial number or service tag. 99% of our servers are Dell so what i'm wonder is if theres a way i can batch process the info from the service tag (mainly the start and end dates of the warrenty) and include this info into the spread sheet the script already creates.
As you can see from the code (still work in progress) it pulls the service tag number and on line 12 and places it into column B of the spreadsheet.
I'm aware that the Dell service tag site is:
http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&ServiceTag=SVCTAG
and you would change the SVCTAG for the actual service tag but what i'm looking for is how to get that info inot the spreadsheet...if at all possible.
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
}
}
ASKER
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?
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
}
ASKER
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?
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?
> 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
ASKER
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
ASKER
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
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
Can you re-post your attempt and we can go from there? :)
Chris
ASKER
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
}
}
}
ASKER
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!
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
}
}
ASKER
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
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
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
#
}
ASKER
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
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
#
}
ASKER
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)
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
Chris
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).DownloadStr
$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
ASKER
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
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
Do you use a proxy server? I assumed the ability to connect directly.
Chris
ASKER
yeah we do.....i've tried both different ones?
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
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
ASKER
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
so 10.10.23.2 port 8080
by pass for local is also ticked
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
ASKER
you'd be authenticated when logging in.....Plus the account i'm testing this all with is a Domain Admin account
ASKER
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
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
ASKER
nope still nothing, I'm running this using the Powershell ISE. It looks like it runs but nothing produces :-(
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
ASKER
Whoop i think you may be onto a winner :-) loads of html code :-) which means nowt to me! LOL
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
#
}
ASKER
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?
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?
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]
ASKER
OK heres the info:
<table cellpadding="0" cellspacing="0" width="100%" class="contract_table"><tr ><td class="contract_header" width="26%">Description</t d><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">DE LL</td><td class="contract_oddrow"><a title="System i
s not enrolled to receive notification prior to service contract expiration.">No</a></td><t d class="contract_oddrow">1/ 4/2006</td ><td class="contract_oddrow
">1/4/2010</td><td class="contract_oddrow"><f ont color="red"><b>0</b></font ></td></tr ></table>
<table cellpadding="0" cellspacing="0" width="100%" class="contract_table"><tr
eader" width="14%">Provider</td><
td><td class="contract_header" width="14%">End Date</td><td class="contract_header" width="14%" style="border-right-width:
lass="contract_oddrow"><a style="color:black;">Next Business Day</a></td><td class="contract_oddrow">DE
s not enrolled to receive notification prior to service contract expiration.">No</a></td><t
">1/4/2010</td><td class="contract_oddrow"><f
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
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]
ASKER
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 :-)
I'm not worried about the Days left as it will be going into a spreadsheet and its the dates that matter :-)
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
ASKER
hmm not tried with yets with lots...
How should the whole code look now
How should the whole code look now
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
#
}
ASKER
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:pr oxyport")
$WebProxy.UseDefaultCreden tials = $True
$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString( $URL)
$String -Match '<table[\w\s\d"=%]*contrac t_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......
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:pr
$WebProxy.UseDefaultCreden
$WebClient = New-Object Net.WebClient
$WebClient.Proxy = $WebProxy
$String = $WebClient.DownloadString(
$String -Match '<table[\w\s\d"=%]*contrac
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......
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
ASKER
ok that sounds good......
Care to share that with me ;-) haha
Care to share that with me ;-) haha
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
#
}
ASKER
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
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
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
ASKER
Nope no errors at all?
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
ASKER
gotta be worth a try?
ASKER
its either that or output to another type of file? But dont know what?
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
#
}
ASKER
still no joy dude
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Guess What.............WE HAVE A WINNER!!!!!!!!! Well spotted Fella
Thank goodness for that :)
Chris
ASKER
indeed thank you ever so much for all your help and your patience!
ASKER
i can now do some slight tidying, auto width the columns and i'm done...Fantastic
ASKER
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
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.
on my HP server environment, the only thing that doesn't work is the "Processor Type/Speed" column so far.
ASKER
@ 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
It'll be done over by multi-processor systems, including hyper-threaded. Win32_Processor doesn't always return a single result.
Chris
Chris
@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 :-)
@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 :-)
ASKER
@JJOZ If you get any luck with that could you let me know please...Cheers
In theory, this should deal with it. Might be a bit messy though :)
Chris
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
#
}
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
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
to
$ws.Cells.Item($GeneralRow
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
$ProcType = Get-WmiObject Win32_Processor -ComputerName $strComputer | Select-Object -First 1 -ExpandProperty Name
Then the later line is only:
$ws.Cells.Item($GeneralRow
But anything goes, as long as what you have works for you :)
Chris
ASKER
oh i'll give that a try when i get back to the office
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
Open in new window