Creating a collection in SCCM for “all SCOM 2007 R2 agents without Cumulative Update 4 installed”

Recently I’ve been working on deploying a SCOM environment to monitor SCCM infrastructure.

Initially we used SCCM to deploy SCOM agents out to all SCCM servers as it is our preferred method to deploy any apps. By doing so, SCOM agents are considered “Manually Installed” in SCOM, therefore I cannot simply approve updates in SCOM console and get it pushed out in SCOM.

I needed to make a collection for all computers that have SCOM 2007 R2 agents installed but without CU4 so it can be used to deploy CU4 to SCOM agents.

Here’s the query:

Description: All computers that has “System Center Operations Manager 2007 R2 Agent” installed AND MonitoringHost.exe version is not 6.1.7221.61

Requirement:

1. Hardware inventory includes Win32Reg_AddRemovePrograms and Win32_AddRemovePrograms64 classes (This is enabled by default)

2. Software inventory includes monitoringhost.exe (by default, *.exe is included)

Query:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "System Center Operations Manager 2007 R2 Agent" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "System Center Operations Manager 2007 R2 Agent") and (SMS_G_System_SoftwareFile.FileName = "MonitoringHost.exe" and SMS_G_System_SoftwareFile.FileVersion != "6.1.7221.61")

</span></em>

Below is the result in my test environment:

Collection Membership in SCCM:

State View of Agent List with Patch List from SCOM:

 

Note: By default, software inventory occurs every 7 days, the SCCM collection membership may not be 100% accurate because it relies on agent hardware and software inventory to feed the data.