Exporting user lists by OU in Active Directory

Posted on 2011-03-08
Last Modified: 2012-05-11
Hi.  I have a large global active directory (single domain) with thousands of users in hundreds of cities around the world.  Each location is broken down into an OU, with a user being in a "Users" OU in a "City" OU, in a "Continent" OU, in a general location OU.  So Joe in New York would be in LOCATIONS/NA/NY/USERS/Joe.

I need to find out how many users are in each city.  I was trying to use CSVDE to export all the users to Excel, but I can not sort them by OU.  Is there a way to do this, whether with CSVDE or some other utility?

Thank you.
Question by:JohnLeo9
  • 4
  • 4
  • 2
LVL 57

Expert Comment

by:Mike Kline
Comment Utility
One tool I really like is adfind by Joe Richards

Powershell is another great opiton

so for adfind

adfind -b "DN of your OU" -f "&(objectcategory=person)(objectclass=user)"  -c

the -c is for count

If you want to list the users in the OU use

adfind -b "DN of your OU" -f "&(objectcategory=person)(objectclass=user)"  samaccountname


LVL 11

Accepted Solution

Tasmant earned 500 total points
Comment Utility
you could use the canonicalname attribute to easy filtering users and count for each location.
try this script:
'On Error Resume Next


'Excel Initialization
Set oExcel = CreateObject("Excel.Application")
	oExcel.Visible = True
Set oBook = oExcel.Workbooks.Add 'As WorkBook
	For i = oBook.Worksheets.Count To 2 Step -1
Set oSheet = oBook.Worksheets(1) 'As WorkSheet
	oSheet.Name = "Data"

'FSO Initialization
Set oFso   = WScript.CreateObject("Scripting.FileSystemObject")
oPath = oFSO.GetAbsolutePathName(".")

'LDAP Initialization
Set rootDSE = GetObject("LDAP://RootDSE")
strDomain = "LDAP://" & rootDSE.Get("defaultNamingContext")
strfilter = "(&(objectCategory=Person)(objectClass=User))"
strAttributes = "distinguishedName,SAMaccountname,canonicalName,userAccountControl" 
strScope = "subtree"

'ADO Initialization
Set objConnection = CreateObject("ADODB.Connection") 
Set objcommand = CreateObject("ADODB.Command") 
objConnection.Provider = "ADsDSOObject" 
objConnection.Open "Active Directory Provider" 
objcommand.ActiveConnection = objConnection

'ADO Properties
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Cache Results") = True

'Execute Query
objCommand.CommandText = "<" & strDomain & ">;" & strFilter & ";" & strAttributes & ";" & strScope
Set objRecordSet = objCommand.Execute

'Write Excel Headers
For i = 0 To objRecordSet.Fields.Count - 1
	With oSheet.Cells(1, i + 1)
		.Value = objRecordSet.Fields(i).Name 
		.Font.Bold = True
	End With

'Write Excel Data
x = 2
Do Until objRecordSet.EOF
	For i = 0 To objRecordSet.Fields.Count - 1
		Select Case objRecordSet.Fields(i).Name
			Case "userAccountControl"
				If objRecordSet.Fields(i).Value And ADS_UF_ACCOUNTDISABLE Then
					oSheet.Cells(x, i + 1).Value = "Disabled" 
					oSheet.Cells(x, i + 1).Value = "Enabled"
				End If
			Case "canonicalName"
				If Not IsNull(objRecordSet.Fields(i).Value) Then
					arrAttr = objRecordSet.Fields(i).Value
					oSheet.Cells(x, i + 1).Value = LCase(Mid(arrAttr(0),1,InStrRev(arrAttr(j),"/")))
					arrAttr = Null
				End If
			Case Else
				oSheet.Cells(x, i + 1).Value = LCase(objRecordSet.Fields(i).Value)
		End Select
	'Just to scroll Excel
	If x > 35 Then
		AutoScroll oSheet.Cells(x - 20, 1)
	End If
	x = x + 1

'WScript.Echo "next"

'Excel constants
Const xlDatabase = 1					'Data from Excel
Const xlPivotTableVersion14 = 4			'Office 2010 Pivot Table
Const xlColumnClustered = 51			'Graph Type
Const msoElementDataLabelCenter = 202	'Graph Show Count on Column
Const xlSum = -4157						'Pivot Table Operation
Const xlCount = -4112					'Pivot Table Operation
Const xlRowField = 1					'Pivot Table Row Field
Const xlPageField = 3 					'Pivot Table Filter Field
Const xlDataField = 4					'Pivot Table Data Field
Const xlLocationAsNewSheet = 1			'Graph Location on new Sheet

'Adding a new WorkSheet for Pivot Table
Set xlSheet  = oBook.Worksheets.Add
xlSheet.Name = "Report"

'Create Pivot Table (type, datasource, version).createPivotTable start, name
'WScript.Echo "Data!R1C1:R" & objRecordSet.RecordCount + 1 & "C" & objRecordSet.Fields.Count
oBook.PivotCaches.Create(xlDatabase, "Data!R1C1:R" & objRecordSet.RecordCount + 1 & "C" & objRecordSet.Fields.Count, xlPivotTableVersion14).CreatePivotTable xlSheet.Range("A1"), "ScriptReport"

'Add scriptPath to Row
With xlSheet.PivotTables("ScriptReport").PivotFields(objRecordSet.Fields("canonicalName").Name)
	.Orientation = xlRowField
	.Position = 1
End With

'Add distinguishedName to Data (we need this to count empty scriptPath, else empty are not count)
With xlSheet.PivotTables("ScriptReport").PivotFields(objRecordSet.Fields("distinguishedName").Name)
		.Orientation = xlDataField
		.Position = 1
End With

oBook.ShowPivotTableFieldList = False 	'Close PivotTable Menu
oExcel.DisplayAlerts = False

'Generate Graph Report
Set oChart = oBook.Charts.Add
oChart.ChartType = xlColumnClustered
oChart.ChartTitle.Text = "Sum of scriptPath"
oChart.Location xlLocationAsNewSheet, "Scripts Chart"
oChart.SetElement msoElementDataLabelCenter

oFile = oPath & "\Export Users v1.0 " & FormatDateTime(Now, vbLongDate) & ".xlsx"
If oFso.FileExists(oFile) Then oFso.DeleteFile oFile, True
oExcel.ActiveWorkbook.SaveAs oFile
oExcel.ACtiveWorkbook.Saved = True

WScript.Echo "End"

Sub AutoScroll(oRange)
	oExcel.Goto oRange, True
End Sub

Open in new window


Author Comment

Comment Utility
MKLINE-- From your description it looks like I would have to type in the name of each OU.  There a couple hundred of them so that would take some time.

TASMANT-- I am not a scripting guy.  What exactly does that script do and how do I run it?  

LVL 11

Expert Comment

Comment Utility
copy/paste the whole script in a text file named "myscript.vbs".
be sure the extension is ".vbs"
just double-click on
it will bind to AD, and export in an Excel speadsheet some attributes (distinguishedname, samaccountname, canonicalname, part of useraccountcontrol = disable or enable)
then, it will create a pivot table with canonical name to be able to count the number of users per canonicalname.
but just run it, you'll see by yourself :)
LVL 57

Expert Comment

by:Mike Kline
Comment Utility
You are right, I was thinking you wanted one OU's count.  Not sure if there is an easy way to break it down in adfind  i.e.

OU UserObjectCount

I'll let you know if I come up with something.


How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline


Author Comment

Comment Utility
TASMANT, do I have to run the script on a DC and does Excel need to be on the same box?
LVL 11

Expert Comment

Comment Utility
Excel need to be on the same box.
you don't need to run it on DC (maybe would be quicker, but do you really need it quicker :p)

Author Comment

Comment Utility
It looked like that script was working great and it was still going after 14000 users, but then it stopped and all the data disappeared.  I got the error "Invalid procedure call or argument" Code 800A0005 on line 102, char 1.

Author Closing Comment

Comment Utility
The script worked until the end, so I was able to stop it with only a couple of users left in order to copy the data in the spreadsheet.  Then I manually counted users out.
LVL 11

Expert Comment

Comment Utility
to correct the script and get it working, add this line when Excel const are defined (line 83)
Const xlPivotTableVersion10 = 1		'Office XP
Const xlPivotTableVersion11 = 2		'Office 2003
Const xlPivotTableVersion12 = 3		'Office 2007
Const xlPivotTableVersion2000 = 0	'Office 2000

Open in new window

and adjust the line 102 with your current office version.
by example, if you have Office 2003, replace the "xlPivotTableVersion14" in the line with "xlPivotTableVersion11"

it should work better.
more, if you encounter errors, the data are always keeped but look on the different Excel sheets, you should retrieve them.

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
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 tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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…

728 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

10 Experts available now in Live!

Get 1:1 Help Now