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
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.
ReplyDeleteSecond, 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)
Next, the PowerShell code (I had to pull some comments out to get under the character limit):
ReplyDeleteSet-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:
@Matt Broadstock can you send me the script via email? cfreeman21[at]gmail.com
ReplyDeletethanks for this. I had to add
ReplyDelete(Get-Content "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName") | ForEach-Object { $_ -replace '&', ' ' } > "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName"
to strip out the &'s in the xml
@Matt Broadstock can you send me the script via email? telliott@cj.com
ReplyDeleteHi, 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.
ReplyDeleteAny 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
try this for the datetime fields:
DeleteCONVERT(datetime, U.v.value('(StartDate/text())[1]','varchar(10)'), 103) as WarrantyStartDate
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}
ReplyDeletePlease advise... Thanks!
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/
DeleteBrad, 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.
DeleteThanks Damian
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
DeleteHave 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)
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.
DeleteCan anybody see dellwarrantyinformation table in database of sccm 1511?
ReplyDelete