I’ve been on a bit of a reporting spree here lately and I have been giving a little bit of love to the two PowerBI templates I’ve got out on TechNet and GitHub. I can now happily say that I’ve done enough minor changes to finally make me say it’s time to release V2.0

https://gallery.technet.microsoft.com/Patch-Compliance-Reporting-95fa01f7?redir=0

Now I know what you’re thinking, you’re thinking ‘but it looks the same as it did before! Well it does on the surface but I’ve corrected a few underlying little things here and there in the filters that are attached to the side bar, added in several features on the other tabs and includes new functionality that wasn’t previously present.

Major Updates:

  • Patch Days in Past Limit – Due to the number of updates returned I have the option of last 120/90/60/30 days – available (Only return information on patches released in X days)
    • This can be increased by adding values to the parameter in the query designer
  • Collection Filter – On the Server/Workstation Patch Compliance tab there is a drop down to choose collections, that was problematic in environments with LARGE collections
    • This is a parameter that uses a wildcard to choose only show the collections in your drop down that are relevant in your environment example ‘MAINT%’ returns all my maintenance window collections to use as filters.
  • Server Name Parameter – Earlier this year PowerBI added an update that you can use Database Names and Server names as parameters now.
    • Now you don’t have to edit all the queries manually just tell it what server and database to connect to when you open the template
  • DatabaseName Parameter – See the above bullet point

Future Updates:

  • WSUS – Last Error Code
  • WSUS – Server Last Scanned against
  • Some Type of Health dial for all SUPS/SUSDB’s in an environment based on number of bad updates?

Here you can see the new ‘Collection Name’ and ‘Software Update group’ selection. – I also fixed a few typo’s where I wrote ‘server’ instead of workstation.

 

 

Finally an example of what you will see when you go to first open the template and run it where you will need to enter parameters:

No more need to edit all of the queries using advanced editor.

As always happy patching and let me know what you think in the comments down below!

(1671)

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan currently works in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

comments
  • Dwight B.
    Posted at 21:00 September 19, 2018
    Dwight B.
    Reply
    Author

    Receiving the following errors

    Column ‘Name0’ in Table ‘ClientInfo’ contains a duplicate value ‘USHOLTCO019’ and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

    Load was cancelled by an error in loading a previous table.

    OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

    and so on

    • Jordan Benzing
      Posted at 22:42 September 19, 2018
      Jordan Benzing
      Reply
      Author

      Usually this happens when you have multiple entries in the database with the same name.

      I would start by double checking your ConfigMgr environment and see if you have a bunch of duplicate entries for the same name.

  • Dwight Brookland
    Posted at 21:36 September 19, 2018
    Dwight Brookland
    Reply
    Author

    I am getting errors stating duplicate items and it points to a machine name as well as it stops processing the data.

  • Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.