SMS Collection for Systems not in other collections

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

SMS Collection for Systems not in other collections

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> SMS Collection for Systems not in other collections

Page: [1]

Message

<< Older Topic Newer Topic >>

SMS Collection for Systems not in other collections - 8/14/2008 12:56:01 PM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

I need to create a collection of systems the are "not in" two or more other collections. I beleive using v_FullCollectionMembership is the right way to do this.

Does anyone have any examples of doing it this way?

Post #: 1

RE: SMS Collection for Systems not in other collections - 8/14/2008 1:26:13 PM

mhudson

Posts: 539
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

Are you looking for something like 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select Name from SMS_CM_RES_COLL_SYS00439)

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to tbiggi)

Post #: 2

RE: SMS Collection for Systems not in other collections - 8/14/2008 1:35:51 PM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

I receive an error about that not being a valid statement when trying to use this in a "Collection" rule. There are a lot of things you can do with SQL that you cannot use in WQL.

_____________________________

Tom Biggi

(in reply to mhudson)

Post #: 3

RE: SMS Collection for Systems not in other collections - 8/14/2008 1:38:33 PM

mhudson

Posts: 539
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

SYS00439 - this is my Collection ID You will need to change it to your ID. Look at the collection and properties and pull the Collection ID from there.

This will buid your collection query. Also you can limit this collection and further limit it using the not in (select Name from SMS_CM_RES_COLL_SYS00439)

change the NOT as needed.

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to tbiggi)

Post #: 4

RE: SMS Collection for Systems not in other collections - 8/14/2008 2:16:55 PM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

ok, that is working. How can I get two - or more - collections in the same subselect?

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select Name from SMS_CM_RES_COLL_TGG00yyy)

_____________________________

Tom Biggi

(in reply to mhudson)

Post #: 5

RE: SMS Collection for Systems not in other collections - 8/14/2008 2:31:06 PM

mhudson

Posts: 539
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

select Name from SMS_CM_RES_COLL_TGG00yyy and SMS_CM_RES_COLL_TGG00ZZZ) I think would work.

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to tbiggi)

Post #: 6

RE: SMS Collection for Systems not in other collections - 8/14/2008 2:39:22 PM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

"think would work" ... is not good. Have you tried this?

I thought the first query was working correctly but it is not. Does there need to be a join or joins?

Let me try to explain this again:

Collection A (maybe this is all of the systems in SMS)
Collection B (a collection of all systems of a particular feature)
Collection C (another collection - static)

Required result is Collection A minus member of Collection B and minus members of Collection C

In other words, all systems in Collection A that are not members of (Collection B or Collection C)

Thanks

_____________________________

Tom Biggi

(in reply to mhudson)

Post #: 7

RE: SMS Collection for Systems not in other collections - 8/14/2008 2:43:46 PM

mhudson

Posts: 539
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

I am not infront of my SMS server right now so I Can't test it for you. I can only pull what I have pull from my documents that I know we use and works.
I know this question has been asked several times and I have written a complex query to do just this on this forum before.

I guess someone can answer or I can see tomorrow on my server and give you a 100% answer.

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to tbiggi)

Post #: 8

RE: SMS Collection for Systems not in other collections - 8/14/2008 3:36:33 PM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

quote:

"think would work" ... is not good. Have you tried this?

Keep in mind, we're voluntarily helping and putting ideas out there that hopefully you can use to do your job. These are merely suggestions to maybe help you find your way, but it's YOUR job to take the suggestions and see if they work. Your tone seems to imply that your expecting a fully tested and working solution. Maybe that's not your intent to come off that way, it's really hard sometimes to read people's tone through email/forums. Maybe just be aware so people don't feel like you're being ungrateful for the help and expecting them to do all your work. Otherwise, we're all happy to help out when we've got time.

Anyway, here's my input...

I prefer SMS_FullCollectionMembership instead of the SMS_CM_RES_COLL stuff. And in my subselects, I prefer using the ResourceID instead of the NAME because the tables are indexed and ordered by the resourceID which usually translates to faster/more efficient queries. And the SMS_G_System_Computer_System is an unnecessary join, so I'd personally remove it. So I'm thinking something like this:

All machines in collection COLLID_A, and not in COLLID_B, COLLID_C
SELECT
sys.ResourceID,
sys.ResourceType,
sys.Name,
sys.SMSUniqueIdentifier,
sys.ResourceDomainORWorkgroup,
sys.Client
FROM
SMS_R_System AS sys
INNER JOIN SMS_FullCollectionMembership AS fcm
    ON sys.ResourceID = fcm.ResourceId
WHERE
fcm.CollectionID = "CollID_A"
AND sys.resourceID NOT IN (SELECT ResourceID
                             FROM SMS_FullCollectionMembership
                             WHERE CollectionID = "CollID_B")
AND sys.resourceID NOT IN (SELECT ResourceID
                             FROM SMS_FullCollectionMembership
                             WHERE CollectionID = "CollID_C")

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)

Post #: 9

RE: SMS Collection for Systems not in other collections - 8/14/2008 4:48:14 PM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

I did not intend to sound that way ... I greatly appreciate the help and the time that folks put into helping others.

I'm still tryiing to get this or yours to work and am not getting the results I need to be able to send out a deployment tonight but will keep working on it ...

_____________________________

Tom Biggi

(in reply to jnelson993)

Post #: 10

RE: SMS Collection for Systems not in other collections - 8/15/2008 11:13:16 AM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

That's cool.

So what's not working about it?

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)

Post #: 11

RE: SMS Collection for Systems not in other collections - 8/15/2008 11:40:17 AM

tbiggi

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline

I was still showing systems in the resultant collection that were not supposed to be there (i.e. those that were in the collections to be excluded). The resulting collection has almost 80,000 members. Here is what I ended up using in an SMS collection membership rule:

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 ResourceId not in (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID = "collID1" ) and ResourceId not in (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID = "collID2")

And the rule has a "limit to" on it instead of changing the "from SMS_R_System"

A copy-n-paste and edit of the collectionID's would not work - kept getting invalid query statement errors even when that was put all into a single line without wrap turned on.

_____________________________

Tom Biggi

(in reply to jnelson993)

Post #: 12

RE: SMS Collection for Systems not in other collections - 8/15/2008 5:18:40 PM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

That makes no sense. When I use that query, it works for me (after I plug in my collection iDs.

Oh well, if you've got something working, then don't mess with it :)

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)

Post #: 13