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
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?  

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
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 …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

730 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