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: http://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 – http://www.scconfigmgr.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.

(1418)

Maurice Daly

Maurice has been working in the IT industry since 1999 and was awarded his first MVP Enterprise Mobility award in 2017. Technology focus includes Active Directory, Group Policy, Hyper-V, Windows Deployment (SCCM & MDT) and Office 365.

comments
  • Thomas
    Posted at 13:56 November 16, 2017
    Thomas
    Reply
    Author

    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.

    • Maurice Daly
      Posted at 15:19 November 16, 2017
      Maurice Daly
      Reply
      Author

      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.

      • Thomas
        Posted at 19:13 November 16, 2017
        Thomas
        Reply
        Author

        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:)

        • Maurice Daly
          Posted at 19:51 November 16, 2017
          Maurice Daly
          Reply
          Author

          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.

  • Dewayne
    Posted at 15:29 November 16, 2017
    Dewayne
    Reply
    Author

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

    • Maurice Daly
      Posted at 15:43 November 16, 2017
      Maurice Daly
      Reply
      Author

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

  • Silvia
    Posted at 17:13 November 16, 2017
    Silvia
    Reply
    Author

    You’ve been rocking these PowerBi reports lately, I love it! <3

  • David
    Posted at 21:50 November 29, 2017
    David
    Reply
    Author

    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.

    • Maurice Daly
      Posted at 23:53 November 29, 2017
      Maurice Daly
      Reply
      Author

      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

      • David
        Posted at 22:01 November 30, 2017
        David
        Reply
        Author

        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?

  • Leave a Reply