Dam Good Admin

Or at least not entirely useless

Yet Another Software Update Dashboard

 

Once I started getting a handle on deploying updates with Configuration Manager my mind quickly moved to proving that it actually worked.  How could I prove to myself and management that the solution I was putting together actually improved our patch compliance.  I had started similar work in our previous management tool and had gotten far enough to know that we weren’t in great shape.

Configuration Manager has a bunch of built-in reporting so I started sifting through it trying to understand what was there.  I was left … disappointed.  The built-in compliance reports are all at the individual Software Update Group level.  While useful that’s not what I was looking for.  So my first inclination was to look to see what custom reports or dashboards existed in the Configuration Manager community.  There’s quite a few of them and range from those that are freely available:

to those that are not free but are from from ‘names you can trust’:

Despite all of that being available I still wanted to build my own.  In part because I suffer from the ‘Not Built Here’ syndrome as much as the next person but because I always … always … want to know how things work.  While it’s hard to justify the labor cost it takes to build a custom dashboard there’s no other way to truly understand how reporting works.  There will be a follow-up post to this one talking about the lessons I learned along the way but this post is intended to share the fruits of that labor.

What Does This Dashboard Do?

The focus of this dashboard is to report compliance across all devices in the organization.  For my purposes I’m defining compliance as having all deployed and applicable updates installed within 14 days of their deadline.   In order to make that feasible these reports work on the update deployment (or assignment) level.  A device is considered unknown if any deployment status is unknown.  If there are no unknown but one or more of the assignments is not compliant then the device is not compliant.  If and only if all deployments are compliant is the device considered compliant.

Overall Compliance

This is intended to be the top-level report that acts as the dashboard.  This is the report that tells you and your management how good or bad things are.  There’s multiple datasets and charts that allow you to breakdown the top level Workstation vs Server split into sub groups.  On the workstation side we do it by departments (Campus vs Retail Locations) and for servers I split out servers that have a ‘never’ maintenance window and are patched manually.  All of the charts and legends are drill-downs to the Overall Compliance – Devices report.  Each dataset and chart is limited using a parameter listing the name of a collection.  Therefore you will want to modify the default values for the CollName parameters as well as the Days parameter if you define compliance differently.

Overall Compliance – Devices

This report is the first drill-down report from the Overall Compliance report. For devices in the selected state and collection it will list the status of each deployment as well as evaluation and error details.  This report isn’t meant to be ran directly but if you do for some reason you will want to change the default values for the CollID and Days parameters.

Compliance for A Specific Computer

This is the bottom level detail report focused on a single client.  There’s a chart showing the device’s overall compliance as well as some relevant details about that client.  When entered from the Overall Compliance  – Devices report the update list will only include those with the drilled-into status.  This report is also useful as a stand-alone report so you may want to modify the UpdateState and Days parameters.

Compliance Overview –  Latest Cumulative Update

I released and blogged about this report last week: Compliance Report for Latest Cumulative Updates.  I’m including it here for completeness and because I have updated it to be more interactive.

Software Updates – Computers with Failures

This report will list all devices in the selected collection that have reported errors.  By default it is sorted so that the clients with the highest error counts appear first.  You can expand each device to list the updates that failed and details about that failure.  This is a good report to give your lower level admins or technicians.  Document common errors that you can’t otherwise automate (ex. disk space) and have them go forth and remediate.

Software Updates – Updates with Failures

This report is the same data as the previous report but grouped by update rather than device.  This allows you quickly see which updates are failing most often in your environment.

Installing the Reports

You can search online for far better instructions than I’m prepared to give here for how to import the reports.  If you’re too lazy to search but not too lazy to try and work off poor instructions, here’s what you need to do.

  • Navigate to the SSRS reports page on your Reporting Services Point server (ex. http://RSPSERVER.domain.com/reports)
  • Create a folder and upload the report files one at a time.
  • Edit the report in Report Builder.
  • Change the Data Source to your SSRS server’s data source.
  • Edit the header images to reference your top-level report folder by replacing ### with your site code.
  • Edit the parameters discussed above as desired.
  • Save the report.
  • Thinks about the life choices you’ve made that have lead to this point.

9 Comments

  1. I am trying to run individual report “Compliance for A Specific Computer”, but getting an error: An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘ClientDetails’. (rsErrorExecutingCommand)
    Invalid column name ‘TopConsol###er0’.
    Not sure what i need to change?
    Thank you.

    • Bryan Dam

      May 19, 2018 at 5:06 pm

      Ah yes, search and replace fail on my part in the version I silently released right before MMS. Just uploaded what should be a fixed version so try that and let me know. You should only need to overwrite the ‘Compliance for Specific Computer’ report.

  2. Hi Bryan,

    Thanks for the excellent post. I was hoping you might be able to clarify a few things:

    You mentioned in your post that “A device is considered unknown if any deployment status is unknown”. In my case I have a Device Collection containing 39 servers and the top level report is showing Unknown at 100%. There is only 1 Software Update Group deployed to this Device Collection and its compliance is 97% if I use the “Compliance 1 – Overall Compliance” built-in report.

    Do you know why the servers might be showing a status of 39 Unknown (100%) using this custom dashboard? All of the other OS types seem to be reporting just fine.

    • Bryan Dam

      May 19, 2018 at 4:48 pm

      Justin, drill down to the ‘Overview By Devices’ report for the unknown servers in that collection. There it will list the devices and the status of each update deployment. In theory, one or more deployments should be reported as ‘unknown’.

  3. Hi,

    This is brilliant work. I have just spent the past week or so updating our servers to the latest patch levels ahead of an upcoming audit and this report is exactly what I am looking for. One really small thing I have noticed though, and can’t work out how to fix. If you click on the pie chart at the top of the left hand side, you are taken to the sub report as expected.. However if you click on the value with the legend you get The item’/configMgr_##### cannot be found. All the other legends work fine just the All workstations one.

    Keep up the great work.

    Cheers
    Paul.

    • Bryan Dam

      May 19, 2018 at 4:45 pm

      Sorry Paul, I _finally_ fixed the comment notification. I uploaded a new version this week. I’m not totally sure that I fixed the issue you mention so let me know if the bug is still there. I have a couple other ideas that I got from Garth Jones while prepping for our MMS session so I plan on updating them again in the next couple of weeks.

  4. Thanks, I appreciate the good work 🙂

  5. I’m getting an error: Cannot find either column “dbo” or the user-defined function or aggregate “dbo.fn_rbac_GetAdminIDsfromUserSIDs”, or the name is ambiguous.

    • Sorry for the late reply Steve … I seemingly am not getting notification regarding comments. What version of ConfigMgr are you using? That function is a foundational one for RBAC so if it’s not there … I’m not sure what to tell you.

Leave a Reply

© 2018 Dam Good Admin

Theme by Anders NorenUp ↑