MSEndpointMgr

SCConfigMgr Software Update Compliance Dashboard – Version 2.0 released

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!

(14250)

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.

14 comments

  • Hello,

    I downloaded the file, however, how do O install the dashboard into SCCM?

    • Unfortunately there is no way to install the dashboard into SCCM. You have to use it with the PowerBI desktop.

      The dashboards you see in SCCM are created by HTML pages that collect data from the ConfigMgr database.

  • 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.

    • 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!

  • 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.

    • 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

  • 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

  • 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

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

  • 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

  • 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

    • 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.

Sponsors