tag:blogger.com,1999:blog-283174281155343165.post4665118683812827325..comments2023-04-11T08:59:39.063-05:00Comments on Brad's IT JumpBag: Dell Warranty Tool in DCIS 4.0 Does Not Write to ConfigMgr DBBrad Goodmanhttp://www.blogger.com/profile/14090073622729821752noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-283174281155343165.post-24997547914841182522017-02-08T01:41:22.819-06:002017-02-08T01:41:22.819-06:00Can anybody see dellwarrantyinformation table in d...Can anybody see dellwarrantyinformation table in database of sccm 1511?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-30002033545258768362016-08-04T14:27:25.328-05:002016-08-04T14:27:25.328-05:00Damian, I checked with that team and found that th...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.Brad Goodmanhttps://www.blogger.com/profile/14090073622729821752noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-61507949064426726722016-08-03T06:45:36.299-05:002016-08-03T06:45:36.299-05:00Thanks Brad, Very frustrating as spent many hours ...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<br /><br />Have filled out the API Dispatch request, let's hope all is not lost.<br /><br />Do you know of anyone who's recently been able to implement this successfully (since June)Anonymoushttps://www.blogger.com/profile/09515757953977119053noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-13069866247874797332016-08-03T06:43:21.904-05:002016-08-03T06:43:21.904-05:00Brad, I've tried to implement this without suc...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.<br /><br />Thanks DamianAnonymoushttps://www.blogger.com/profile/09515757953977119053noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-33946100960499682042016-06-22T12:09:06.732-05:002016-06-22T12:09:06.732-05:00The current Warranty Tool will no longer work. Th...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/Brad Goodmanhttps://www.blogger.com/profile/14090073622729821752noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-88731988694220878912016-06-08T09:50:12.993-05:002016-06-08T09:50:12.993-05:00This script seems to have stop working once dell t...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}<br /><br />Please advise... Thanks!Anonymoushttps://www.blogger.com/profile/00106589473806853318noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-87654525317901487302016-05-11T22:00:26.621-05:002016-05-11T22:00:26.621-05:00try this for the datetime fields:
CONVERT(datetim...try this for the datetime fields:<br /><br />CONVERT(datetime, U.v.value('(StartDate/text())[1]','varchar(10)'), 103) as WarrantyStartDateAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-21418390967668393192016-02-12T09:15:49.464-06:002016-02-12T09:15:49.464-06:00Hi, great bit of code, but I am having problems wh...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.<br /><br />Any idea?<br />Thanks!!!<br /><br />Invoke-Sqlcmd : Conversion failed when converting date and/or time from character string.<br />Get-Dell-Info.ps1:67 char:1<br />+ Invoke-Sqlcmd -query $SQLCMDImportDellWarrantyData -database 'xx_xxx' -serverins ...<br />+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br /> + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException<br /> + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommandAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-67340422324408943812016-02-04T11:05:26.404-06:002016-02-04T11:05:26.404-06:00@Matt Broadstock can you send me the script via em...@Matt Broadstock can you send me the script via email? telliott@cj.comAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-46468491596014297772015-11-24T19:16:23.683-06:002015-11-24T19:16:23.683-06:00thanks for this. I had to add
(Get-Content "...thanks for this. I had to add<br /><br />(Get-Content "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName") | ForEach-Object { $_ -replace '&', ' ' } > "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName"<br /><br />to strip out the &'s in the xmlstempesthttps://www.blogger.com/profile/03726407934979520309noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-67352294395431352672015-09-09T09:35:10.215-05:002015-09-09T09:35:10.215-05:00@Matt Broadstock can you send me the script via em...@Matt Broadstock can you send me the script via email? cfreeman21[at]gmail.comAnonymoushttps://www.blogger.com/profile/00106589473806853318noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-46395808052662156232015-05-23T18:50:48.745-05:002015-05-23T18:50:48.745-05:00Next, the PowerShell code (I had to pull some comm...Next, the PowerShell code (I had to pull some comments out to get under the character limit):<br />Set-StrictMode -version 2<br />$ErrorActionPreference = "Stop"<br /><br />[string]$Month = (get-date -f MM)<br />[string]$Day = (get-date -f dd)<br />[string]$WarrantyFileName = "WarrantyInformation_" + $Month + "_" + $Day + ".xml"<br />#Write-Host $WarrantyFileName<br /><br />#Run the command to get the updated Warranty data into an XML file<br />& "C:\Program Files (x86)\Dell\CommandIntegrationSuite\DellWarranty-CLI.exe" get_info internal_sccm_db config_file="C:\Program Files (x86)\Dell\CommandIntegrationSuite\DellWarrantyconfig2.cfg"<br /><br />#Copy the new XML file so it is local to the SQL Server<br />Copy-Item -Path "C:\ProgramData\Dell\CommandIntegrationSuite\$WarrantyFileName" -Destination "\\SERVERNAME\E$\DellWarrantyTest\$WarrantyFileName" -Force<br /><br />#exit<br /><br />[string]$SQLCMDImportDellWarrantyData =<br />"DECLARE @xml XML<br />set @xml =<br /> (<br /> select BulkColumn<br /> from openrowset(BULK 'E:\DellWarrantyTest\$WarrantyFileName',SINGLE_CLOB) as x<br /> )<br /><br />insert into [XXXAssetInfo].[dbo].[DellWarrantyInformation]<br /> ([ServiceTag],<br /> [SystemID],<br /> [Build],<br /> [Region],<br /> [LOB],<br /> [SystemModel],<br /> [ShipDate],<br /> [Provider],<br /> [ServiceLevelCode],<br /> [ServiceLevelDescription],<br /> [EntitlementType],<br /> [WarrantyStartDate],<br /> [WarrantyEndDate],<br /> [WarrantyDaysLeft]<br /> )<br />select TOP 200000<br /> T.c.value('(ServiceTag/text())[1]','Varchar(30)') as ServiceTag<br /> ,T.c.value('(SystemID/text())[1]','Varchar(30)') as SystemID<br /> ,T.c.value('(Buid/text())[1]','Varchar(30)') as Build<br /> ,T.c.value('(Region/text())[1]','Varchar(30)') as Region<br /> ,T.c.value('(LOB/text())[1]','Varchar(30)') as LOB<br /> ,T.c.value('(SystemModel/text())[1]','Varchar(30)') as SystemModel<br /> ,T.c.value('(SystemShipDate/text())[1]','datetime') as ShipDate<br /> ,U.v.value('(Provider/text())[1]','Varchar(30)') as Provider<br /> ,U.v.value('(ServiceLevelCode/text())[1]','Varchar(50)') as ServiceLevelCode<br /> ,U.v.value('(ServiceLevelDescription/text())[1]','Varchar(50)') as ServiceLevelDescription<br /> ,U.v.value('(EntitlementType/text())[1]','Varchar(30)') as EntitlementType<br /> ,U.v.value('(StartDate/text())[1]','datetime') as WarrantyStartDate<br /> ,U.v.value('(EndDate/text())[1]','datetime') as WarrantyEndDate<br /> ,U.v.value('(DaysLeft/text())[1]','Int') as WarrantyDaysLeft<br />from @xml.nodes('Warranty/Asset') T(c)<br />cross apply T.c.nodes('Entitlement') U(v)"<br /><br />#Clear out old data<br />Invoke-Sqlcmd -query 'TRUNCATE TABLE DellWarrantyInformation' -database 'XXXAssetInfo' -serverinstance 'SERVERNAME'<br /><br />#Run the command to add the new data<br />Invoke-Sqlcmd -query $SQLCMDImportDellWarrantyData -database 'XXXAssetInfo' -serverinstance 'SERVERNAME'<br />CD C:<br />Matt Broadstockhttps://www.blogger.com/profile/13841910796336229131noreply@blogger.comtag:blogger.com,1999:blog-283174281155343165.post-31258534811375121122015-05-23T18:45:10.362-05:002015-05-23T18:45:10.362-05:00Thanks so much for posting this. I wanted to share...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.<br /><br />Second, I took that SQL an incorporated it into a PowerShell script to automate:<br />1. Running the tool to get all of the data into a new XML<br />2. Copying the XML to the SQL Server (since that is the easiest way for SQL to process the XML)<br />3. Running Invoke-Sqlcmd to populate the data into a custom table.<br /><br />First, you have to create the table:<br />CREATE TABLE [dbo].[DellWarrantyInformation](<br /> [ServiceTag] [varchar](30) NOT NULL,<br /> [SystemID] [varchar](50) NULL,<br /> [Build] [varchar](50) NULL,<br /> [Region] [varchar](50) NULL,<br /> [LOB] [varchar](50) NULL,<br /> [SystemModel] [varchar](50) NULL,<br /> [ShipDate] [datetime] NULL,<br /> [ServiceLevelCode] [varchar](50) NULL,<br /> [ServiceLevelDescription] [varchar](255) NULL,<br /> [Provider] [varchar](50) NULL,<br /> [WarrantyStartDate] [datetime] NULL,<br /> [WarrantyEndDate] [datetime] NULL,<br /> [WarrantyDaysLeft] [int] NULL,<br /> [EntitlementType] [varchar](30) NULL<br />) ON [PRIMARY]<br /><br />(Character limit reached)Matt Broadstockhttps://www.blogger.com/profile/13841910796336229131noreply@blogger.com