This is going to be a three-part series where I will try to explain some of the components behind Software Update reporting in MEMCM. Part 1 is focused on the building blocks of a report, the Views in the MEMCM database. If you’re looking for a query that you can directly use in your environment, I’m afraid this is not the right place (although I have added a couple of sample mini queries towards the end). If you want to build one from scratch as per your own requirements or understand how to modify another report, this post may be of some value.
Also, if you’re completely new to SQL and want someplace to start, I cannot recommend this post by SystemCenterDudes enough. You won’t find a better guide elsewhere. I wish I had this info when I was getting started.
Before going to the database views, let’s discuss the different compliance states of the updates:
- Required – The update needs to complete a successful installation (and/or post-installation reboot ) and report the success status back to MEMCM.
- Not Required – The update is not applicable on that device.
- Installed – The update was required on that device and has completed a successful installation and reported the status back to MEMCM.
- Unknown – Whatever the status of the update is, it is not able to report back to the MEMCM server. The update could have been installed here, but if it’s not able to report the same to the MEMCM servers, it would fall under Unknown status.
As you can see in the below screenshot, we have various compliance states for an update and a number written beneath. That number is the count of the servers for that state for that particular update. So in the case of KB890830, it is Required on 1 server, Installed on none, and Not Required on 1 server. And for one of the servers, its status is Unknown. Once the update gets installed on the server where it’s Required, the view will change in a way that Required becomes 0 and Installed becomes 1.
If you are wondering about the Percent Compliant being 33 even though the update is not installed anywhere, that’s because the Not Required server will be counted as Compliant just because that update is “not missing” from that server. So 1 out of 3 servers is compliant, therefore the 33 percent.
Alright, so let’s start with the most common views which are required for Software Update reporting. The views you end up using depend on what metric you’re trying to report:
This view tells us which of the four compliance states an update is in and for what device.
- 0 = Unknown
- 1 = Not Required
- 2 = Required
- 3 = Installed
- ResourceID – Used to join with v_R_System
- CI_ID – Used to join with v_UpdateInfo
If you only care about the Required and Installed status, you can use the v_Update_ComplianceStatus view.
This view contains the metadata info about a specific update or Software Update Group (SUG)
- Title – Update name
- Description – Description of the update
- ArticleID – KBNumber for the update
- CI_ID – Used to join with v_Update_ComplianceStatusAll
- CI_UniqueID – Not very useful for compliance reporting, but comes in handy while troubleshooting.
- 1 = Standalone updates (Drivers and Third-party updates)
- 8 = Updates
- 9 = SUG
- 0 = No
- 1 = Yes
- 0 = No
- 1 = Yes
- 2 = Low
- 6 = Moderate
- 8 = Important
- 10 = Critical
- DatePosted – The release date for the update
- MaxExecutionTime – Maximum time allowed for the update to install. This value is in seconds.
This view helps in categorizing the updates based on one of the following categories:
- Critical Updates
- Definition Updates
- Feature Packs
- Security Updates
- Service Packs
- Update Rollups
- CategoryInstanceName – The type of update, as mentioned above.
- CategoryTypeName – The value for that should be – ‘UpdateClassification’
SELECT DISTINCT CategoryInstanceName FROM v_CICategoryInfo_All WHERE CategoryTypeName='UpdateClassification'
The result of this query depends on what all categories you have selected in the Classifications tab of the Software Update Point.
This view gives details about the Software Update scan status per device. It’s this scan that decides which of the four compliance states does an update belongs to.
- ResourceID – Used to join with v_R_System
- LastScanTime – The date and time at which the last update scan happened
- 0 = Scan state unknown
- 1 = Scan is waiting for catalog location
- 2 = Scan is running
- 3 = Scan completed
- 4 = Scan is pending retry
- 5 = Scan failed
- 6 = Scan completed with errors
- 0 = Success
- Any other value = Failed
SELECT * FROM v_StateNames WHERE TopicType=501
This view will be used to get all device details like Device name, OS, etc.
- Netbios_Name0 – Device name
- Operating_System_Name_and0 – OS details
- ResourceID – Used to join with other views like v_Update_ComplianceStatusAll or v_UpdateScanStatus, etc.
This view will be used for targeting our report to a certain collection.
- CollectionID – The collection we want to target our report at.
Leaving you with a couple of queries just to give you an idea about what kind of information we can get from the stuff we learned today. After reading this post, I hope that Software Update reporting does not feel as intimidating as it used to.
Count of Required Updates per device
-- Count of required Updates SELECT DISTINCT vrs.Netbios_Name0 'Device', COUNT(ucs.CI_ID) 'Total Updates Required' FROM v_R_System vrs JOIN v_Update_ComplianceStatusAll ucs on ucs.ResourceID=vrs.ResourceID JOIN v_CICategoryInfo_All cica on cica.CI_ID=ucs.CI_ID WHERE ucs.Status=2 -- Required AND cica.CategoryTypeName = 'UpdateClassification' --AND cica.CategoryInstanceName='Security Updates' GROUP BY vrs.Netbios_Name0
For my test environment, I have just three devices.
Details of Required updates for a specific device
--Details of required updates SELECT vrs.Netbios_Name0 'Server', ui.Title 'Update Title', ui.ArticleID 'KB Number', convert(varchar, ui.DatePosted, 6) 'Date Published', DATEDIFF(DAY,ui.DatePosted,GETDATE()) 'Days Since Published', cica.CategoryInstanceName 'Update Category' FROM v_R_System vrs JOIN v_Update_ComplianceStatusAll ucs on ucs.ResourceID=vrs.ResourceID JOIN v_UpdateInfo ui on ui.CI_ID=ucs.CI_ID JOIN v_CICategoryInfo_All cica on cica.CI_UniqueID=ui.CI_UniqueID WHERE vrs.Netbios_Name0='WIN7PRO' --Enter Device Name AND ucs.Status=2 --Required AND ui.IsSuperseded=0 --Not Superceded AND cica.CategoryTypeName='UpdateClassification' --AND cica.CategoryInstanceName='Security Updates' ORDER BY [Days Since Published]
As I narrowed down my search to just ‘WIN7PRO‘, I am getting the 21 required updates.