Fetching members of groups in AD

To continue from my last post. Now that I could query the customer AD I wanted to fetch all users belonging to a certain group. I found a lot of "solutions" of how to construct my LDAP query through google though non worked.

In the end, with a lot of trial and error, I managed to get it working. This is the way you query for members in a group using LDAP:

First find our the DN of your group (replace group1 with the name of your group):

SELECT sAMAccountName, distinguishedName
FROM   OPENQUERY(ADSI2,'<LDAP://server.customer.local/DC=customer,DC=local>;(&(objectClass=group)(cn=group1));sAMAccountName,distinguishedName;subtree')

Now copy the distinguishedName from the results and replace <distinguishedName> in the following query:

SELECT sAMAccountName, userPrincipalName, Name, department, Manager, title, mobile, ipPhone, mail, distinguishedName, streetAddress, l, postalCode, co, company
FROM   OPENQUERY(ADSI3,'<LDAP://server.customer.local/DC=customer,DC=local>;(&(objectcategory=person)(objectClass=user)(memberOf=<distinguishedName>));sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree')                      
There you are, all users in group "group1"