Software Update Reporting in MEMCM – Part 1

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.

Compliance States

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.

More info on Microsoft Docs

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.

Database Views

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:

• v_Update_ComplianceStatusAll
• v_UpdateInfo
• v_CICategoryInfo_All
• v_UpdateScanStatus
• v_R_System
• v_FullCollectionMembership

More info on Microsoft Docs

v_Update_ComplianceStatusAll

This view tells us which of the four compliance states an update is in and for what device.

Important Columns

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

v_UpdateInfo

This view contains the metadata info about a specific update or Software Update Group (SUG)

Important Columns

• 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.
• CIType_ID
  • 1 = Standalone updates (Drivers and Third-party updates)
  • 8 = Updates
  • 9 = SUG

• IsSuperseded
  • 0 = No
  • 1 = Yes

• IsExpired
  • 0 = No
  • 1 = Yes

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

v_CICategoryInfo_All

This view helps in categorizing the updates based on one of the following categories:

1. Critical Updates
2. Definition Updates
3. Feature Packs
4. Security Updates
5. Service Packs
6. Tools
7. Update Rollups
8. Updates
9. Upgrades

More info on Microsoft Docs

Important Columns

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

v_UpdateScanStatus

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.

Important Columns

• ResourceID – Used to join with v_R_System
• LastScanTime – The date and time at which the last update scan happened
• LastScanState
  • 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

• LastErrorCode
  • 0 = Success
  • Any other value = Failed

SELECT
	*
FROM
	v_StateNames
WHERE
	TopicType=501

v_R_System

This view will be used to get all device details like Device name, OS, etc.

Important Columns

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

v_FullCollectionMembership

This view will be used for targeting our report to a certain collection.

Important Columns

• CollectionID – The collection we want to target our report at.

Sample Queries

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.