Thursday, September 6, 2012

SCCM SMS Query

How to query for an application but exclude another defined query.

This would be useful if you want to only target a specific kind of computer. For example when upgrading from Office 2003 to Office 2007 and you want to keep Access 2003 present. You want to target only computer with office 2003 that have no sign of 2007.

Create a query that target all instance of 2007 (collectionid 'S010014D')

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2007"

Then create another query searches for all instances of 2003 excluding the previous query results.

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Edition 2003" and SMS_R_System.ResourceId not in (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE collectionid IN('S010014D'))

No comments:

Post a Comment