Re: How to combine existing collections to include or exclude...

Here is a working 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 Client = 1 and ClientType = 1 and ResourceId not in
(select ResourceID from SMS_CM_RES_COLL_FSS00365)
---------------------------------

Note: The very last object (sms_cm_res_coll_fss00365), just replace the
fss00365 with the collection ID you would like to exclude.

Note again: This will return clients only. You can modify anyway you like
to add additional properties.


"Kim Oppalfens <MVP>" wrote:

Well, every collection has a collection id. For each collection a wmi class
is called sms_cm_res_collid_thei-id-associated with the collection.

If you are familiar with subselect values in queries you could create
something like this.

Select name from sms_r_system where name not in ( Select name from
sms_cm_res_collid_......)

Now every time the collection with sms_cm_res_collid is updated with new
machines to exclude, the primary collection would be modified as well.

--
Kim Oppalfens
Telindus Belgium
MVP Windows Server System - SMS
"George Phillips" <GeorgePhillips@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:D37838CE-14F1-44B7-B0F7-02D86221E00A@xxxxxxxxxxxxxxxx
Thanks! Could you explain how to do that?

"Kim Oppalfens <MVP>" wrote:

Only problem is that your exclusion list is static.
By excluding based on collection id you even could do this in a dynamic
fashion.

--
Kim Oppalfens
Telindus Belgium
MVP Windows Server System - SMS
"George Phillips" <GeorgePhillips@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:33091D84-6F38-45E6-86F3-D7CCF78E3F1F@xxxxxxxxxxxxxxxx
Yes, that worked! Thanks, again, for your assistance.

"Matthew Hudson" wrote:

How does this work........

To do it with only 2 collections do this

Collection A: Servers in remote location
Collection B: Servers you want minus the ones you don't want


New collection B
New Rule:
Limted to Collection A

[edit query statement/Criteria]
Cireterion type: List of Values
Where: Computer System (attribute = name)
Operator (is not in)
Value to add: Add computers you don't want to show up in Collection B
------------
When you refresh you will have a new collection with A, computers
minus
the
ones on your list

------------------------------------------------------------------------------
"George Phillips" wrote:

That would be great! I appreciate your willingness to help.

"Matthew Hudson" wrote:

Tell ya what...let me do some work over here by creating some
stuff
and I
will see if I can't just send you the collection queries and the
query
itself. based on what you want.
It will be either later today or tomorrow, if that is ok.
Assuming
no one
else answers this better for you.



"George Phillips" wrote:

Wow, you kind of lost me. They didn't make SMS for dumb people
like
me, did
they? :) Is there a resource or white paper you can point me to
that might
demonstrate this step by step?

My collection A already has the 'Limit to Collection' selected
because this
is selecting the computers in my remote offices via AD but
limits
it only to
the Windows 2003 Server collection so that collection A contains
only Windows
2003 Servers in my remote offices. I want to be able to use the
Direct
Membership to populate collection B so that I can exclude
servers
at will by
advertising to Collection C which is equal to A minus B.

Thanks,
George

"Matthew Hudson" wrote:

Sorry about that part, you will need to create a query based
on a
set of data
(asking for only computer name)
then edit the collection and do a query "Sub selected values"
you can then
select the query and say "computer name" not it (your created
query) limited
to your other collection

This will grab all the computers that are in the limited
collected minus the
ones that are already in the query.

I am not sure this made much sense...

Basically you have Collection A, B and you want a new
collection
that is A-B
= C
C collection is limited to collection A.
Query X is all computers in B
C Collection query is now "Sub selected values" of computer
names
Where A is
not in Query X

How does that sound?

"George Phillips" wrote:

Thanks, Matthew, for the tip! That will help me add two
collections together.
What do you recommend for excluding a collection? Using my
earlier example,
suppose I want to create a collection that consists of
Collection A minus
Collection B?

Thanks,
George

"Matthew Hudson" wrote:

There are several ways you can do this,
Choice A:

You can create a new collection D and then right click and
create a "link to
collection" This will create a duplicate of a selected
collection under D,
you can do this for all of them. Then create an
advertisment
that is posted
to D and include subcollections. This will do all a,c.

Choice B:

Create a new collection D. Copy each of the criterias in
to
that collection
(without limitations). Then you will have 2 queries (all
limited as they
were) but will combine in one collection.

"George Phillips" wrote:

Can someone help me understand what needs to be done in
SMS
2003 SP2 to
combine existing collections to effectively create a
different collection.
Suppose I have collection A, collection B and collection
C.
I want to be able
to advertise a package/program to a collection that
would
equal collection A
minus collection B. Collection A already has the 'Limit
to
collection' used
in order to create itself. I want to further limit A by
excluding B. Suppose
I want to send a package to A plus C. Any help is
appreciated.

Thanks,
George






.

Relevant Pages

  • RE: How to combine existing collections to include or exclude...
    ... "Matthew Hudson" wrote: ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ... will see if I can't just send you the collection queries and the query ...
    (microsoft.public.sms.admin)
  • Re: How to combine existing collections to include or exclude...
    ... "Matthew Hudson" wrote: ... Servers you want minus the ones you don't want ... [edit query statement/Criteria] ... Add computers you don't want to show up in Collection B ...
    (microsoft.public.sms.admin)
  • RE: How to combine existing collections to include or exclude...
    ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ... When you refresh you will have a new collection with A, computers minus the ... will see if I can't just send you the collection queries and the query ...
    (microsoft.public.sms.admin)
  • RE: filter for values in another table
    ... But you indicated that you wanted to exclude all of the values where the ID ... SELECT TableA.* ... Would I just enter all of the ids into the parenthesis? ... runs is to create a query that some people call a "frustrated outer join". ...
    (microsoft.public.access.queries)
  • Re: Help with counting only one time
    ... You want to exclude all but the first row for each order? ... In the following example, I will assume there is a primary key field named ID, and the table is called Table1. ... but I am clueless on what to do in the query even ...
    (microsoft.public.access.queries)