Skip to main content

sccm: integrating dell warranty data into configmgr

UPDATE: forgot to add the preparatory step to randomize the datescriptran values.
UPDATE: greg ramsey asked me to post the script so here it is. click HERE. it's called
UPDATE: somehow i included scott’s file in my zip which caused some confusion. i have since removed it so if you need it, be sure to visit his blog for the freshest version. thanks dionna for pointing it out!

this is a process for joining dell warranty information to your configmgr discovery and inventory database. challenges in the past have been that dell was not very forthcoming with providing an easy to query, easy to access warranty database. often times it had to be delivered to you in a spreadsheet or scraped off a webpage which made for inconsistent data updates and constant script failures.



in my time as an administrator, there have been many iterations of this that i have used. the first one was a little vbscript i wrote which would take a text dump i would get from dell, read in the values, and insert the records into a table. while this worked, it was most cumbersome getting dell to send me the files on a routine basis.

the second one, a bit more elaborate, was a powershell script i wrote which would go to a webpage with the specified serial number and scrape the information. this process worked relatively well, except that their webpage format changed constantly causing the script to error out more often than not.



hopefully this is the last iteration. this version is a play on the version created by scott keiffer. he created a module which utilizes a web service that dell set up. (you didn't know there was a web service?) along with the module, scott wrote up a powershell script that retrieves warranty data and inserts it into a sql table.

there's two ways to get the data included in his solution. one is done client side, the other server side. my preference is the server side method, but i wanted to change things up a bit so that it worked in a manner that i was more comfortable with. in particular, i wanted to implement it so that it would do the following:

  • do not drop the database table on every execution
  • only create the table when it doesn't already exist
  • update records that exist and insert new ones that do not
  • update stale records

almost every change i had to make was inside of the sql query statements so for the most part, it would have been fairly basic for most sql dbas. i, however, am NO dba hence the effort required for me was much greater than i care to admit. :-|


script changes

my script alterations were minor. i think you'll agree. here's a short run down with the why:

  • $tablequery (62) - this is a variable in which i'm holding a sql statement that creates the table IF the table DOES NOT already exist. this is handled in a sql if statement. (the original script dropped the table and created a new one each time.)
  • $dellquery (81) - this variable also stores a sql statement. the query looks for any system that is matches a dell profile AND does not have a warranty end date in the dellwarrantyinfo table OR has a datescriptran value older than a randomized number. (the original script simply pulled back any system that matched a dell profile.)
  • check for null value or empty arrays (102) - this is just a slight modification to adjust to the kind of data that is pulled in from the $dellquery change. i didn't correct the error statements yet when no systems are detected for updating. i guess i'm being lazy since it works just fine.
  • update statement instead of insert (114) - i added an if statement here to handle how sql manages the record. if it doesn't exist, insert it. if it does, update it. i figure the tradeoff in not having to gather the warranty data and create a new record each time it runs (in environments with a lot of systems) is fair for having to lookup whether the record exists or not.


preparatory step

as you can imagine, all of the datescriptran values would be identical for anything that ran during the first execution. this doesn't bode well for updating a group of systems at a time since they would all evaluate to having old warranty data at some point in the future. so... after your first execution, you will need to run the following sql statement which will randomize the date value to some degree:

UPDATE DellWarrantyInfo
SET [DateScriptRan] = DateAdd(dd, -(ABS(CHECKSUM(NEWID())) % 180 + 23), GetDate())

this makes the values somewhere less than or equal to 180 and greater than or equal to 23. you can move the values around to whatever is appropriate for you.


the script

ready to play? i cannot reiterate how IMPORTANT it is to test, test, test in a lab environment. there are too many environmental differences to run things blindly. i don't even know if it runs in my environment correctly. ;)

DELL Warrenty Info - v1.0
Scott Keiffer, 2011 (skeiffer_A_T_cm_D_O_T_utexas_D_O_T_edu)
Marcus C. Oh, 8/10/2011 (marcus.oh_at_g_mail_dot_com)
A few minor changes for the script to work in update mode

License: GPL v2 or later

This script uses the get-dellwarranty function and SQL to get and store the warranty information for all dell systems in a specified ConfigMgr site.

I am not responsible with any problems this script may cause you.
param ([parameter(Mandatory=$true)][String]$SQLServer, [parameter(Mandatory=$true)][String]$SiteCode)

# SQL function, 1 connection per command, may want to break that up but too lazy.
function Invoke-SqlQuery
[Parameter(Mandatory=$true)] [string]$ServerInstance,
[Parameter(Mandatory=$true)] [string]$Query,

try {
$ConnectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;Connect Timeout=$ConnectionTimeout"
$conn=new-object System.Data.SqlClient.SQLConnection
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
Write-Output ($ds.Tables[0])
finally {

# ---- Main ----

$ConfigMgrDatabase = "SMS_$SiteCode"
$warrantyDatabase = "DellWarrantyInfo"
$tableName = "DellWarrantyInfo"
$scriptPath=Split-Path -parent $MyInvocation.MyCommand.Definition

#Import warranty function
Import-Module "$scriptPath\DellWarrantyInfoFunction.psm1"

Write-Host "--- Dell Warranty Info SQL population script ---"

# create or recreate main table
Write-Verbose "Recreating main table..."

$TableQuery = @"
Not Exists ( select * from sys.tables where name = 'DellWarrantyInfo' )
create table DellWarrantyInfo ( ResourceID int, ComputerName varchar(40),
DateScriptRan datetime, DaysLeft int, DellIBU varchar(16),
[Description] varchar(40), EndDate datetime, Provider varchar(16),
ServiceTag varchar(16), ShipDate datetime, StartDate datetime,
SystemType varchar(40), WarrantyExtended int);
grant select on DellWarrantyInfo to smsschm_users,webreport_approle

Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query $TableQuery
if(!$?) { Write-Error "There was a problem creating or recreating the main table" }

# get a list of dell computers in the site
Write-Host "Obtaining list of Dell systems..."

$DellQuery = @"
SELECT DISTINCT sys.netbios_name0 as ComputerName,
sys.ResourceID, bios.SerialNumber0 as ServiceTag
FROM v_R_System sys
LEFT OUTER JOIN DellWarrantyInfo as dw on sys.ResourceID = dw.resourceid
INNER JOIN v_GS_PC_BIOS as bios on bios.ResourceID = sys.ResourceID
WHERE bios.Manufacturer0 like '%Dell%'
AND ( dw.EndDate IS NULL
OR dw.EndDate = ''
OR dw.DateScriptRan < DateAdd(dd, -(Round((407-322) * RAND() + 322,0)), GetDate()) )

$dellSystems = Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query $DellQuery
if(!$? -or !$dellSystems) { Write-Error "There was a problem receiving the list of Dell systems." }

#progressbar variables
$length = $dellSystems.count / 100
if ($length -eq 0) { $length=1/100 }

#if array is of length 0 the foreach clause still runs with a null value. If check to fix.
if($dellSystems.count -gt 0 -OR $dellSystems.IsNull("ServiceTag") -eq $false -OR $dellSystems -ne $null)
Write-Host "Gathering warranty information..."
foreach ($dellSystem in $dellSystems)
#draws the progressbar based on the current count / (length/100)
Write-Progress "Processing..." "$($dellSystem.ComputerName)" -perc ([Int]$count++/$length)
$WarrantyInfo = Get-DellWarranty $dellSystem.ServiceTag -ServiceTagInput
#insert info into database
if ($WarrantyInfo) {
Write-Verbose "Issuing update on $($dellSystem.ComputerName)..."
Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query "

Not Exists (select ResourceID from DellWarrantyInfo
where ResourceID = '$($dellSystem.ResourceID)')
'$(if($WarrantyInfo.WarrantyExtended){1}else{0})' )

UPDATE $tableName
SET [ComputerName] = '$($dellSystem.ComputerName)',
[DateScriptRan] = '$(Get-Date)',
[DaysLeft] = '$($WarrantyInfo.DaysLeft)',
[DellIBU] = '$($WarrantyInfo.Region)',
[Description] = '$($WarrantyInfo.Description)',
[EndDate] = '$($WarrantyInfo.EndDate)',
[Provider] = '$($WarrantyInfo.Provider)',
[ServiceTag] = '$($WarrantyInfo.ServiceTag)',
[ShipDate] = '$($WarrantyInfo.ShipDate)',
[StartDate] = '$($WarrantyInfo.StartDate)',
[SystemType] = '$($WarrantyInfo.SystemType)',
[WarrantyExtended] = '$(if($WarrantyInfo.WarrantyExtended){1}else{0})'
WHERE [ResourceID] = '$($dellSystem.ResourceID)'"

if(!$?) { Write-Error "There was a problem adding $($dellSystem.ComputerName) to the database" }
Write-Host "Script Complete."


the reports

thank you anonymous for pointing out that i completely forgot to include how to access the data. the script will create a database table inside of your sccm database. the reports included in the original script (noted under the background section) function exactly as indicated in scott's original post. just import them and use it.

have fun!


  1. Looks like an interesting script. How do I implement this in to SCCM?

  2. thanks. i updated it because of your comment -- but basically the reports included in scott's post work exactly alike here. i just modified the script to work in a different way.

  3. The account running the script needs db_owner rights in the database. I was getting duplicate records each time it ran with the account having db_datareader and db_datawriter privileges. Once I made the account a db_owner I stopped getting duplicate records.

    1. that's kind of weird. i don't recall having to do that ... but thanks for the update.

  4. Please share the step by step method how to use your script in our enviroment with SCCM to fetch Dell warranty info as the Dell Website has again changed.

    1. the steps for implementing are located here:

      my article is a revision to make it work in a "delta" like update.

  5. Also do we need to install Windows Power Shell on all Dell machines to make the script work

    1. no, it's not necessary. you only need powershell on the machine where the script is running -- which should be your configmgr server in most cases.

    2. HI,

      This is Rakesh. as per my understanding if i use the above script, no need to use scott's script which runs on client machines using SCCM software distribution method. only run the above script(created by you) on SCCM database server which provide the same information as Scott's script.
      Please correct me if my understanding is incorrect.

    3. hey rakesh -- that is correct. you can use either method. the one here uses scott's sql method. mine is modified a little bit to handle delta changes.

  6. Hi,

    this is Rakesh again. can you discribe more about your script that where i can declare parameters for database server and database name.I will appreciate your help.

  7. Hi Dude,

    This is Rakesh.I got the answer. it ran successfuly with expected outputs. Thank you so much for creating such a good script.

  8. Would there ever be any interest in correcting the problem where Dell lists the first year of warranty separate from the second and subsequent years? I modified the the old VBScript stuff to merge the dates in two rows when the descriptions were the same, but I don't know powershell well enough to do the same. This made it so that the warranty type went from the ship dat to the end date without having double rows for a single warranty type (active and future)


Post a Comment

Popular posts from this blog

how to retrieve your ip address with powershell...

update: this is how it’s performed in powershell v3 as demonstrated here.(get-netadapter | get-netipaddress | ? addressfamily -eq'IPv4').ipaddress update: this is by far the easiest.PS C:\temp> (gwmi Win32_NetworkAdapterConfiguration | ? { $_.IPAddress -ne $null }).ipaddress
are you laughing yet?  i know you probably find this topic amusing.  it's really interesting though.  whenever you get over it, i'll do this in the standard cmd.exe interpreter and then in powershell to show you what kind of coolness powershell does.done?  okay, good.  this is an interpretation of a demo that bob wells did at our smug meeting.  hope you like it.i should tell you, it's not as simple as the title would lead you to believe.  i like doing that little slight-of-hand thing since it gives the impression that i'm painting a very easy target on my back for your criticism (though it's probably true in other ways)!  the idea is that we want to retrieve just the ip ad…

understanding the “ad op master is inconsistent” alert

i use the term “understanding” loosely.  this is by far no definitive guide on this particular alert, just a few things i have picked up in my attempt to understand it.let’s look at the context of the alert:The Domain Controller's Op Master is inconsitent. See additional alerts for details.
first of all, it gives very little information.  the only particularly useful detail is that it indicates which server is having the issue.  other than that, just a spelling error as there are no additional critical alerts to look at for details.this rule, as you know, comes from a sealed mp.  therefore, we can’t modify anything in it except the overrides.  the couple i’ve tinkered with are:interval (sec) log success event to begin with, interval (sec) is just set way too high.  the default is 60 seconds.  why on earth would anyone want to know that your op master consistency may be off, every minute?  actually, i could think of a few reasons, but really, it’s overkill.  the way the script works…

sccm: content hash fails to match

back in 2008, I wrote up a little thing about how distribution manager fails to send a package to a distribution point. even though a lot of what I wrote that for was the failure of packages to get delivered to child sites, the result was pretty much the same. when the client tries to run the advertisement with an old package, the result was a failure because of content mismatch.I went through an ordeal recently capturing these exact kinds of failures and corrected quite a number of problems with these packages. the resulting blog post is my effort to capture how these problems were resolved. if nothing else, it's a basic checklist of things you can use.DETECTIONstatus messagestake a look at your status messages. this has to be the easiest way to determine where these problems exist. unfortunately, it requires that a client is already experiencing problems. there are client logs you can examine as well such as cas, but I wasn't even sure I was going to have enough material to …