Filtering ADO query
in [VbScript]
|
From: L8knight on 26 Apr 2010 16:51 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!
From: Richard Mueller [MVP] on 26 Apr 2010 18:36 "L8knight" <tbusenbark(a)googlemail.com> wrote in message news:64cdc0ad-99e2-4a06-a4a4-c0ad26aac698(a)e1g2000vbg.googlegroups.com... > 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 --
From: L8knight on 27 Apr 2010 12:34 On Apr 26, 5:36 pm, "Richard Mueller [MVP]" <rlmueller- nos...(a)ameritech.nospam.net> wrote: > "L8knight" <tbusenb...(a)googlemail.com> wrote in message > > news:64cdc0ad-99e2-4a06-a4a4-c0ad26aac698(a)e1g2000vbg.googlegroups.com... > > > > > 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?
From: Richard Mueller [MVP] on 27 Apr 2010 13:49 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 --
From: L8knight on 27 Apr 2010 15:11 On Apr 27, 12:49 pm, "Richard Mueller [MVP]" <rlmueller- nos...(a)ameritech.nospam.net> 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!
|
Pages: 1 Prev: vbs with conditions Next: additional functionality needed using IMAPI2 (burning CD/DVD) |