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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
LVL 57

Expert Comment

by:Mike Kline
ID: 35070233
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
ID: 35070402
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

ID: 35070455
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?  

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

LVL 11

Expert Comment

ID: 35070579
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
ID: 35070609
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.



Author Comment

ID: 35070847
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

ID: 35070905
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

ID: 35073875
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

ID: 35158513
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

ID: 35175984
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

Are your AD admin tools letting you down?

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

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

729 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