Sunday, 7 July 2013

SCCM - SSRS report for Summary Update List Non-Compliance

Within the organization I currently contract too SCCM is utilized for the deployment of monthly Microsoft patches and to report on compliance through monthly and quarterly 'Update Lists'. To assist regional server and desktop teams track compliance of assets they manage against these update lists I have created various SSRS Reports, this is the first one I will be blogging about. 

My goal was to enable support teams to easily specify the CollectionID which groups the assets they are responsible for and select one or more update lists to easily identify which machines have not reached a compliant state. I  have tried to include other key pieces of information which are directly related to this kind of a analysis such as the windows update agent version, heartbeat, scan date and a link to another report for all available maintenance windows for the machine.

In addition to grouping all key information for reporting compliance on a per machine basis I have tried to immediately assist teams with their analysis by automatically highlighting when a heartbeat date or scan date is more than three days old. I also highlight when the windows update agent version is below a minimum known good version for SCCM 2007. These indicators should be investigated as a matter of priority, i.e. if the heartbeat is aged then local SCCM agent service is either stopped\offline or the agent is generally unhealthy. 

It is important that the role of the scan date\data is understood clearly by teams performing troubleshooting. I have seen it many times where server teams see a non-compliant server so believe that by manually installing an update this will immediately result in a compliant state. Support teams must understand that the scan process is not only utilized to determine update applicability locally on the machine but also all parent SCCM site servers in the hierarchy will independently calculate the compliance state for the machine based on the submitted scan state data.

Example Report Results. 

Report Features

  • Simple drop down selection criteria for SCCM deployments and deployment collections.
  • More complete overview of key information for each stage of the deployment of updates.
  • Near real time, changes are dependent on inter-site replication and the scan state submitted by client agents.
  • Ideal for drill through links to specific custom and default SCCM reports to display more detailed information on devices in a specific state.
All that should be required to get this report working in other environments that already have SSRS available is to upload the RDL and use Report Builder to modify the server details to point to their SCCM SQL database server name and database name. 

RDL Downloads

Another report which displays the same data but in a Excel friendly table format.

Please feel free to post a comment if you are unable to get the report working successfully.