LDAP Query for SQL: Unable to Retrieve Users from Distribution Group in Child OU

[LTHuang]

Hello Neowin Community,

 

I have been struggling for a few days with this Windows Active Directory LDAP query for SQL and I am unsuccessful with get the desired results. I am trying to get a list of users in a distribution group that is in a child OU. Let's say that the distribution group is called "Network Group" in the "IT Network" child OU that is in the "IT Dept" parent OU. If the group was in the parent OU "IT Dept" I would have no problems, but since it is in the child OU "IT Network" under the parent OU "IT Dept", for some reason I am unable to successfully query. The following is my query (with internal.domain.com replacing my real network domain):

 

SELECT sAMAccountName as Login, CN as Name
FROM OPENQUERY
  (
    ADSI, 'SELECT sAMAccountname,CN
    FROM ''LDAP://internal.domain.com/DC=internal,DC=domain,DC=com''
      WHERE objectCategory=''person''
        AND objectClass=''user'' 
        AND memberOf=''CN=Network Group,OU=IT Network,OU=IT Dept,DC=internal,DC=domain,DC=com''
    ORDER BY CN'
  )

 

Can anyone point out what I may be doing incorrectly? I have tried switching the positions of "IT Dept" and "IT Network" perhaps thinking that there was an ordering issue with parent and child OUs, but that still did not work.

 

Thanks,

LTHuang

[_kane81]

I have no idea re LDAP queries, but in SQL you would use

memberOf IN ('CN=Network Group', 'OU=IT Network', 'OU=IT Dept', 'DC=internal' , 'DC=domain,DC=com')

 

'=' operator usually means you are trying to match on an exact string

 

* I btw probably didnt help with my answer....

[LTHuang]

Thank you _kane81 for your suggestion, which I did implement and unfortunately did not resolve the issue, but rather encountering an "error occurred while preparing the query" message. Any thoughts on this? Regardless, thank you for your time and input, and I would appreciate any further suggestions if you have any. Thanks again. - LTHuang