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!

---

marcusoh.blogspot.com