Hey this is a cool one, even if you don't have a big list send them into us here at SCC and we will complie them in to a useful doc.

Or you could send them to simon at skinner.fr and Pete and I will put it together

This collection query one shows how to query for free disk space to determine readiness for MS Office and other software installations. Collection Query - Office 2003 Pro Installation

 

Good start Tommy, here's one before I get back to Christmas Dinner and family time. This collection query will show you all systems that have a particular service installed based on service NAME ..not the Display Name...(NAME is what you'd use in a 'net start' or 'net stop' command)

this one would show you all servers with the OpsMgr SDK service installed (all Management Server basically)

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 SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "OMSDK")

 And technically, if  you throw a not in there, you could see all systems without the service

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 SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "OMSDK")

 

 And suppose worth mentioning an example of a collection query that shows clients client that do not have an application installed. You basically just add the NOT into the query.

Here's a query to show systems without Office 2007 Professional installed. 

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')

Again the key is to make sure the value in DisplayName at the end of the query matches what you see in Add/Remove Programs

I'll post a couple of more complex examples soon.

 Here's a brief how-to to create a ConfigMgr collection based on the memberships of multiple existing collections. I have used this a few times and think it's pretty cool! 

http://sms-hints-tricks.blogspot.com/2008/11/collection-query-of-multiple.html

Lots of queries in the Code Repository. Plus, you can create your own code packs and share them with the community.



http://www.myitforum.com/articles/11/view.asp?id=8535

Here's another I've been playing with. ConfigMgr collection query with variables. Here's a collection query that 

SELECT * FROM SMS_R_System
WHERE SystemOUName like "%PLAY%"

This will match all systems that have an OU that has the word PLAY in it

http://social.technet.microsoft.com/Forums/en/configmgrsdk/thread/6554f9a4-6973-4463-8ee4-37836424c519