group and user report for a particular OU

Posted on 2008-06-11
Last Modified: 2008-12-01
I need to produce a report, txt or preferably xls that lists all the groups in the Bizmark OU and all of the members of those groups, filtering out computer accounts.  also, i'd like to list the firstname lastname instead of logon name; all logon names are by empoyee ID and are non intuitive.

I'd like teh report to look something like this.

  Burt Blylevin
  Luke Longley
  Jeb Springfield
 paul allen
 charles zesterhaus

Question by:everetjo
  • 4
  • 3
LVL 38

Expert Comment

ID: 21761448

Accepted Solution

ms-pro earned 500 total points
ID: 21762614

' Bind to RootDSE - this object is used to 

set objRootDSE = getobject("LDAP://RootDSE")

' File name to export to

strExportFile = "C:\MyExport.xls" 

' Root of search set to default naming context.

' e.g. dc=expert-exchange,dc=com

' RootDSE saves hard-coding the domain.  

' If want to search within an OU rather than the domain,

' specify the distinguished name of the ou.  e.g. 

' ou=students,dc=expert-exchange,dc=com"

strRoot = objRootDSE.Get("DefaultNamingContext")

' Filter for user accounts - could be modified to search for specific users,

' such as those with mailboxes, users in a certain department etc.

strfilter = "(&(objectCategory=Person)(objectClass=User))"

' Attributes to return from the query

strAttributes = "sAMAccountName,userPrincipalName,givenName,sn," & _

		"initials,displayName,physicalDeliveryOfficeName," & _

		"telephoneNumber,mail,wWWHomePage,profilePath," & _

		"scriptPath,homeDirectory,homeDrive,title,department," & _

		"company,manager,homePhone,pager,mobile," & _

		"facsimileTelephoneNumber,ipphone,info," & _


'Scope of the search.  Change to "onelevel" if you didn't want to search child OU's

strScope = "subtree"

set cn = createobject("ADODB.Connection")

set cmd = createobject("ADODB.Command") "Provider=ADsDSOObject;"

cmd.ActiveConnection = cn

cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _

		   strAttributes & ";" & strScope

set rs = cmd.execute

' Use Excel COM automation to open Excel and create an excel workbook

set objExcel = CreateObject("Excel.Application")

set objWB = objExcel.Workbooks.Add

set objSheet = objWB.Worksheets(1)

' Copy Field names to header row of worksheet

For i = 0 To rs.Fields.Count - 1

	objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name

	objSheet.Cells(1, i + 1).Font.Bold = True


' Copy data to the spreadsheet


' Save the workbook


' Clean up



set objSheet = Nothing

set objWB =  Nothing


set objExcel = Nothing

Open in new window


Author Comment

ID: 21814199

i get an error on line 13 char 1 saying that the directory property cannot be found in the cache

i hardcoded the AD Path info instead of rootDSE

Expert Comment

ID: 21816841
you dont need to edit on char 13.
you just need to specify the distinguished name of the ou. char 11.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 21859652
Im still new with VBS and I'm still having trouble.

if my full AD path is LDAP://OU=SALES, DC=hq, DC=nt, DC=domain, DC=com

how would I ensure that this OU is the focus ?

Expert Comment

ID: 21860724
GetObject("LDAP://OU=SALES, DC=hq, DC=nt, DC=domain, DC=com")

Author Comment

ID: 21899126
I'm still having some problems, and I think im doing something wrong.

How would I change the code to have it look in this one OU and create an xl file with all the information present in the user objects ?

using LDAP://OU=SALES, DC=hq, DC=nt, DC=domain, DC=com, I replaced the set objRootDSE statement but I'm not sure how to proceed.

Author Comment

ID: 22000268
got it working!! thanks MsPro

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now