locked
Collection query that checks two AD groups RRS feed

  • Question

  • I am trying to write a query for a dynamic collection to install Office 2007 from an AD group, however, we also have a a group for office 2010 and we would like to make sure the computer is not in that group before adding it to the 2007 collection. T hope that makes sense. 

    I built a query with a sub query using the query design tool and I get the error: "The query statement is not valid. Please enter a valid statement"

    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.SystemGroupNam = "AD\\Office 2007 " and SMS_R_System.Name not in (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 = "AD\\Office 2010")

    I have ran both queries seperatly in their own collections and they work. So i think the problem is either the "not in" statement or the sub query. Any ideas or suggestions?

     

    Wednesday, September 14, 2011 11:15 PM

Answers

  • Was that simply a bad copy/paste job, forgetting the E on systemgroupname?

     

    Select SMS_R_SYSTEM.ResourceID
    from SMS_R_System
    where SMS_R_System.SystemGroupName = "AD\\Office 2007"
    and
    SMS_R_System.ResourceID not in
    (
    select SMS_R_SYSTEM.ResourceID
    from SMS_R_System where SMS_R_System.SystemGroupName = "AD\\Office 2010"
    )

     

    If that was just a bad copy/paste job, and you did have the E on systemgroupname, I've seen this sometimes; in a subselect the wql doesn't like the double quotes:

     

    Select SMS_R_SYSTEM.ResourceID
    from SMS_R_System
    where SMS_R_System.SystemGroupName = "AD\\Office 2007"
    and
    SMS_R_System.ResourceID not in
    (
    select SMS_R_SYSTEM.ResourceID
    from SMS_R_System where SMS_R_System.SystemGroupName = 'AD\\Office 2010'
    )

     

    Oh, and notice I've used ResourceID on your subselect? not Name?  Name is not indexed, resourceid is.  You'll be happier in the long run if you use resourceid, not name; from an efficiency point of view.


    Standardize. Simplify. Automate.
    Thursday, September 15, 2011 12:57 AM

All replies

  • Was that simply a bad copy/paste job, forgetting the E on systemgroupname?

     

    Select SMS_R_SYSTEM.ResourceID
    from SMS_R_System
    where SMS_R_System.SystemGroupName = "AD\\Office 2007"
    and
    SMS_R_System.ResourceID not in
    (
    select SMS_R_SYSTEM.ResourceID
    from SMS_R_System where SMS_R_System.SystemGroupName = "AD\\Office 2010"
    )

     

    If that was just a bad copy/paste job, and you did have the E on systemgroupname, I've seen this sometimes; in a subselect the wql doesn't like the double quotes:

     

    Select SMS_R_SYSTEM.ResourceID
    from SMS_R_System
    where SMS_R_System.SystemGroupName = "AD\\Office 2007"
    and
    SMS_R_System.ResourceID not in
    (
    select SMS_R_SYSTEM.ResourceID
    from SMS_R_System where SMS_R_System.SystemGroupName = 'AD\\Office 2010'
    )

     

    Oh, and notice I've used ResourceID on your subselect? not Name?  Name is not indexed, resourceid is.  You'll be happier in the long run if you use resourceid, not name; from an efficiency point of view.


    Standardize. Simplify. Automate.
    Thursday, September 15, 2011 12:57 AM
  • The error is gone but the results are wrong.

    It does look like a bad copy/paste, thanks. I added the 'E' back onto SMS_R_System.SystemGroupName but received the same error. I then changed the quotes like you suggested but still get the error.  I then copied your code and changed the AD group names to the real ones and SCCM accepted it and lets me save it. But now I am receiving the wrong results, which is a problem I was having earlier in my troubleshooting. 

    It looks like I am getting every computer that is on the office 2007 group and ever other computer that is not in office 2010 group, which is most of the systems. What I would like is only computers in the 2007 group as long as they are not already in the 2010 group. Should that question should be in a different form since it is different than the original? And your answer did correct the original problem so thanks! 

     

    Thank you for your assistance. 

    Thursday, September 15, 2011 4:40 PM