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]

Hi Corbett, thanks for looking into this,
this is the result:

UpdateDellWarrantyInfo.exe -Verbose
13/01/2010 15:03:58*CON:Data Source=******;Initial Catalog=Lansweeper;Persist S
ecurity Info=True;User ID=******;Password=******;Connection Timeout=400
13/01/2010 15:03:58*SQL:SELECT TOP (1) Assetlink FROM tsysassetlinks WHERE (Vend
or LIKE 'dell%')
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection);
etc....

[Hemoco]

@mrdaytrade: do you know which type of proxyserver/authentication you are using?

[Corbett]

I took Lansweeper's suggestion to also output the stack trace. I also added an optional argument called -verbose. That will output additional information to the screen as the tool runs. Both of these together will help with debugging problems.

[mrdaytrade]

Still doesn't work for me. Our proxy settings are set via group policy under User Config - Windows Settings - Internet Explorer Maint - Connection - Proxy Settings.

I am an authenticated user, however, whenever we go out to the internet we get the proxy popup requesting our user name and password.

Update: I just made a few changes to my proxy settings... I now get this message.

1/6/2010 10:34:14 AM+Query for: xxxxxxxxx
System.Net.WebException: Unable to connect to the remote server ---> System.Net.
Sockets.SocketException: No connection could be made because the target machine
actively refused it 143.166.224.210:80
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddre
ss socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Sock
et s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state,
IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---

[Corbett]

mrdaytrade, at this point I'm not quite sure what else to do. I know, bad answer...

Can anyone help figure out why the following code isn't working? It should be able to access a website over HTTP using the default proxy server settings and the default credentials. I suspect it has to do with the type of authentication that his proxy server is doing (basic, maybe?).

                objRequest = System.Net.HttpWebRequest.Create(_strURL);
                //Handle proxy servers
                objRequest.Proxy = System.Net.WebRequest.DefaultWebProxy;
                objRequest.Proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;                

                objResponse = objRequest.GetResponse();
                System.IO.Stream objStream = objResponse.GetResponseStream();
                System.IO.StreamReader objReader = new System.IO.StreamReader(objStream);
                _strHTMLSource = objReader.ReadToEnd();

mrdaytrade wrote:
Still doesn't work for me. Our proxy settings are set via group policy under User Config - Windows Settings - Internet Explorer Maint - Connection - Proxy Settings.

I am an authenticated user, however, whenever we go out to the internet we get the proxy popup requesting our user name and password.

Update: I just made a few changes to my proxy settings... I now get this message.

1/6/2010 10:34:14 AM+Query for: xxxxxxxxx
System.Net.WebException: Unable to connect to the remote server ---> System.Net.
Sockets.SocketException: No connection could be made because the target machine
actively refused it 143.166.224.210:80
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddre
ss socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Sock
et s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state,
IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---

[Hemoco]

@Corbett maybe you can also output the stacktrace in case of error so you can get the linenumber.

[pvanlien]

I did not use any parameters. just ran the .exe

[pvanlien]

Hi corbett.

I somehow get an error listed below, any idea?

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run
Behavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe
havior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S
tring method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at UpdateDellWarrantyInfo.Program.Main(String[] args)

thanks in advance

[Tomdm]

pvanlien wrote:
Hi corbett.

I somehow get an error listed below, any idea?

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
....

FYI i get exactly the same info, i tried without options and with the "-all" option, no difference

[Corbett]

Tomdm, if you are using the 1.6 (Beta) version there should be a stack trace to tell me which line number is failing. Also, running with -verbose should help troubleshoot the problem. It appears that the SQL statement isn't formatted correctly, the -verbose option will output the SQL statement prior to executing it. Thanks.

Tomdm wrote:

FYI i get exactly the same info, i tried without options and with the "-all" option, no difference