|
|
 
0 |
This question came up again with a coworker today, so thought I would document the process for our reference. Here is how to setup a collection in ConfigMgr and input a query to make a dynamic collection in ConfigMgr. This example is for systems with Office 2007 Pro installed...and another for systems without Office 2007 installed. This value is that which is displayed in Add / Remove Programs, so you can substitute any application name to create whatever queries you like. We had a pretty cool discussion in the SCC ConfigMgr forum where we shared some sample collection queries (click here to see that thread).
How to Create the Collection
1. Right click and select New Collection
2. Give the collection a Name and Description and click Next
3. On the Membership Rules screen, click the database icon. This brings up the Query Rule Properties window…
4. Then, in the Query Rule Properties window provide a name for the query and set the Resource Class: to “System Resource”. Click Edit Query Statement.
5. On the next screen, click Show Query Language. Paste your collection query into the window and click OK.
Here's the query. I got the display name of Office from Add/Remove Programs. You'll need to update the value with what you see in your environment.
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 where SMS_R_System.ResourceId in
(select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft Office Professional 2007')
6. Click Next, Next again and then Finish.
When you open the query, you should have a list of every system with Office 2007 installed.
Finding Systems WITHOUT an Application Installed
It's just as easy to do the opposite here and find systems without the application installed. We just have to add a NOT into the 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 where SMS_R_System.ResourceId NOT in
(select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft Office Professional 2007')
Conclusion
This is mostly for my reference, but if it helps someone else out there...cool