• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Filtering ADO query

L

L8knight

#1
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 Computer

R

Richard Mueller [MVP]

#2
"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 Computer

L

L8knight

#3
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 Computer

R

Richard Mueller [MVP]

#4
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 Computer

L

L8knight

#5
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 Computer

Users Who Are Viewing This Thread (Users: 1, Guests: 0)