Exporting user lists by OU in Active Directory

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.
Who is Participating?
TasmantConnect With a Mentor Commented:
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

Mike KlineCommented:
One tool I really like is adfind by Joe Richards   http://www.joeware.net/freetools/tools/adfind/index.htm

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


JohnLeo9Author Commented:
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?  

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

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 :)
Mike KlineCommented:
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.


JohnLeo9Author Commented:
TASMANT, do I have to run the script on a DC and does Excel need to be on the same box?
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)
JohnLeo9Author Commented:
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.
JohnLeo9Author Commented:
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.
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.
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.