Re: Access to a custom table

From: Jon Ellis (MSFT) (JonEllisMSFT_at_discussions.microsoft.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 10:24:07 -0700

Hi Tony,

I see now, thanks for the clarification! The 'NOT' logic throws a wrench in things. The problem with creating your own table is that I don't think would be queryable via the WBEM provider. If that doesn't work for you, how about something like this

Create a collection based on this query for machines you want to include:

select * from SMS_R_System where ResourceID not in (select ResourceID from SMS_CM_RES_COLL_<collectionid>)

where <collectionid> is the collection id of the collection that systems you don't want are contained.

That 'excluding' collection would be built using multiple (each less than 4k) subselect queries rules like so:

Rule1:
select NetbiosName from SMS_R_System where NetbiosName in ("computername1", "computername2", ...)
Rule2:
select *from SMS_R_System where NetbiosName in ("computernameA", "computernameB", ...)
...

Does this make sense?

Regards,
Jon (MSFT) 

-- 
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony" wrote:
> Jon,
> Thanks for the advice.   What you suggested would not work because the "NOT"
> logic.   Let say I have computers named "A, B, C, D" in the database.   The
> first collection query criteria would something like "select * from
> sms_system where netbiosname is NOT in ('A')".    The second collections
> query criteria would be something like "select * from sms_system where
> netbiosname is NOT in ('B')".  The third and fourth would go similarly.
> The result of first collection would consist of computers named B, C, D and
> the second collection would consist of computer named A, C, D and linking
> them under the parent collection would have "A,B,C,D" which effectively
> every computers you NOT wanted.    Clearly, this is the desired result.
> 
> Currently, the only way I know how to do it (after reading a post in
> myITForum) is to create my own table with the computer names and modify the
> Collection_Rules_SQL table to change the SQL statement to something like
> this: "select * from sms_system where netbiosname is NOT in (select hostname
> from myTable).    This way, i have an easy way to manage the list which is
> important as I have two collections that use the hostname in the list to
> build the queries (one with the "NOT" logic and one without).
> 
> 
> 
> 
> 
> and relatively simple to implement.    However, managment of the list of
> computers will be very cumbersome.
> "Jon Ellis (MSFT)" <JonEllisMSFT@discussions.microsoft.com> wrote in message
> news:D11A196C-4E2C-4FC7-9810-93FC88DFD82F@microsoft.com...
> > Hi Tony,
> >
> > Would it be possible to create multiple collections, grouped under a
> single collection, then advertise to that, selecting the option to include
> subcollections?
> > Example:
> > You have ~16K worth of computer names you want to put in a NOT subselect
> query.
> > Create 4 collections (Coll1-Coll4).  For each collection, create 1 rule
> that has the NOT subselect query.   Place Coll1-Coll4 under CollParent via
> the Admin UI.  Advertise to CollParent, and choose the option to include
> members of subcollections.
> >
> > While somewhat clumsy, this would bypass the problem of not being able to
> place multiple query rules in a single collection due to the way a NOT
> subselect works.
> >
> > Hope this helps,
> >
> > Regards,
> > Jon Ellis (MSFT)
> > -- 
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> >
> > "Tony" wrote:
> >
> > > What's the neccessary steps required to expose a custom made table in a
> SMS
> > > database such that I can use to build WQL queries for Collections.   The
> > > problem I am facing is there is a large of number of machines that I
> need to
> > > be excluded from a collections and it exceeded the 4096 characters limit
> to
> > > generate an accurate collection.    The query used for to build the
> > > collections is something like this:
> > > -------
> > > select
> > >
> sms_r_system.ResourceID,sms_r_system.ResourceType,sms_r_system.Name,sms_r_sy
> > >
> stem.SMSUniqueIdentifier,sms_r_system.ResourceDomainORWorkgroup,sms_r_system
> > > ..Client from sms_r_system where NetbiosName NOT in (machine1,
> > > machine2,....machineXXX)
> > >
> > > where number of machines can exceed the WQL limit set in SMS.
> > > -------
> > > I would not able to use mulitple of these statements and stack them to
> > > populate a collection with the logic operator, "NOT".
> > >
> > > In summary, if anyone has any idea to do the following, please share
> your
> > > ideas:
> > > 1. How to access a custom made table in SMS?
> > > 2. How to use Collections objects to define WQL query?   Essentially, I
> want
> > > to be able to have to "NOT in Collections" effect.
> > >
> > > Thanks in advance.
> > >
> > >
> > >
> > > -- 
> > > Remove ".NOSPAM" from email address to reply
> > >
> > >
> > >
> 
> 
>

Relevant Pages

  • Re: Distribute to user account objects in specific AD group in SCC
    ... For your second query, perhaps try using the subselect 'not in' ... This gave me a list of all the computers in the AD group. ... It does NOT work the same way as users in a usergroup. ... problem upon removing the computer account from the AD group and invoking ...
    (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 ... 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: Create Collection of Systems That Do Not Have an Application I
    ... SQL doesn't like the "select *" in a subselect, you want to select the same ... BTW, while this query is correct, it could be simplified to eliminate many ... not in (select ResourceID from SMS_G_System_SoftwareProduct ... >>> I am trying to create a collection of computers which do not have MS ...
    (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...
    ... is selecting the computers in my remote offices via AD but limits it only to ... select the query and say "computer name" not it limited ... This will grab all the computers that are in the limited collected minus the ...
    (microsoft.public.sms.admin)