Wednesday, April 1, 2015

Dell Warranty Tool in DCIS 4.0 Does Not Write to ConfigMgr DB

The latest version of the Dell Command Integration Suite 4.0 for System Center (link) includes the Warranty Tool similar to the previous versions.  But it does not write to the ConfigMgr DB.  This is due to a compatibility issue with the 2012 R2 DB and will hopefully be resolved in a near future version of the tool.

For now, here is a script that will help you import the results file from the Warranty Tool into your ConfigMgr 2012 DB.  This script was shared with me by a customer who has been using it.


DECLARE     @xml XML

set   @xml =
      (
      select BulkColumn
      from  openrowset(BULK 'C:\ProgramData\Dell\CommandIntegrationSuite\WarrantyInformation_03_05.xml',SINGLE_CLOB) as x
      )

select
            T.c.value('(ServiceTag/text())[1]','Varchar(30)') as ServiceTag
            ,T.c.value('(SystemID/text())[1]','Varchar(30)') as SystemID
            ,T.c.value('(Buid/text())[1]','Varchar(30)') as Buid
            ,T.c.value('(Region/text())[1]','Varchar(30)') as Region
            ,T.c.value('(LOB/text())[1]','Varchar(30)') as LOB
            ,T.c.value('(SystemModel/text())[1]','Varchar(30)') as SystemModel
            ,T.c.value('(SystemShipDate/text())[1]','datetime') as ShipDate
            ,U.v.value('(Provider/text())[1]','Varchar(30)') as Provider
            ,U.v.value('(ServiceLevelDescription/text())[1]','Varchar(50)') as Description
            ,U.v.value('(EntitlementType/text())[1]','Varchar(30)') as EntitlementType
            ,U.v.value('(StartDate/text())[1]','datetime') as WarrantyStartDate
            ,U.v.value('(EndDate/text())[1]','datetime') as WarrantyEndDate
            ,U.v.value('(DaysLeft/text())[1]','Int') as DaysLeft
from @xml.nodes('Warranty/Asset') T(c)
cross apply T.c.nodes('Entitlement') U(v)

order by ServiceTag, WarrantyEndDate

13 comments:

  1. Thanks so much for posting this. I wanted to share a couple of things. First, the ORDER BY doesn't seem to handle large recordsets very well. Removing it was the difference between a 20 second query and a 10+ hour query on a large amount of records. Making it a subselect helped but I just removed it for my purposes.

    Second, I took that SQL an incorporated it into a PowerShell script to automate:
    1. Running the tool to get all of the data into a new XML
    2. Copying the XML to the SQL Server (since that is the easiest way for SQL to process the XML)
    3. Running Invoke-Sqlcmd to populate the data into a custom table.

    First, you have to create the table:
    CREATE TABLE [dbo].[DellWarrantyInformation](
    [ServiceTag] [varchar](30) NOT NULL,
    [SystemID] [varchar](50) NULL,
    [Build] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [LOB] [varchar](50) NULL,
    [SystemModel] [varchar](50) NULL,
    [ShipDate] [datetime] NULL,
    [ServiceLevelCode] [varchar](50) NULL,
    [ServiceLevelDescription] [varchar](255) NULL,
    [Provider] [varchar](50) NULL,
    [WarrantyStartDate] [datetime] NULL,
    [WarrantyEndDate] [datetime] NULL,
    [WarrantyDaysLeft] [int] NULL,
    [EntitlementType] [varchar](30) NULL
    ) ON [PRIMARY]

    (Character limit reached)

    ReplyDelete
  2. Next, the PowerShell code (I had to pull some comments out to get under the character limit):
    Set-StrictMode -version 2
    $ErrorActionPreference = "Stop"

    [string]$Month = (get-date -f MM)
    [string]$Day = (get-date -f dd)
    [string]$WarrantyFileName = "WarrantyInformation_" + $Month + "_" + $Day + ".xml"
    #Write-Host $WarrantyFileName

    #Run the command to get the updated Warranty data into an XML file
    & "C:\Program Files (x86)\Dell\CommandIntegrationSuite\DellWarranty-CLI.exe" get_info internal_sccm_db config_file="C:\Program Files (x86)\Dell\CommandIntegrationSuite\DellWarrantyconfig2.cfg"

    #Copy the new XML file so it is local to the SQL Server
    Copy-Item -Path "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName" -Destination "\\SERVERNAME\E$\DellWarrantyTest\$WarrantyFileName" -Force

    #exit

    [string]$SQLCMDImportDellWarrantyData =
    "DECLARE @xml XML
    set @xml =
    (
    select BulkColumn
    from openrowset(BULK 'E:\DellWarrantyTest\$WarrantyFileName',SINGLE_CLOB) as x
    )

    insert into [XXXAssetInfo].[dbo].[DellWarrantyInformation]
    ([ServiceTag],
    [SystemID],
    [Build],
    [Region],
    [LOB],
    [SystemModel],
    [ShipDate],
    [Provider],
    [ServiceLevelCode],
    [ServiceLevelDescription],
    [EntitlementType],
    [WarrantyStartDate],
    [WarrantyEndDate],
    [WarrantyDaysLeft]
    )
    select TOP 200000
    T.c.value('(ServiceTag/text())[1]','Varchar(30)') as ServiceTag
    ,T.c.value('(SystemID/text())[1]','Varchar(30)') as SystemID
    ,T.c.value('(Buid/text())[1]','Varchar(30)') as Build
    ,T.c.value('(Region/text())[1]','Varchar(30)') as Region
    ,T.c.value('(LOB/text())[1]','Varchar(30)') as LOB
    ,T.c.value('(SystemModel/text())[1]','Varchar(30)') as SystemModel
    ,T.c.value('(SystemShipDate/text())[1]','datetime') as ShipDate
    ,U.v.value('(Provider/text())[1]','Varchar(30)') as Provider
    ,U.v.value('(ServiceLevelCode/text())[1]','Varchar(50)') as ServiceLevelCode
    ,U.v.value('(ServiceLevelDescription/text())[1]','Varchar(50)') as ServiceLevelDescription
    ,U.v.value('(EntitlementType/text())[1]','Varchar(30)') as EntitlementType
    ,U.v.value('(StartDate/text())[1]','datetime') as WarrantyStartDate
    ,U.v.value('(EndDate/text())[1]','datetime') as WarrantyEndDate
    ,U.v.value('(DaysLeft/text())[1]','Int') as WarrantyDaysLeft
    from @xml.nodes('Warranty/Asset') T(c)
    cross apply T.c.nodes('Entitlement') U(v)"

    #Clear out old data
    Invoke-Sqlcmd -query 'TRUNCATE TABLE DellWarrantyInformation' -database 'XXXAssetInfo' -serverinstance 'SERVERNAME'

    #Run the command to add the new data
    Invoke-Sqlcmd -query $SQLCMDImportDellWarrantyData -database 'XXXAssetInfo' -serverinstance 'SERVERNAME'
    CD C:

    ReplyDelete
  3. @Matt Broadstock can you send me the script via email? cfreeman21[at]gmail.com

    ReplyDelete
  4. thanks for this. I had to add

    (Get-Content "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName") | ForEach-Object { $_ -replace '&', ' ' } > "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName"

    to strip out the &'s in the xml

    ReplyDelete
  5. @Matt Broadstock can you send me the script via email? telliott@cj.com

    ReplyDelete
  6. Hi, great bit of code, but I am having problems when I run the Script. I'm getting the below error. If I remove the date fields, it works.

    Any idea?
    Thanks!!!

    Invoke-Sqlcmd : Conversion failed when converting date and/or time from character string.
    Get-Dell-Info.ps1:67 char:1
    + Invoke-Sqlcmd -query $SQLCMDImportDellWarrantyData -database 'xx_xxx' -serverins ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

    ReplyDelete
    Replies
    1. try this for the datetime fields:

      CONVERT(datetime, U.v.value('(StartDate/text())[1]','varchar(10)'), 103) as WarrantyStartDate

      Delete
  7. This script seems to have stop working once dell took this site down. http://xserv.dell.com/services/AssetService.asmx how do we get this script to work and populate the database now.... Looks like need to use this site now? https://api.dell.com/support/v2/assetinfo/header/tags.xml?svctags={svctags}&apikey={apikey}

    Please advise... Thanks!

    ReplyDelete
    Replies
    1. The current Warranty Tool will no longer work. The backend service has been taken down. Unfortunately, we do not have a replacement tool yet. The newer api does work but requires an API key that you have to go through a registration process for. Not a popular decision but nothing I can do about this. Here is the only info I have at the moment. http://en.community.dell.com/dell-groups/supportapisgroup/

      Delete
    2. Brad, I've tried to implement this without success and sent the request to Dell API Dispatch - any ideas what's involved in getting this over the line? Very frustrating with the conflicting information on the Dell site.

      Thanks Damian

      Delete
    3. Thanks Brad, Very frustrating as spent many hours trying to get it working - can't find any mention of this being retire on the Dell site > http://en.community.dell.com/techcenter/enterprise-client/w/wiki/7533.dell-command-integration-suite-for-system-center

      Have filled out the API Dispatch request, let's hope all is not lost.

      Do you know of anyone who's recently been able to implement this successfully (since June)

      Delete
    4. Damian, I checked with that team and found that they are inundated with requests. They are working their way through the queue but it may take a little time.

      Delete
  8. Can anybody see dellwarrantyinformation table in database of sccm 1511?

    ReplyDelete