MSEndpointMgr

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.

(12041)

Maurice Daly

Maurice has been working in the IT industry for the past 20 years and currently working in the role of Senior Cloud Architect with CloudWay. With a focus on OS deployment through SCCM/MDT, group policies, active directory, virtualisation and office 365, Maurice has been a Windows Server MCSE since 2008 and was awarded Enterprise Mobility MVP in March 2017. Most recently his focus has been on automation of deployment tasks, creating and sharing PowerShell scripts and other content to help others streamline their deployment processes.

14 comments

  • Hi Maurice,

    Super nice PowerBI report! I’m trying to tweak the report to suite my needs, but I can’t figure out how and where the data from ServiceTagSummary comes from – Can you help me on this one?

  • It’s work fine for me.
    If anyone to receive the following error on Dell Command Warranty, check the internet access to URL: api.dell.com/*

    DC-DWRE04: Error retrieving warranty data
    Error (System.ArgumentNullException) at DC-DWRE04:
    Value cannot be null.
    Parameter name: source

  • Just what i’m looking for however, do you have this in a regular SQL report that i can add to configmgr?

    • Hi Sean,

      Unfortunately not as this was intended to demonstrate PowerBI as a reporting tool, however the warranty checker utility can populate a SQL DB and you could query the DB in a SSRS custom report then.

      Maurice

  • Maybe I am not getting something, I copied the script, modified it for my db site, db and a UNC path to store the CSV file. I ran the task, it completed, no CSV was generated. Last run result shows “Incorrect function. (0x8007001). I can’t see anything else. Something is clearly not right I am just not sure what. The task and script was ran with an account that has access to the SQL database.

    • Hi David,

      The first thing you should try in troubleshooting is to run the Dell Warranty utility directly to see if the CSV is generated:

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

      Regards

      Maurice

      • I fixed it, it was a typo :). Thanks for the response. Another question though, how can I pull the device name with this tool and add it to my report?

  • Can you push data from the warranty tool directly into the cm database so that you can integrate it into normal sql reports?

    • Editing the ConfigMgr SQL DB or adding to it in this method is completely unsupported.

  • Why export to csv? wouldnt it be better to put it directly into a seperate sql database, and use the powerbi report directly from there? it allows yo publish the powerbi into Teams, and use the gateway for daily refresh for those that need it.

    • It is which ever you are comfortable doing, if you want to create a separate database then you can use the /Ocs switch with the Dell Warranty utility and do so.

      Just remember that you will need sufficient SQL licensing in that instance also.

      • Already have a license with the sql server for sccm 🙂

        What i did was i got a public API key from Dell, and used it with Mike’s https://gallery.technet.microsoft.com/Dell-Warranty-Bulk-Import-bc0e4d47 powershell script to check sccm database tags once a day and update the table accordingly.

        Then i just createad a powerbi that connects to the sql and all members of the teams site can check warranty status that is daily refreshed:)

      • You must understand that not everyone has spare SQL licensing capacity, hence a flat format provides a free alternative. It’s also worth noting that under the license for SQL with SCCM, you are not entitled to use additional databases in that instance either.

        If you have SQL enterprise licensing of course, then the world is your oyster.

Sponsors