Windows Vista Forums

Filtering ADO query

  1. #1


    L8knight Guest

    Filtering ADO query

    Hopefully someone can point me in the right direction, spent too long
    on this already

    I am trying to query AD to get all groups with "SECURITY" in the name
    (ie: G USA Security Team" or GG_USA_FIREWALL_SECURITY_RW). Since our
    AD is so vast other methods I have tried just take too long (although
    they do work). So I'm trying to filter ADO results since its much
    quicker but I don't have much experience with it.

    Part of my code:

    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory domain.

    Set objRootDSE = GetObject("LDAP://RootDSE")

    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"

    ' Filter on user object with specified NT name.
    'strFilter = "(objectClass=group)"
    strFilter = "(&(objectCategory=group)(|(cn=*security*)))" <----of
    course this doesn't work

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "name,grouptype"

    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    ";subtree"
    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False


    ' Run the query.
    Set adoRecordset = adoCommand.Execute
    'adoRecordset.Filter = "cn LIKE '%Security%'" <--- My other attempt


    The last line above, commented out was my original attempt; I would
    return all Groups then try to filter them by looking for SECURITY in
    the container name. I at least got results with this method but they
    were not accurate based on what I can view in AD. So then I tried the
    strFilter but I think you can't use double wildcards, the word has to
    either be at the beginning or end of the string.

    Hopefully you understand what I'm after, I'm not the best at
    explaining things

    Thanks in advance for any help!

      My System SpecsSystem Spec

  2.   


  3. #2


    Richard Mueller [MVP] Guest

    Re: Filtering ADO query


    "L8knight" <tbusenbark@newsgroup> wrote in message
    news:64cdc0ad-99e2-4a06-a4a4-c0ad26aac698@newsgroup

    > Hopefully someone can point me in the right direction, spent too long
    > on this already
    >
    > I am trying to query AD to get all groups with "SECURITY" in the name
    > (ie: G USA Security Team" or GG_USA_FIREWALL_SECURITY_RW). Since our
    > AD is so vast other methods I have tried just take too long (although
    > they do work). So I'm trying to filter ADO results since its much
    > quicker but I don't have much experience with it.
    >
    > Part of my code:
    >
    > Set adoCommand = CreateObject("ADODB.Command")
    > Set adoConnection = CreateObject("ADODB.Connection")
    > adoConnection.Provider = "ADsDSOObject"
    > adoConnection.Open "Active Directory Provider"
    > adoCommand.ActiveConnection = adoConnection
    >
    > ' Search entire Active Directory domain.
    >
    > Set objRootDSE = GetObject("LDAP://RootDSE")
    >
    > strDNSDomain = objRootDSE.Get("defaultNamingContext")
    > strBase = "<LDAP://" & strDNSDomain & ">"
    >
    > ' Filter on user object with specified NT name.
    > 'strFilter = "(objectClass=group)"
    > strFilter = "(&(objectCategory=group)(|(cn=*security*)))" <----of
    > course this doesn't work
    >
    > ' Comma delimited list of attribute values to retrieve.
    > strAttributes = "name,grouptype"
    >
    > ' Construct the LDAP syntax query.
    > strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    > ";subtree"
    > adoCommand.CommandText = strQuery
    > adoCommand.Properties("Page Size") = 100
    > adoCommand.Properties("Timeout") = 30
    > adoCommand.Properties("Cache Results") = False
    >
    >
    > ' Run the query.
    > Set adoRecordset = adoCommand.Execute
    > 'adoRecordset.Filter = "cn LIKE '%Security%'" <--- My other attempt
    >
    >
    > The last line above, commented out was my original attempt; I would
    > return all Groups then try to filter them by looking for SECURITY in
    > the container name. I at least got results with this method but they
    > were not accurate based on what I can view in AD. So then I tried the
    > strFilter but I think you can't use double wildcards, the word has to
    > either be at the beginning or end of the string.
    >
    > Hopefully you understand what I'm after, I'm not the best at
    > explaining things
    >
    > Thanks in advance for any help!
    The following filter worked for me:

    strFilter = "(&(objectCategory=group)(cn=*security*))"

    This returns only the groups with the string "security" in the Common Name.

    --
    Richard Mueller
    MVP Directory Services
    Hilltop Lab - http://www.rlmueller.net
    --



      My System SpecsSystem Spec

  4. #3


    L8knight Guest

    Re: Filtering ADO query

    On Apr 26, 5:36*pm, "Richard Mueller [MVP]" <rlmueller-
    nos...@newsgroup> wrote:

    > "L8knight" <tbusenb...@newsgroup> wrote in message
    >
    > news:64cdc0ad-99e2-4a06-a4a4-c0ad26aac698@newsgroup
    >
    >
    >

    > > Hopefully someone can point me in the right direction, spent too long
    > > on this already
    >

    > > I am trying to query AD to get all groups with "SECURITY" in the name
    > > (ie: G USA Security Team" or GG_USA_FIREWALL_SECURITY_RW). *Since our
    > > AD is so vast other methods I have tried just take too long (although
    > > they do work). *So I'm trying to filter ADO results since its much
    > > quicker but I don't have much experience with it.
    >

    > > Part of my code:
    >

    > > Set adoCommand = CreateObject("ADODB.Command")
    > > Set adoConnection = CreateObject("ADODB.Connection")
    > > adoConnection.Provider = "ADsDSOObject"
    > > adoConnection.Open "Active Directory Provider"
    > > adoCommand.ActiveConnection = adoConnection
    >

    > > ' Search entire Active Directory domain.
    >

    > > Set objRootDSE = GetObject("LDAP://RootDSE")
    >

    > > strDNSDomain = objRootDSE.Get("defaultNamingContext")
    > > strBase = "<LDAP://" & strDNSDomain & ">"
    >

    > > ' Filter on user object with specified NT name.
    > > 'strFilter = "(objectClass=group)"
    > > strFilter = "(&(objectCategory=group)(|(cn=*security*)))" * *<----of
    > > course this doesn't work
    >

    > > ' Comma delimited list of attribute values to retrieve.
    > > strAttributes = "name,grouptype"
    >

    > > ' Construct the LDAP syntax query.
    > > strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    > > ";subtree"
    > > adoCommand.CommandText = strQuery
    > > adoCommand.Properties("Page Size") = 100
    > > adoCommand.Properties("Timeout") = 30
    > > adoCommand.Properties("Cache Results") = False
    >

    > > ' Run the query.
    > > Set adoRecordset = adoCommand.Execute
    > > 'adoRecordset.Filter = "cn LIKE '%Security%'" *<--- My other attempt
    >

    > > The last line above, commented out was my original attempt; I would
    > > return all Groups then try to filter them by looking for SECURITY in
    > > the container name. *I at least got results with this method but they
    > > were not accurate based on what I can view in AD. *So then I tried the
    > > strFilter but I think you can't use double wildcards, the word has to
    > > either be at the beginning or end of the string.
    >

    > > Hopefully you understand what I'm after, I'm not the best at
    > > explaining things
    >

    > > Thanks in advance for any help!
    >
    > The following filter worked for me:
    >
    > strFilter = "(&(objectCategory=group)(cn=*security*))"
    >
    > This returns only the groups with the string "security" in the Common Name.
    >
    > --
    > Richard Mueller
    > MVP Directory Services
    > Hilltop Lab -http://www.rlmueller.net
    > --
    Thanks very much! You've confirmed that this works which is good. So
    through testing I've determined that the problem is, it is not walking
    the subtree even though its set:

    strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    ";subtree"

    We have an OU structure like this OU - America, OU - Indiana, OU -
    DataGroups.

    If I point the script (within strBase) to point directly at the
    Indiana OU it will go through the subtree and find the groups. If I
    point it to America it will find nothing (although groups meeting my
    criteria are in all the state OUs).
    Is there a limitation to "subtree" as to how many levels down it will
    go? I thought subtree will walk every sub-ou?

      My System SpecsSystem Spec

  5. #4


    Richard Mueller [MVP] Guest

    Re: Filtering ADO query


    Thanks very much! You've confirmed that this works which is good. So
    through testing I've determined that the problem is, it is not walking
    the subtree even though its set:

    strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    ";subtree"

    We have an OU structure like this OU - America, OU - Indiana, OU -
    DataGroups.

    If I point the script (within strBase) to point directly at the
    Indiana OU it will go through the subtree and find the groups. If I
    point it to America it will find nothing (although groups meeting my
    criteria are in all the state OUs).
    Is there a limitation to "subtree" as to how many levels down it will
    go? I thought subtree will walk every sub-ou?
    -------------

    Scope subtree should have no limit on the number of levels. I can't
    duplicate the problem. I tested in a domain with 3 levels of OU's. For
    example, I searched for an object like:

    cn=Jim Smith,ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com

    I can specify the base of the script as "dc=MyDomain,dc=com",
    "ou=Parish,dc=MyDomain,dc=com", "ou=School,ou=Parish,dc=MyDomain,dc=com", or
    "ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com".

    In all 4 cases, the query finds the object in OU=Grade8, when I specify
    scope subtree. What you describe is what I would expect if the scope were
    onelevel. With this scope the object in my example would only be found if
    the base were specified as
    "ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com".

    --
    Richard Mueller
    MVP Directory Services
    Hilltop Lab - http://www.rlmueller.net
    --



      My System SpecsSystem Spec

  6. #5


    L8knight Guest

    Re: Filtering ADO query

    On Apr 27, 12:49*pm, "Richard Mueller [MVP]" <rlmueller-
    nos...@newsgroup> wrote:

    > Thanks very much! *You've confirmed that this works which is good. *So
    > through testing I've determined that the problem is, it is not walking
    > the subtree even though its set:
    >
    > strQuery = strBase & ";" & strFilter & ";" & strAttributes &
    > ";subtree"
    >
    > We have an OU structure like this OU - America, OU - Indiana, OU -
    > DataGroups.
    >
    > If I point the script (within strBase) to point directly at the
    > Indiana OU it will go through the subtree and find the groups. *If I
    > point it to America it will find nothing (although groups meeting my
    > criteria are in all the state OUs).
    > Is there a limitation to "subtree" as to how many levels down it will
    > go? *I thought subtree will walk every sub-ou?
    > -------------
    >
    > Scope subtree should have no limit on the number of levels. I can't
    > duplicate the problem. I tested in a domain with 3 levels of OU's. For
    > example, I searched for an object like:
    >
    > cn=Jim Smith,ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com
    >
    > I can specify the base of the script as "dc=MyDomain,dc=com",
    > "ou=Parish,dc=MyDomain,dc=com", "ou=School,ou=Parish,dc=MyDomain,dc=com", or
    > "ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com".
    >
    > In all 4 cases, the query finds the object in OU=Grade8, when I specify
    > scope subtree. What you describe is what I would expect if the scope were
    > onelevel. With this scope the object in my example would only be found if
    > the base were specified as
    > "ou=Grade8,ou=School,ou=Parish,dc=MyDomain,dc=com".
    >
    > --
    > Richard Mueller
    > MVP Directory Services
    > Hilltop Lab -http://www.rlmueller.net
    > --
    Okay, somehow it works now. I have no idea why. The only thing I
    did was remove ";subtree" to try running the script without it since
    "subtree" is the default operation anyway. It didn't work so I typed
    subtree back in and ran again and it worked. I noticed, and this
    shouldn't make a difference, that in my first version there is a space
    after subtree (";subtree"_) and when I re-typed it in without the
    space at the end I started getting the proper results. I can't
    believe that space could have made the difference but I made no other
    changes.

    Coding is fun!

    Thanks again for all your help, I can finally get something else done
    today!

      My System SpecsSystem Spec


Filtering ADO query
Similar Threads
Thread Forum
Filtering Vista music pictures video
keyboard filtering VB Script
ListBox Filtering .NET General
filtering my inbox Vista mail
WLM & AOL Junk Filtering Live Mail