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.

[Corbett]

I rewrote the UpdateDellWarrantyInfo utility so it now uses Dell's web service rather than the support site. The download is attached to the first post of this thread.

Please note that I had to change the config file to store a reference to the web service.

[dteague]

Corbett wrote:
I rewrote the UpdateDellWarrantyInfo utility so it now uses Dell's web service rather than the support site. The download is attached to the first post of this thread.

Please note that I had to change the config file to store a reference to the web service.

Tried and am not get it to work...

Windows 7 (My Client)
The application has failed to start because its side-by-side configuration is in
correct. Please see the application event log or use the command-line sxstrace.e
xe tool for more detail.

Windows 2003 (LanSweeper server)
The system cannot execute the specified program.

Any ideas what I'm doing wrong?

[Corbett]

dteague wrote:

Tried and am not get it to work...

Windows 7 (My Client)
The application has failed to start because its side-by-side configuration is in
correct. Please see the application event log or use the command-line sxstrace.e
xe tool for more detail.

Windows 2003 (LanSweeper server)
The system cannot execute the specified program.

Any ideas what I'm doing wrong?

Were you successfully running the old version of the utility? It requires .NET Framework 3.5. Do you have that already?

I also noticed that if you don't first edit the .config file with the correct SQL Server connection information you'll get an error that says "This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem."

[dteague]

Corbett wrote:
Were you successfully running the old version of the utility? It requires .NET Framework 3.5. Do you have that already?

I also noticed that if you don't first edit the .config file with the correct SQL Server connection information you'll get an error that says "This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem."

Yeah, the old one was working fine. I just checked, and .NET 3.5 SP1 (as well as 4.0) is installed.

[Corbett]

dteague wrote:
Yeah, the old one was working fine. I just checked, and .NET 3.5 SP1 (as well as 4.0) is installed.

I managed to get the same error on Windows 7 64bit: "The application has failed to start because its side-by-side..." with an invalid .config file.

Double check the connectionString portion of the config file. It should be in the format of:

<add key="connectionString" value="Data Source=SERVER;Initial Catalog=LSDB;Persist Security Info=True;User ID=SQLUSER;Password=SQLPASSWORD;Connection Timeout=400" />

substituting the appropriate settings from your environment for the bold portions.

If that doesn't solve it, look in the Application event log for clues.

[dteague]

Corbett wrote:

<add key="connectionString" value="Data Source=SERVER;Initial Catalog=LSDB;Persist Security Info=True;User ID=SQLUSER;Password=SQLPASSWORD;Connection Timeout=400" />

substituting the appropriate settings from your environment for the bold portions.

If that doesn't solve it, look in the Application event log for clues.

Thanks... I had left the <>'s in for all of them... So I had (for example) <lansweeperdb> instead of just lansweeperdb.

Stupid mistake . Thanks for updating the script and figuring out my issue!

[aeneal]

I'm having a similar issue that dteague is having. I get this error:

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

Can someone help...I'm not that knowledgeable about Powershell scripts.

[dteague]

I keep getting this error...

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script fil
e, or operable program. Check the spelling of the name, or if a path was included, verif
y that the path is correct and try again.
At C:\Downloads\dell.ps1:16 char:24
+ $SQLQry = Invoke-Sqlcmd <<<<  -SuppressProviderContextWarning -ServerInstance $SQLSVR 
-Database $DB -Username $User -Password $Pwd -Query $Query
    + CategoryInfo          : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoun 
   dException
    + FullyQualifiedErrorId : CommandNotFoundException
:

Anyone have any ideas? I have tried this on both a Windows 7 machine, and a Windows 2003 SP2 box.

[synergisadmin]

I believe you have to download the SQL 2008 R2 Management pack and then the Feature Pack for PowerShell.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978 (Search on Power Shell and Management Tools for 2008 on that webpage)

EDIT - Confirmed you need

1) Shared Management Tool (for 2008)
2) PowerShellTools for SQL

Both can be found via the link above.

[dteague]

synergisadmin wrote:
I believe you have to download the SQL 2008 R2 Management pack and then the Feature Pack for PowerShell.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978 (Search on Power Shell and Management Tools for 2008 on that webpage)

EDIT - Confirmed you need

1) Shared Management Tool (for 2008)
2) PowerShellTools for SQL

Both can be found via the link above.

Well, the issue I have is my SQL server is on the corporate SQL server, and I can't run scripts on it. When I tried to download these tools, it seems they wanted SQL server loaded on the computer I was running the script (they errored on the install because it could not find a local SQL server).