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!

(3030)

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 most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. 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.

  • Jason
    Posted at 15:18 September 21, 2018
    Jason
    Reply
    Author

    Also getting duplicate errors when trying to run the reports.

  • Rich Townsend
    Posted at 15:50 September 24, 2018
    Rich Townsend
    Reply
    Author

    How can I make it see all my collections? No matter what collection filter I use and what wildcards, it always shows the same 6 (I have over 50).

    Thanks

  • Kevin
    Posted at 19:04 September 25, 2018
    Kevin
    Reply
    Author

    Same here. We have a non-persistent VDI pools that cause duplicates. Could it go by a unique value like ResourceID instead?

  • Adam Phillips
    Posted at 09:08 October 15, 2018
    Adam Phillips
    Reply
    Author

    Thanks for this Jordan, it is amazing a very useful!!!

    I have found a way around the duplicates issue in the ClientInfo table by running that SQL query in excel, removing duplicates and then using the Excel file as the data source for that table. Not ideal but it works.

    Because of this i need to do the same for the CollectionMembership table but cannot find where the SQL is for this query? There is no option in the Advanced Editor for the source of this? I know it will use the CollectionFilter parameter somewhere but im struggling to find it, am i missing something?

    Thanks again for this

  • Adam Phillips
    Posted at 09:09 October 15, 2018
    Adam Phillips
    Reply
    Author

    Thanks for this Jordan, it is amazing a very useful!!!

    I have found a way around the duplicates issue in the ClientInfo table by running that SQL query in excel, removing duplicates and then using the Excel file as the data source for that table. Not ideal but it works.

    Because of this i need to do the same for the CollectionMembership table but cannot find where the SQL is for this query? There is no option in the Advanced Editor for the source of this? I know it will use the CollectionFilter parameter somewhere but im struggling to find it, am i missing something?

    Thanks again for this Jordan

  • Reza
    Posted at 02:35 November 30, 2018
    Reza
    Reply
    Author

    Hi Jordan,

    Thank you for the update.
    I’ve started experiencing an issue with the SCConfigMGR-Patch-Compliance templates (v1.0.0.7 and v2.0) where the reports have stopped picking up most of the missing updates.
    For instance, one of the servers had about 60 updates missing earlier this month though it’s only showing 8 updates missing when I re-ran the report. We have not installed any updates on this server this month and the Software Center on the server shows that it still requires 60 updates.
    When I run an earlier version of the report (e.g. SCCM-Month-Slider-Public-V1.02.pbit), the report picks up the correct amount of updates for each server.
    Do I need to change the queries to get the right numbers?
    Thankyou.

    • Jordan Benzing
      Posted at 20:23 December 5, 2018
      Jordan Benzing
      Reply
      Author

      This usually happens because of how the date range filters are set up on the report. If you look into the visualization filters you’ll notice that there are quite a few filters that get applied to things you can strip them down as needed to allow the data to come through in a more verbose fashion.

      cheers,

      -J

  • Reza
    Posted at 02:51 December 6, 2018
    Reza
    Reply
    Author

    Thank you for your response, Jordan.

    I’ve tried toggling the filters but it didn’t make any difference.

    I then edited the query on the ComplianceInfo table where I changed the line
    “V_UpdateInfo.DateCreated BETWEEN DATEADD(d,”&DaysInThePast&”,GetDate()) and GetDATE() ” (or the line “V_UpdateInfo.DateCreated BETWEEN DATEADD(d,-365,GetDate()) GetDATE()” in v1.0.0.7) to “V_UpdateInfo.DateCreated BETWEEN ‘2017-01-1 00:00:00.00’ and GetDATE()” which I got from the PatchInfo table query in your SCCM-Month-Slider-Public-V1.02.pbit template.

    This worked and the dashboard now picks up the right amount of missing patches.
    Thanks.

    • Jordan Benzing
      Posted at 04:04 December 6, 2018
      Jordan Benzing
      Reply
      Author

      Ah – You should have been able to get the same result I believe from updating the parameter set to use an older date range into the past. I’m glad you got it resolved!

      cheers!

  • Leave a Reply

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