Dell Warranty Checker – ConfigMgr PowerBI Report

In case you missed it, Dell released an update to their excellent Command Integration Suite this week. As part of the update they included a new version of their warranty checker utility, thankfully this now has an inbuilt API for checking warranty info. Using this utility we can leverage information from ConfigMgr in the form of an exported CSV file, combine this with ConfigMgr SQL dat and then render a warranty report via PowerBI for a warranty dashboard.

So here we will step through how to do this.

Dell Command Integration Suite

The first step here is to upgrade to the latest version of the Dell Command Integration Suite (at the time of this post that is 5.0). This can be downloaded from the following URL: https://en.community.dell.com/techcenter/enterprise-client/w/wiki/7533.dell-command-integration-suite-for-system-center.

After installation you now have the warranty tool to leverage with ConfigMgr by using the /ICS switch and pointing it at your ConfigMgr SQL server/database . The /E= switch allows you to specify the report location as by default this goes into the ProgramData directory, however in this instance we will use a UNC share as reports will be scheduled to write to this location.

More switch information for proxy server authentication etc can be found by using the /? switch as normal.

Schedule The Warranty Information Report

Now set up a scheduled task to run the tool and place the exported CSV in our shared location. This will allow the PowerBI report to refresh the data either manually or automatically through the use a PowerBI gateway.

1. Add a scheduled task either manually or by using the following code below in a PowerShell PS1 script file. Change the YOURSERVER and YOURSHARE values to suit:

   $TaskArguments = "/Ics=" + '"' + "Data Source=YOURSQLSERVER;Database=YOURDB;Integrated Security=true;" + '"' + " /E=" + '"' + "\\YOURSERVER\YOURSHARE\DellWarranty.csv" + '"'
   $Action = New-ScheduledTaskAction -Execute 'DellWarranty-CLI.exe' -Argument $TaskArguments -WorkingDirectory "C:\Program Files (x86)\Dell\CommandIntegrationSuite"
   $Trigger = New-ScheduledTaskTrigger -At "00:00" -Daily
   $Settings = New-ScheduledTaskSettingsSet -DontStopOnIdleEnd -RestartInterval (New-TimeSpan -Minutes 5) -RestartCount 10 -StartWhenAvailable
   $Settings.ExecutionTimeLimit = "PT0S"
   $UserName = Read-Host -Prompt "Username"
   $SecurePassword = Read-Host -Prompt "Password" -AsSecureString
   $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $SecurePassword
   $Password = $Credentials.GetNetworkCredential().Password
   $Task = New-ScheduledTask -Action $Action -Trigger $Trigger -Settings $Settings
   $Task | Register-ScheduledTask -TaskName 'Dell Warranty Report Refresh' -User $UserName -Password $Password

2. When creating the task manually or via a script ensure that you have the correct authentication method set depending on your environment:

   Integrated Security
   DellWarranty-CLI.exe /Ics=”Data Source=YOURSQLSERVER;Database=YOURSCCMDB;Integrated Security=true;” /E=”\\YOURSERVER\YOURSHARE\DellWarranty.csv”

   Specified User & Password
   DellWarranty-CLI.exe /Ics=”Data Source=YOURSQLSERVER;Database=YOURSCCMDB;User ID=USERNAME;Password=USERPASSWORD;” /E=”\\YOURSERVER\YOURSHARE\DellWarranty.csv”
   

3. Test running the scheduled task and review the CSV output. You should have something like this;

   

PowerBI Report

With our exported CSV now accessible on a network share, we can set about creating a PowerBI report to allow you to drill down and graphically represent your warranty details.

Download PowerBI Desktop (if you don’t already have it installed) – https://powerbi.microsoft.com/en-us/desktop/

Download the Dell Warranty PowerBI template from Microsoft Technet – https://gallery.technet.microsoft.com/Dell-Warranty-PowerBI-f9696115

1. Run the Dell Warranty Scheduled task, or manually run the Dell Warranty utility and generate the CSV to a shared location
2. Open PowerBI Desktop
3. Click File, Open and select the PowerBI template
4. You will be prompted for SQL server details, click close

   

5. Next you will receive a warning about the sources being unavailable, again click close
   
6. Click on Edit Queries – Edit Query
7. You will now see two query sources listed
8. Highlight the DellWarranty Source
9. Click on Advanced Editor
10. Change the path to the UNC path of your Dell Warranty CSV file

    

11. Now you should see the data from the CSV

    

12. Select the SCCM-Data source and click on Advanced Editor
13. Change the SQLServer and DB values to match your environment
14. When prompted click on the Edit Permissions button

    

15. Click on the Run button to run the SQL query

    

16. You should now have data from your SQL server

    

17. Click on Close & Apply
18. Click on the Refresh button
19. You should now have a report similar to the one below:

    

20. Now you can either Publish the report directly to your Office 365 Sharepoint environment or schedule a push of data out via a PowerBI gateway.More on the PowerBI gateway feature is available on the post by Jordan Benzing – https://msendpointmgr.com/2017/11/13/keeping-data-current-with-the-powerbi-gateway/

Warranty State Calculated Field

You can edit the warranty state calculated field to suit your own needs;

Warranty State = If([Warranty Remaining]<=0,”Warranty Expired”,If([Warranty Remaining]<1,”Warranty Expiring”,If([Warranty Remaining]<3,”Warranty Renewal Due”,If([Warranty Remaining]>3,”In Warranty”))))

Interactive Report

You should now have a report that allows you to filter based on the machine type, the type of warranty, location etc.

Update – 17/11/2017

Some tweaks and graphics have been added by Jordan and myself, so the template has been updated.