I don't think I worded that correctly, but I am trying to create a collection of machines that don't have a firewall policy. My original collection is this: 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.SystemGroupName = "TIGA\\WindowsServerFirewallPolicy" The collection populates fine with that. I made another subcollection and tried to make a subselect query and used the "not in" operation to get the rest of the servers in our domain that are not part of that group. I then pointed to the above query. I get a syntax error when trying to exit out. 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.SystemGroupName = "TIGA\\WindowsServerFirewallPolicy" What am I doing wrong?

Hi, try this: Member of Query: select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "TIGA\\WindowsServerFirewallPolicy" Not member of using the subeselected statement in the Collection 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 SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "TIGA\\WindowsServerFirewallPolicy") that should do it. regards, Jrgen -- My System Center blog ccmexec.com -- Twitter @ccmexec

Hi Jorgen, Thanks for the reply. I "think" its working, but not 100% because it's pulling all machines from the domain now. I only wanted servers since this policy is only applicable to them. I tried adding another memebership rule to that secondary collection to filter by server since we have a mix of 2003, 2008 and 2008 R2: 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = "%Server%" Doesn't seem to work. Even tried this since it's working on other OS based collections: 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 OperatingSystemNameandVersion like '%Server%'

Hi, Try this: 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "TIGA\\WindowsServerFirewallPolicy") and SMS_G_System_OPERATING_SYSTEM.Caption like "%Server%" It should pull all server OS systems that is not a member of the group. regards, Jrgen-- My System Center blog ccmexec.com -- Twitter @ccmexec

Hi Jorgen, Progress! It def. cut things down but I think part of my problem was that this second collection (no firewallpolicy) was a subcollection of the firewallpolicy collection. I removed it, recreated it at the same level as firewall policy and used your code. WAY better results. About 35/40 servers higher than I'm used to monitoring but I think it's because of special circumstances. You might have just helped me inadvertently identify "rogue" servers! One other question now. We have a security baseline group, one for windows2k3 (called W2K3SecurityBaseline) and one for windows2k8 (called W2K8SecurityBaseline). I created the first collection, security baseline with this: 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.SystemGroupName = "TIGA\\W2K3SecurityBaseline" or SMS_R_System.SystemGroupName = "TIGA\\W2K8SecurityBaseline" I get about a quarter of our servers. About 100ish. Now the part to find those without that group membership: 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "TIGA\\W2K3SecurityBaseline") and (SMS_R_System.SystemGroupName = "TIGA\\W2K8SecurityBaseline") and SMS_G_System_OPERATING_SYSTEM.Caption like "%Server%" It def pulls machines, but only about 85. Numbers aren't jiving. I'm missing about 150-200 servers that should fall into one or the other collections. I think we have a lot more servers that don't belong to that security baseline collection, so the number should be higher. But it's not.

Hi, I think your second query for servers without group membership should like this: 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "TIGA\\W2K3SecurityBaseline" or SMS_R_System.SystemGroupName = "TIGA\\W2K8SecurityBaseline") and SMS_G_System_OPERATING_SYSTEM.Caption like "%Server%" Thanks

Hi Quan! That worked! You and Jorgen saved me a ton of work. Thanks to you both.