group and user report for a particular OU

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.

sales
  Burt Blylevin
  Luke Longley
  Jeb Springfield
marketing
 paul allen
 charles zesterhaus

etc.
everetjoAsked:
Who is Participating?
 
ms-proCommented:

' 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," & _
		"streetAddress,postOfficeBox,l,st,postalCode,c"
'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")
 
cn.open "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
Next
 
' Copy data to the spreadsheet
objSheet.Range("A2").CopyFromRecordset(rs)
' Save the workbook
objWB.SaveAs(strExportFile)
 
' Clean up
rs.close
cn.close
set objSheet = Nothing
set objWB =  Nothing
objExcel.Quit()
set objExcel = Nothing

Open in new window

0
 
everetjoAuthor Commented:
ms.

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
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
ms-proCommented:
you dont need to edit on char 13.
you just need to specify the distinguished name of the ou. char 11.
0
 
everetjoAuthor Commented:
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 ?
0
 
ms-proCommented:
GetObject("LDAP://OU=SALES, DC=hq, DC=nt, DC=domain, DC=com")
0
 
everetjoAuthor Commented:
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.
0
 
everetjoAuthor Commented:
got it working!! thanks MsPro
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.