Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Exporting user lists by OU in Active Directory

Posted on 2011-03-08
Medium Priority
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   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


LVL 11

Accepted Solution

Tasmant earned 2000 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?  

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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

Ready for your healthcare security check-up?

In the past few years, healthcare organizations have become a prime target for advanced attacks. Does your organization have what it needs to defend itself? Schedule your healthcare security check-up today and download our free Healthcare Security Resource Kit today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here's a look at newsworthy articles and community happenings during the last month.
This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
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…
Loops Section Overview
Suggested Courses

610 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