How can I report on members of groups in Active Directory

mishcondereya
mishcondereya used Ask the Experts™
on
Hi

I'm trying to use Crystal Reports 2008 to create a report in Active Directory. I need to report on employees and the groups they are members of. I have currently set up to queries:
1. pulls data relating to users:
  SELECT sAMAccountName, employeeID, department from 'LDAP://dc=test,dc=com' where objectClass = 'user' and objectCategory = 'person'

2. pulls a list of different groups.
SELECT  cn, mail, memberOF FROM 'LDAP://dc=test,dc=com WHERE objectCategory='group'

What I need to do is find a column I can use to link between these two queries, but I cant seem to find a column I can use to link it with. Is this possible?

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris DentPowerShell Developer
Top Expert 2010

Commented:

The member attribute of a Group will have a list of Distinguished Names for each object within that group.

So to link the two you might use:

1.

SELECT sAMAccountName, employeeID, department, distinguishedName from 'LDAP://dc=test,dc=com' where objectClass = 'user' and objectCategory = 'person'

2.

SELECT  cn, mail, member, memberOF FROM 'LDAP://dc=test,dc=com WHERE objectCategory='group'

If you want to do it the other way around, the memberOf attribute can be requested for a user, that contains the distinguishedName of each group a user belongs to. Arrays in both cases.

Does that get you any further?

Chris

Author

Commented:
Hi Chris

I'm still having issues with linking the two queries - no data is being returned in the Member or memberOf attributes in the second query, so I still cant link the two.

Any ideas why that might be?

thanks

Chris DentPowerShell Developer
Top Expert 2010

Commented:

I can help you test them outside of Crystal Reports if that's any use? If it's running within I'm afraid I'm a bit lost at sea.

Chris
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
If you dont mind, I think it would be very useful...I feel like I'm so close.

thanks

James
PowerShell Developer
Top Expert 2010
Commented:

VBScript will give us a suitable framework then if you don't mind that.

The user version first.

Chris
SEARCH_STRING = "SELECT sAMAccountName, employeeID, department, memberOf FROM 'LDAP://dc=test,dc=com' where objectClass = 'user' and objectCategory = 'person'"

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

objCommand.CommandText = SEARCH_STRING

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Searchscope") = 2
objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute

Do While Not objRecordSet.EOF

  On Error Resume Next
  strUsername = "" : strUsername = objRecordSet.Fields("sAMAccountName").Value
  strEmployeeID = "" : strEmployeeID = objRecordSet.Fields("employeeID").Value
  strDept = "" : strDept = objRecordSet.Fields("department").Value
  strMemberOf = "" : strMemberOf = Join(objRecordSet.Fields("memberOf").Value, vbCrLf)

  WScript.Echo strUsername & vbCrLf & strEmployeeID & vbCrLf & strDept & vbCrLf & _
    strMemberOf

  objRecordSet.MoveNext
Loop
	
objConnection.Close

Open in new window

Chris DentPowerShell Developer
Top Expert 2010

Commented:

In each case, the biggest change is the string at the top, one for each search.

The second returns rather a lot, really I suspect you're only interested in coupling up memberOf on the user to member on the group.

Does that help at all?

Chris
SEARCH_STRING = "SELECT  cn, mail, member, memberOf FROM 'LDAP://dc=test,dc=com WHERE objectCategory='group'"

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

objCommand.CommandText = SEARCH_STRING

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Searchscope") = 2
objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute

Do While Not objRecordSet.EOF

  On Error Resume Next
  strCN = "" : strCN = objRecordSet.Fields("cn").Value
  strMail = "" : strMail = objRecordSet.Fields("mail").Value
  strMember = "" : strMember = Join(objRecordSet.Fields("member").Value, vbCrLf)
  strMemberOf = "" : strMemberOf = Join(objRecordSet.Fields("memberOf").Value, vbCrLf)

  WScript.Echo strCN & vbCrLf & strMail & vbCrLf & strMember & vbCrLf & _
    strMemberOf

  objRecordSet.MoveNext
Loop
	
objConnection.Close

Open in new window

Author

Commented:
thanks Chris.

I follow the logic perfectly, but I cannot get a return on either the member or memberOf columns. I am looking into a potential security issue.

thanks for your help
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Even with the vbs files?

If you're looking at security, check the permissions on the member attribute. memberOf is constructed (from member), it doesn't really exist, so you won't find anything regarding security for that.

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial