The Challenge - How do I keep my SQL Querries simple and still include additional logic for whether a system is in the BASIC population that gets normal change management or SPECIAL population that can't receive updates as readily such as Lab or Manufacturing systems.
So if I want to create a Dynamic Collection in my environment to deploy a patch to Adobe Reader 9 to bring it to Adobe 9.1 I would start with the base SQL something like -
SELECT v.guid
FROM vComputer v
JOIN Inv_AeX_OS_Add_Remove_Programs arp ON arp.[_ResourceGuid] = v.Guid
WHERE arp.[name] = 'Adobe Reader 9'
Very Easy Right? But let's say you want to send this job out to the Basic population but avoid the Special population. You could add the SQL to your dynamic collection to call out things like ismanaged = 1 or maybe your BASIC population only uses the subnet 10.10.x.x. You can add this right into your Dynamic Collections SQL.
The Tricky part is, what do you do WHEN my BASIC population subnets change or the rules for what constitutes a Manufacturing PC changes? Now you have to touch every Dynamic Collection you've created to change the SQL to reflect the BASIC or SPECIAL requirements.
Here is another way you might attack the problem.
Create two collections. The first will be your BASIC collection where you will write your SQL for ismanged -1 and 10.10.x.x etc. Then create a SPECIAL collection which is completely opposite.
Then you can create your Adobe Reader 9 Collection for your upgrade using the simple SQL listed above and exclude the Special Collection. Please note this works well for exclusions but not inclusions. If you include a collection in a dynamic collection, the SQL will run and then the 2nd collection will be added regardless of the SQL. You can then use this as a basic template for future sq upgrades where you might change 'Adobe Reader 9' to something like 'Office 2003'.
In one of my current environments I've taken this a step further. In this very large environment we have 3 client facing Notification Servers that forward all their data to a Master NS that has billing information on it. So at the end of the month we can run a report based on a Dynamic Collection that returns only Billable systems. This data is only available from the Master NS. So what I've done is point the BASIC and SPECIAL Dynamic Collections SQL back to the Dynamic Collection on the Master NS using the SQL below.
select distinct masterns_v.guid
FROM MasterNS.altiris.dbo.vcomputer MasterNS_v
join dbo.vComputer vc on MasterNS_v.guid = v.guid
inner join MasterNS.altiris.dbo.collectionMembership MasterNS_Ent on MasterNS_Ent.ResourceGuid = MasterNS_v.Guid
WHERE MasterNS_Ent.CollectionGuid = '{db0366ca-5dd5-4fc9-b2ca-08f4694aea1a}'
--- Replace the GUID above with your Master Basic Dynamic Collection GUID.
So what this gains me is the ability to change my BASIC or SPECIAL Dynamic Collection SQL in ONE CENTRALIZED PLACE and still have it trickle down to each NS without updating every Dynamic Collection.