Update LS database with warranty information from Dell's site

[Corbett]

FYI: Lansweeper 5.0 which is currently in beta will have this feature build-in for Dell, HP, Lenovo

This isn't exactly a "custom action" but this topic seems the best. I wrote an application that when executed it will update the Purchase Date and Warranty Date custom fields of all Dell computers in the LS database. It accomplishes this by making an HTTP request to Dell's support site and pulling down the appropriate dates. Some assumptions are made here: The oldest date found on the support page is the purchase date and the most recent date found on the page is the warranty expiration date.

EDIT (3/21/2012): Dell changed their support site which broke the program. The program now uses the web service that Dell provides for getting warranty information. Due to this change a new line needs to be added to the .config file:

<add key="DellWarrantyURL" value="http://xserv.dell.com/services/assetservice.asmx"/>

This is a .NET application and therefore requires Framework 3.5.

Before running the program be sure to edit the .config file and set the connectionString option with the appropriate SQL Server computer name, database name, username and password.

In my situation I used the Task Scheduler to run this application once a week (UpdateDellWarrantyInfo -all) to make sure all computers have the most recent warranty information and once a day
(UpdateDellWarrantyInfo -warrantyInfo) to get the warranty information for new computers.

12/21/2009, 4:15PM ET - Added most recent version of utility.
12/24/2009, 10:50AM ET - Attached most recent version (v1.4) of utility and updated the documentation.
01/04/2010, 1:30PM ET - Fixed bug where invalid Asset Tags (AAAAAA5 for example) would throw an exception. It now just skips it and keeps going. Attached v1.5.
01/13/2010, 10:07AM ET - Added a proxy authentication mechanism (integrated authentication only), fixed a SQL statement that failed on SQL 2000, added -verbose option for troubleshooting (rewrote argument handling routines), added stack trace to error output for additional troubleshooting. Attached v1.6.
01/18/2010, 8:16AM ET - Rewrote date handling routine to better handle different date formats. Attached v1.7.
07/02/2010, 12:16PM ET - I haven't had much time to maintain this lately. Attached v1.7 of the source.
03/21/2012, 8:26AM ET - Rewrote the warranty fetch routine to use the Dell web service. Note this version now requires a DellWarrantyURL key in the .config file. Attached v2.0.

Usage: UpdateDellWarrantyInfo [options] [-verbose]

Options

-all
Will update the warranty information for all Dell computers in the Lansweeper
database.

-warrantyInfo
Will update the warranty information only for the Dell computers that have
empty warranty and purchase dates in the Lansweeper database.

-assetTag <assetTag>
Will update the warranty information for a single Dell computer based on the
asset tag.  Must provide a single asset tag.

-computer <computerName>
Will update the warranty information for a single Dell computer based on the
computer name.  Must provide a single computer name.

-verbose
Print troubleshooting information to the console.

-help
Will display this screen.

NOTE: If no options are provided, will update all Dell computers.

[rddelossantos]

This script worked for me too. Thank you!

[jradcliffe]

How / where do you run this? I am not familiar with Power-Shell at all, but would very much like to have our dell warranty information up-to-date again.

[rddelossantos]

jradcliffe wrote:
How / where do you run this? I am not familiar with Power-Shell at all, but would very much like to have our dell warranty information up-to-date again.

Copy and paste the script on notepad and save it on your desktop with a .ps1 extention name. If you are using Windows 7, it has a built-in powershell which you can use. Right click on the xxx.ps1 (that you saved) and choose edit. It should open up a powershell editor. Change $SQLSVR = "sqlserver" to your SQL database server name. Also change $DB, $User and $Pwd with your database name, user account to login to DB and DB password respectively. Save it and in the editor itself there's a "play button" (green), click it and you'll see that there are results on the bottom pane.

Thanks.

[staadmin]

from What LS suppoort is saying, this appears to be an issue with the change of Dell's web site. When I run this tool the Purchase date is OK but the warranty date is showing as -Warranty Date: '1/1/0001' Anyone have any idea how to fix this tool to show correct Warranty date? Any help is appreciated!

[sandve]

staadmin wrote:
from What LS suppoort is saying, this appears to be an issue with the change of Dell's web site. When I run this tool the Purchase date is OK but the warranty date is showing as -Warranty Date: '1/1/0001' Anyone have any idea how to fix this tool to show correct Warranty date? Any help is appreciated!

Here's a PowerShell script that will update the DB with info from Dells webservice.

[aeneal]

sandve wrote:

staadmin wrote:
from What LS suppoort is saying, this appears to be an issue with the change of Dell's web site. When I run this tool the Purchase date is OK but the warranty date is showing as -Warranty Date: '1/1/0001' Anyone have any idea how to fix this tool to show correct Warranty date? Any help is appreciated!

Here's a PowerShell script that will update the DB with info from Dells webservice.

Hello sandve

I downloaded the powershell script, however I receive the following error. Can you please help with this:

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
verify that the path is correct and try again.
At C:\LS_DellWarrantyInfo\LS_DellWarrantyInfo.ps1:48 char:18
+ Invoke-Sqlcmd <<<< -SuppressProviderContextWarning $SQLcommand
+ CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

[ahahum]

sandve wrote:

staadmin wrote:
from What LS suppoort is saying, this appears to be an issue with the change of Dell's web site. When I run this tool the Purchase date is OK but the warranty date is showing as -Warranty Date: '1/1/0001' Anyone have any idea how to fix this tool to show correct Warranty date? Any help is appreciated!

Here's a PowerShell script that will update the DB with info from Dells webservice.

The script looks good! Thank you for posting it!

Anyone give me a tip on how to modify this to use the lansweeper database on a remote SQL server?

I'm not very acclimated to powershell yet...it's on the to-do list.

Thanks!

[pdumbleton]

ahahum wrote:

sandve wrote:

staadmin wrote:
from What LS suppoort is saying, this appears to be an issue with the change of Dell's web site. When I run this tool the Purchase date is OK but the warranty date is showing as -Warranty Date: '1/1/0001' Anyone have any idea how to fix this tool to show correct Warranty date? Any help is appreciated!

Here's a PowerShell script that will update the DB with info from Dells webservice.

The script looks good! Thank you for posting it!

Anyone give me a tip on how to modify this to use the lansweeper database on a remote SQL server?

I'm not very acclimated to powershell yet...it's on the to-do list.

Thanks!

I was able to change the code slightly to allow for connection to a remote sqlserver.

# Updates LanSweeper database with Dell warranty information
# Some code taken from http://poshcode.org/2482

Get-PSSnapin –registered
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
Get-PSSnapin SQL*

cls
$SQLSVR = "Server\Instance"
$DB = "lansweeperdb"
$User = "username"
$Pwd = "Password"
$Query = "SELECT SerialNumber, Computername FROM lansweeperdb.dbo.tblSystemEnclosure WHERE LEN(serialnumber) = 7 AND Manufacturer LIKE 'Dell%'"

$SQLQry = Invoke-Sqlcmd -SuppressProviderContextWarning -ServerInstance $SQLSVR -Database $DB -Username $User -Password $Pwd -Query $Query

Foreach ($n in $SQLQry){
$SrvTag = $n | select -ExpandProperty serialnumber
$CompName = $n | select -ExpandProperty Computername

Write-Host
Write-Host
Write-Host $SrvTag ":" $CompName

$DummyGUID = New-Object GUID('11111111-1111-1111-1111-111111111111')
$AppName = "LS_DellWarrantyInfo.ps1"
$proxy = New-WebServiceProxy -URI 'http://xserv.dell.com/services/AssetService.asmx'
$proxy.GetAssetInformation($DummyGUID, $AppName, $SrvTag) | ForEach-Object {
$PurchaseDate = $_.AssetHeaderData.SystemShipDate
$WarrantyEndDate = get-date('02/23/1965')
Write-Host
Write-Host "Information for Service Tag" $_.AssetHeaderData.ServiceTag
Write-Host "Model:" $_.AssetHeaderData.SystemType $_.AssetHeaderData.SystemModel
Write-Host "Ship Date:" $_.AssetHeaderData.SystemShipDate.ToString('d')
$_ |
Select-Object -ExpandProperty Entitlements |
Select-Object -Property ServiceLevelDescription,Provider,StartDate,EndDate | ForEach-Object {
"{0,20}{1,-50}" -f "Entitlement:",$_.ServiceLevelDescription.Split(',')[1]
"{0,20} {1,-50}" -f "Provided by:",(% { if ($_.Provider -eq 'DELL') { 'Dell' } else {'Unisys'} })
"{0,20} {1,-50}" -f 'Starts:',$_.StartDate.ToString('d')
"{0,20} {1,-50}" -f 'Ends:',$_.EndDate.ToString('d')
if ($_.EndDate -gt $WarrantyEndDate) { $WarrantyEndDate = $_.EndDate }
}
Write-Host "Warranty End Date:" $WarrantyEndDate.ToString('d')
}
$SQLcommand = "UPDATE dbo.tblCompCustom SET Warrantydate = '$WarrantyEndDate', PurchaseDate = '$PurchaseDate' WHERE Computername = '$CompName' IF @@ROWCOUNT = 0 Insert into dbo.tblCompCustom (Computername, Warrantydate, Purchasedate) Values ('$CompName', '$WarrantyEndDate', '$PurchaseDate')"
Write-Host $SQLcommand
Invoke-Sqlcmd -SuppressProviderContextWarning -ServerInstance $SQLSVR -Database $DB -Username $User -Password $Pwd $SQLcommand
}

This worked for me.

[pcully]

Hi All
New to this forum and have a question.
We have been running Lanseeper now for eighteen months with no problems.
As we are a predominantly Dell environment the warrant checking application has been a boon, however as of Monday we have all 800 odd purchase dates showing 05/11/11 and no warranty information.
Is this something that others are experiencing or has something broken?
Peter

[bpc]

This application is still working for us on lansweeper 4.2.0.56 despite Dell's recent redesign. It appears Dell is still maintaining the legacy version of the support site as well, but I imagine that won't always be the case. I also found this powershell script that would be a solid replacement, just need to modify the SQL commands for the lansweeper database. http://marcusoh.blogspot.com/2011/08/sccm-integrating-dell-warranty-data.html