I am trying to determine of the users with Office 2007, which are missing SP2. All the queries I am tried do not give back accurate results. Here is an example of one that did not work: select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Office%2007%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != '2007 Microsoft Office Suite Service Pack 2 (SP2)' Thanks

Possibly due to how you have the office 2007 detected. Just as a side note, if you have office updates pushed from SCCM via SUP then don't bother creating a SP2 collection. You can deploy the patch to all machines and only machines that need SP2 will get and install it. Since you just want machines that don't have sp2 installed why not just do this. SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Suite Service Pack 2%") and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Office 2007%" This should then pull up all machines that don't have Sp2 install, then it will then only look at machines that have office 2007 installed. I have tested it but that should work fo ryou.http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

I hate to be a pain but that is an incomplete query and I can't seem to find the correct select statement to add to the beginning of the query. Thanks

Possibly due to how you have the office 2007 detected. Just as a side note, if you have office updates pushed from SCCM via SUP then don't bother creating a SP2 collection. You can deploy the patch to all machines and only machines that need SP2 will get and install it. Since you just want machines that don't have sp2 installed why not just do this. SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Suite Service Pack 2%") and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Office 2007%" This should then pull up all machines that don't have Sp2 install, then it will then only look at machines that have office 2007 installed. I have tested it but that should work fo ryou. http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com This works to bring back computers without Office 2007 SP2: 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Suite Service Pack 2%") But adding the bolded code throws an error [*The ConfigMgr Provider reported an error.*]: 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Suite Service Pack 2%") AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Professional Plus 2007%" Thanks

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office%Service Pack 2%" ) and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007" ok I tested this on one of my test machines. We have Office Enterprise 2007, hence why you see that here. Hope this gets you there.... http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com This worked perfectly. I had no idea how difficult this particular query was going to be. I appreciate all the effort!!!! 5 star! Thank

Without the perfect query you can't perform your duties and there are so many things and tricks you can do with collections. Glad it worked for you!http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com