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 MCO_DellWarranty.zip
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.

 

history

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.

 

background

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.

http://xserv.dell.com/services/assetservice.asmx

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

Notes:
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.

Disclaimer:
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
{
param(
[Parameter(Mandatory=$true)] [string]$ServerInstance,
[string]$Database,
[Parameter(Mandatory=$true)] [string]$Query,
[Int32]$QueryTimeout=600,
[Int32]$ConnectionTimeout=15
)

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


# ---- 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 = @"
IF
Not Exists ( select * from sys.tables where name = 'DellWarrantyInfo' )
BEGIN
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
END
"
@

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 }
$count=1

#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 "

IF
Not Exists (select ResourceID from DellWarrantyInfo
where ResourceID = '$($dellSystem.ResourceID)')
BEGIN
INSERT INTO $tableName VALUES (
'$($dellSystem.ResourceID)',
'$($dellSystem.ComputerName)',
'$(Get-Date)',
'$($WarrantyInfo.DaysLeft)',
'$($WarrantyInfo.Region)',
'$($WarrantyInfo.Description)',
'$($WarrantyInfo.EndDate)',
'$($WarrantyInfo.Provider)',
'$($WarrantyInfo.ServiceTag)',
'$($WarrantyInfo.ShipDate)',
'$($WarrantyInfo.StartDate)',
'$($WarrantyInfo.SystemType)',
'$(if($WarrantyInfo.WarrantyExtended){1}else{0})' )

END
ELSE
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!

Comments

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

    ReplyDelete
  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.

    ReplyDelete
  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.

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

      Delete
  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.

    ReplyDelete
    Replies
    1. the steps for implementing are located here: http://myitforum.com/cs2/blogs/skeiffer/archive/2011/05/11/dell-warranty-info.aspx

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

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

    ReplyDelete
    Replies
    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.

      Delete
    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.

      Delete
    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.

      Delete
  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.

    ReplyDelete
  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.

    ReplyDelete
  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)

    ReplyDelete

Post a Comment

Popular posts from this blog

using preloadpkgonsite.exe to stage compressed copies to child site distribution points

UPDATE: john marcum sent me a kind email to let me know about a problem he ran into with preloadpkgonsite.exe in the new SCCM Toolkit V2 where under certain conditions, packages will not uncompress.  if you are using the v2 toolkit, PLEASE read this blog post before proceeding.   here’s a scenario that came up on the mssms@lists.myitforum.com mailing list. when confronted with a situation of large packages and wan links, it’s generally best to get the data to the other location without going over the wire. in this case, 75gb. :/ the “how” you get the files there is really not the most important thing to worry about. once they’re there and moved to the appropriate location, preloadpkgonsite.exe is required to install the compressed source files. once done, a status message goes back to the parent server which should stop the upstream server from copying the package source files over the wan to the child site. anyway, if it’s a relatively small amount of packages, you can

How to Identify Applications Using Your Domain Controller

Problem Everyone has been through it. We've all had to retire or replace a domain controller at some point in our checkered collective experiences. While AD provides very intelligent high availability, some applications are just plain dumb. They do not observe site awareness or participate in locating a domain controller. All they want is the name or IP of one domain controller which gets hardcoded in a configuration file somewhere, deeply embedded in some file folder or setting that you are never going to find. How do you look at a DC and decide which applications might be doing it? Packet trace? Logs? Shut it down and wait for screaming? It seems very tedious and nearly impossible. Potential Solution Obviously I wouldn't even bother posting this if I hadn't run across something interesting. :) I ran across something in draftcalled Domain Controller Isolation. Since it's in draft, I don't know that it's published yet. HOWEVER, the concept is based off

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.   DETECTION status messages take 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 m