Solved

group and user report for a particular OU

Posted on 2008-06-11
8
519 Views
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.

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

etc.
0
Comment
Question by:everetjo
  • 4
  • 3
8 Comments
 
LVL 38

Expert Comment

by:Shift-3
ID: 21761448
0
 
LVL 7

Accepted Solution

by:
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," & _

		"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
 

Author Comment

by:everetjo
ID: 21814199
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
 
LVL 7

Expert Comment

by:ms-pro
ID: 21816841
you dont need to edit on char 13.
you just need to specify the distinguished name of the ou. char 11.
0
 

Author Comment

by:everetjo
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 ?
0
 
LVL 7

Expert Comment

by:ms-pro
ID: 21860724
GetObject("LDAP://OU=SALES, DC=hq, DC=nt, DC=domain, DC=com")
0
 

Author Comment

by:everetjo
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.
0
 

Author Comment

by:everetjo
ID: 22000268
got it working!! thanks MsPro
0

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Lockdown of laptops 10 40
GPO warning 15 27
Active Directory screwed 9 37
Active Directory delegation of control to a user 3 37
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Resolve DNS query failed errors for Exchange
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 to another domain controller. Log onto the new domain controller with a user account t…
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 …

746 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

9 Experts available now in Live!

Get 1:1 Help Now