다음을 통해 공유


System Center 2012 R2 Configuration Manager - Software Update Compliance - Compare Compliance

I’ll start this blog post with a disclaimer since it is not within the bounds of native abilities within System Center 2012 R2 Configuration Manager.

The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft.

This report does not have any support and should be used as an example on enhancing your own reporting posture. I have not validated the results outside of my own lab so please verify the results in your environment.

To follow on with the Software Update Compliance dashboard for System Center 2012 Configuration Manager, this report compares software update compliance by displaying side by side results between two points in time. The results are also presented within a 12 month period which allows for light trend analysis. Some of the steps needed in configure this report is described in the previous blog post.

Similar to the Software Update Compliance report there is no method to exclude updates and it looks for “All” applicable updates for managed systems.

 

Software Update Compliance– Compare Compliance

The Software Update Compliance – Compare Compliance report is broken into several parts; System Compliance, Software Update Compliance, and Monthly Compliance which is further separated between Servers and Workstations.

 

 Parameters

The System Compliance and Software Update Compliance charts are filtered on all parameters. The Start Date and End Date parameters will filter the overall charts to show results of the updates that were released\revised between those times.

When running the report it will default to comparing software update data between the top 2 most recent dates. This can be changed by modifying the @DateCollected1 and @DateCollected2 parameters.

 

Overall System Compliance Charts

Each of the Overall charts are similar in that they are categorized by the operating system and show the compliance between two separate dates. The Overall System Compliance charts displays the compliance of systems that are not missing any updates.

 

Overall Software Update Compliance

The Overall Software Update Compliance charts displays the percentage of applicable updates installed.

 

 

Monthly Charts

The Monthly charts are similar to the Overall charts except they only show a 12 month period starting from the @EndDate – 11 months to the @EndDate. I didn’t include a monthly system compliance to limit the number of charts in the report.

 

Software Update Compliance Database

The intention of this report is to keep things very simple in SQL. Because of this the SQL queries are not as optimal as they could be however, should still should perform adequately.

The information needed to compare results are basic device information, the applicable updates for each system, and the status of each update. This information is then captured in a separate database which allow comparisons between two given dates.

 

Create the SQL Database

Although the database can be hosted on any SQL Server (2008 - 2012) with SSRS, for simplicity sake the database has been created on the SQL Server hosting the Configuration Manager database and SSRS.

  1. Create a database called Software Update Compliance

  2. Create 3 tables

    a. Devices

    Column

    Data Type

    Allow Null

    ResourceID

    int

    No

    Name0

    nvarchar(50)

    No

    Operating System

    nvarchar(100)

    No

    LastScanTime

    smalldatetime

    No

    DateCollected

    smalldatetime

    No

     b. Software Updates

    Column

    Data Type

    Allow Null

    CI_ID

    int

    No

    Title

    nvarchar(513)

    No

    ArticleID

    nvarchar(64)

    No

    BulletinID

    nvarchar(64)

     Yes

    DateRevised

    smalldatetime

    No

    Deployed

    bit

    No

    Category

    nvarchar(50)

    No

    DateCollected

    smalldatetime

    No

    c. Status

    Column

    Data Type

    Allow Null

    ResourceID

    int

    No

    CI_ID

    int

    No

    Status

    nchar(10)

    No

    DateCollected

    smalldatetime

    No

Replicate the SQL data

There are many ways to get data from one database to another. SSIS would be the preferred method however, SSIS relies on the SQL Server Agent which may not be enabled for some. The below method should work for the majority of people.

 

Create a Stored Procedure

The stored procedure collects the data needed and stores it in the Software Update Compliance database.

The diagram below shows what views the data is pulled from and where it is stored in the new database.

  1. Using SQL Management Studio open sp_CollectSoftwareUpdateStatus.sql which is located in the attached zip file.  Replace each instance of [CM_<SITECODE>] with your CM Sitecode and exec the script.  This will create a stored procedure in the Software Update Compliance database.

    (example: Replace [CM_<SITECODE>] with [CM_HQ1]

     

Manage the SQL Database

Managing the SQL database won’t be covered in this blog as there are many scenarios to cover. Consult with your SQL DBA to find the right method to back up the SQL Database.

If you don’t have a SQL DBA and little SQL experience then understand that other than having a good backup of the database, overtime the transaction logs may grow and need to be managed separately. See https://technet.microsoft.com/en-us/library/ms191429(v=sql.110).aspx for more information.

 

Trigger the SQL Stored Procedure

There are many ways to get the stored procedure to run. A SQL Server Job would be a good method but this too is dependent on the SQL Server Agent. A scheduled task or System Center Orchestrator could be used but I'll describe a method to leverage native Configuration Manager capabilities after each successful software update sync using the Status Filter Rules.

Be cautious with this method. If you have an aggressive sync schedule and a large amount of devices this will quickly grow the database. 

  1. Copy Get-SoftwareUpdateStatus.ps1 to C:\Tools

    1. The location doesn’t need to be C:\Tools and could be anywhere on the server
  2. In the CM Console click Administration from the Wonder Bar

  3. Expand Site Configuration --> Click Sites

  4. Click Status Filter Rules from the Ribbon

  5. Click the Create button

  6. Give the filter rule a name (e.g. Get Software Update Compliance Information)

  7. Check Component

  8. Select SMS_WSUS_SYNC_MANAGER

  9. Check Message ID

  10. Enter 6702

  11. Click Next

  12. (Optional) Check Report to the event log

  13. Check Run a program

  14. Enter C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -file "C:\Tools\Get-SoftwareUpdateStatus.ps1" –noprofile

    1. Change the location of the file if you stored it somewhere else on the server

    2. Modify the file and change the DaysScanned value as needed

  15. Click Next

  16. Click Close

 

Upload the RDL File

  1. Upload the RDL file to the SSRS server.

    1. The rdl file could be uploaded anywhere however, it’s recommended to place it in the Sofware Updates Compliance folder describe in my previous blog post
  2. Modify the report to include your datasource.  You will need to at least modify the connection string with your SQL Server that is hosting the Software Updates Compliance database.

Software Updates Compliance - Compare Compliance.zip

Comments

  • Anonymous
    February 17, 2015
    wow!!!!!!!!!!
  • Anonymous
    March 09, 2015
    I want to express one of my biggest thank you. This is excellent stuff very usefull, thank you!
  • Anonymous
    May 14, 2015
    Wonderfull blog posts, thanks for sharing your expertise on such a difficult (for me) subject.
    having those great examples with explanations and sample files make things a lot easier to aprehend!

    a huge thanks from the South of France :)
    Pierre (Red Kaffe)
  • Anonymous
    June 16, 2015
    I have a SQL server and a SCCM Management Point Server (2 Seperate Servers) how should I schedule this on the SQL server or on the Management Point?
  • Anonymous
    June 16, 2015
    Never mind I got this working... Thanks! But also up above for Devices you need to create LastHWI under device and allow NULL