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.

[Tomdm]

after some time 1.6.1 sill gave me a date format error, i was a bit too fast to say that all problems were solved, however 1.6.2beta seems to have processed already more then 100 PCs without problems at the moment. So for me the problems seems to be gone

[Corbett]

Thanks for letting me know that it isn't quite right. I completely rewrote the date handling to hopefully work with different date formats better. Can you please try out this version and let me know?

Thanks.

Tomdm wrote:
after some time 1.6.1 sill gave me a date format error, i was a bit too fast to say that all problems were solved, however 1.6.2beta seems to have processed already more then 100 PCs without problems at the moment. So for me the problems seems to be gone

[Martin]

Thanks for your reply and help. Still no joy though...

15/01/2010 08:47:15*CON:Data Source=Marge\marge_2005;Initial Catalog=LansweeperDB;Integrated Security=True;Connection Timeout=400
15/01/2010 08:47:15*SQL:SELECT TOP 1 Assetlink FROM tsysassetlinks WHERE (Vendor LIKE 'dell%')
15/01/2010 08:47:15*URL:http://support.euro.dell.com/support/topics/topic.aspx/emea/shared/support/my_systems_info/en/details?c=uk&l=en&s=bsd&~tab=1&ServiceTag={assettag}
15/01/2010 08:47:15*SQL:SELECT Computername, IdentifyingNumber FROM dbo.tblComputersystemProduct where vendor like '%dell%' and identifyingnumber <> '' and identifyingnumber is not null order by computername
15/01/2010 08:47:15+Query for: 36CP93J
15/01/2010 08:47:17 -Purchase Date: '28/11/2007'
15/01/2010 08:47:17 -Warranty Date: '28/11/2010'
15/01/2010 08:47:17*SQL:begin tran;update dbo.tblCompCustom with (serializable) set PurchaseDate=CONVERT(DATETIME,'28/11/2007',102), Warrantydate=CONVERT(DATETIME,'28/11/2010',102) where computername = '36CP93J';if @@rowcount = 0 begin insert into dbo.tblCompCustom (Computername, PurchaseDate, Warrantydate) values ('36CP93J',CONVERT(DATETIME,'28/11/2007',102),CONVERT(DATETIME,'28/11/2010',102)) end;commit tran;
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime datatype resulted in an out-of-range datetime value.
The statement has been terminated.

Thanks anyway,
Martin

[Tomdm]

Martin, try to download version 1.6.1 this seems to have solved the problem for me....

Thx Corbett

[Martin]

Hi Corbett,
it seems to be a really great tool. Thank you. Unfortunately I'm having the same isuue as Tomdm. I ran your 1.6.1BETA version and this is the output:

14/01/2010 15:29:38*CON:Data Source=Marge\marge_2005;Initial Catalog=LansweeperDB;Integrated Security=True;Connection Timeout=400
14/01/2010 15:29:38*SQL:SELECT TOP 1 Assetlink FROM tsysassetlinks WHERE (Vendor LIKE 'dell%')
14/01/2010 15:29:38*URL:http://support.euro.dell.com/support/topics/topic.aspx/emea/shared/support/my_systems_info/en/details?c=uk&l=en&s=bsd&~tab=1&ServiceTag={assettag}
14/01/2010 15:29:38*SQL:SELECT Computername, IdentifyingNumber FROM dbo.tblComputersystemProduct where vendor like '%dell%' and identifyingnumber <> '' and identifyingnumber is no
t null order by computername
14/01/2010 15:29:38+Query for: 36CP93J
14/01/2010 15:29:52 -Purchase Date: '28/11/2007'
14/01/2010 15:29:52 -Warranty Date: '28/11/2010'
14/01/2010 15:29:52*SQL:begin tran;update dbo.tblCompCustom with (serializable) set PurchaseDate=CONVERT(DATETIME,'28/11/2007',102), Warrantydate=CONVERT(DATETIME,'28/11/2010',102) where computername = '36CP93J';if @@rowcount = 0 begin insert into dbo.tblCompCustom (Computername, PurchaseDate, Warrantydate) values ('36CP93J','28/11/2007','28/11/2010') end;commit tran;
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value.
The statement has been terminated.

The system I'm running your tool from is WinXP SP3 with UK locale.
Any ideas?
Thanks,
Martin

[Corbett]

Martin, thank you for providing the full output; that is a huge help. I fixed the date formatting while updating an existing record but not when inserting a new record. Try this file and let us know how it goes.

Martin wrote:
14/01/2010 15:29:52*SQL:begin tran;update dbo.tblCompCustom with (serializable) set PurchaseDate=CONVERT(DATETIME,'28/11/2007',102), Warrantydate=CONVERT(DATETIME,'28/11/2010',102) where computername = '36CP93J';if @@rowcount = 0 begin insert into dbo.tblCompCustom (Computername, PurchaseDate, Warrantydate) values ('36CP93J','28/11/2007','28/11/2010') end;commit tran;

[Corbett]

Tomdm,
I suspect it has to do with the regional settings for your SQL server. 7/23/2009 (mm/dd/yyyy) is a valid date but 23/7/2009 (mm/dd/yyyy) is not. I never thought this would be used internationally. 🙂

Please try this updated version. If it still fails please post the -verbose output, I'm primarily interested in the last SQL statement prior to the error. Thanks.

[Tomdm]

Corbett, thanks, this seems to have resolved the problem, we are indeed running SQL2000,

HOWEVER

it seems we ran into an other problem, related to the warranty date format

System.Data.SqlClient.SqlException: The conversion of a char data type to a date
time data type resulted in an out-of-range datetime value.
The conversion of a char data type to a datetime data type resulted in an out-of
-range datetime value.
The statement has been terminated.

this is an example machine which triggers this problem:

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

[Corbett]

Correct. It seems SQL 2000 doesn't like the SELECT TOP statement with parentheses. Thankfully SQL 2005 and 2008 don't care about the missing parentheses as long as it's a simple SELECT statement with no parameters. Check out v1.6 on the top of the thread.

[Hemoco]

Might have to do with the sql server version (2000)
Maybe you can change your source code to "SELECT TOP 1 Assetlink"