Exporting From Active Directory

**tri_94** · 22nd April 2008, 12:41 PM

Hi there, i need to export some information from AD,

and i require the following

mailnickname
Email Address
USNCHANGED

i've tried CSVDE -f C:\export.csv -l "mailnickname,mail,usnchanged"

however i only get the mailnickname and the mail information

any ideas anyone.

thanks
nick

**Geoff** · 22nd April 2008, 02:42 PM

What is your domains functional level?

**altecsole** · 22nd April 2008, 02:54 PM

You could run the following vbscript. Just change the domain name and OU info to suit your domain. Doing it this way allows you to run it for each OU. Hope this helps.

Code:

Const DOMAIN = "dc=your_domain,dc=your_area,dc=sch,dc=uk"
Const TOPLEVEL_OU = "ou=School Users"
Const LEVEL1_OU = "ou=Staff Accounts"
Const LEVEL2_OU = "ou=staff2"

Dim objOU
Dim strOUPath
Dim user
Dim strDictUser
Dim objUSNChanged, dblUSNChanged

On Error Resume Next

strOUPath = LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
Set objOU = GetObject _
	("LDAP://" & strOUPath)

For Each user In objOU
	WScript.Echo "Username: " & user.sAMAccountName
	WScript.Echo "Mail nickname: " & user.mailNickname
	WScript.Echo "Email: " & user.mail
	Set objUSNChanged = user.Get("uSNChanged")
	dblUSNChanged = Abs(objUSNChanged.HighPart * (2^32) + objUSNChanged.LowPart)
	WScript.Echo "USNChanged: " & dblUSNChanged & VbCrLf
Next

Set objOU = nothing
Set dblUSNChanged = nothing

**tri_94** · 22nd April 2008, 02:57 PM

sorry new to this part, what do you mean?

**altecsole** · 22nd April 2008, 04:00 PM

Originally Posted by tri_94

sorry new to this part, what do you mean?

PM me with your domain name and the OU containing the users that you want to get the info for. If it's a sub OU I'll need the name of the OUs above it. I'll edit the script so you can see what I mean.

**Geoff** · 22nd April 2008, 04:07 PM

Originally Posted by tri_94

sorry new to this part, what do you mean?

Technet - What Are Active Directory Functional Levels?

You need a minimum domain functional level of Windows 2000 native otherwise usnchanged is not recorded in AD.

Additionally, usnchanged is not replicated, so you must always query the same DC.

Polling for Changes Using USNChanged (Windows)

**tri_94** · 23rd April 2008, 12:30 PM

Hi there again, sorry to be so vague about this.

We are installing some software called Safe com for monitoring printing and our finance officer has agreed to trial a system called pull printing. which is were the pupils have a pin number and they type in at the printer and then it slows a list of what they have sent to print and then you can choose which you like.

So I've got a choice of supplying a csv file with all pupils names and email addresses and a pin number. however if i can use the USNChanged number i can integrate it to create a pin when i create a new user rather then manually doing it.

So what i require is to export details such as mailnickname, (which is our pupils usernames) and mail (which is our email address). and also I've seen that the uSNChanged is a unique number..

I'm a bit of a beginner to most of the AD side of things as we have a RM network here.

from what i understand this will be the information i will need to use ?

OU=Domain Controllers,DC=cordeauxcc,DC=internal

OU=Establishments,OU=Domain Controllers,DC=cordeauxcc,DC=internal

OU=Students,OU=CHS,OU=Establishments,DC=cordeauxcc ,DC=internal

DC=cordeauxcc,DC=internal

thanks for any help
nick

**altecsole** · 23rd April 2008, 01:41 PM

Okay, give this a try.

Copy the code into Notepad and save it as a .vbs file. Open a cmd prompt and run the script.

It'll write the result to a csv file in the same directory as the one the script is run from.

I'm not sure that USNChanged is the value you want as this will change when user attributes change. You could generate auto numbers in Excel?

Let me know if you have any problems.

Code:

'==========================================================================
' NAME: ExportDetails.vbs
'
' AUTHOR: Jim Williams
' DATE  : 29/02/2008
'
' COMMENT: Gets username, email and USNChanged values for users in OU
'==========================================================================
Option Explicit

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Dim objShell
Set objShell = CreateObject("Wscript.Shell")
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Set log file path to current directory
Dim strLogFilePath, openFile
strLogFilePath = objShell.CurrentDirectory & "\ExportDetails.csv"

'Check if file exists, if not; create and add headings
If (objFSO.FileExists(strLogFilePath)) Then
	WScript.Echo "The Export File already exists. Rename, or move the file, and run again."
	WScript.Echo "Exiting..."
	WScript.Quit
	'Exit
Else
	Set openFile = objFSO.OpenTextFile(strLogFilePath, ForWriting, True)
	openFile.WriteLine "Username, Email, USNChanged"
	openFile.Close
End If

Dim strReport
strReport = GetUserInfo()

'write report to file
Set openFile = objFSO.OpenTextFile(strLogFilePath, ForAppending, True)
openFile.Write strReport
openFile.Close
WScript.Echo "Complete. Export file written to " & strLogFilePath

'tidy up
Set objShell = Nothing
Set objFSO = Nothing

Function GetUserInfo()
	Const DOMAIN = "dc=cordeauxcc,dc=internal"
	Const TOPLEVEL_OU = "ou=Establishments"
	Const LEVEL1_OU = "ou=CHS"
	Const LEVEL2_OU = "ou=Students"
	
	Dim objOU, strOUPath, user
	Dim objUSNChanged, dblUSNChanged
	Dim strMyReport
	
	On Error Resume Next
	
	strOUPath = LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
	Set objOU = GetObject _
		("LDAP://" & strOUPath)
	For Each user In objOU
		Set objUSNChanged = user.Get("uSNChanged")
		dblUSNChanged = Abs(objUSNChanged.HighPart * (2^32) + objUSNChanged.LowPart)
		strMyReport = strMyReport & user.sAMAccountName & ", " & user.mail & ", " & dblUSNChanged & VbCrLf
	Next
	'tidy and return
	Set objOU = Nothing
	Set objUSNChanged = Nothing
	GetUserInfo = strMyReport
	
End Function

**tri_94** · 24th April 2008, 01:57 PM

hi there tried that and it worked for some however i noticed that within the OU students I've got other OU groups

how can i modify it to include these ou groups aswell?

Sixth Form
2000
2001
2002
2003
2004
2005
2006
2007

thanks
nick

**altecsole** · 24th April 2008, 02:40 PM

Originally Posted by tri_94

hi there tried that and it worked for some however i noticed that within the OU students I've got other OU groups

how can i modify it to include these ou groups aswell?

Sixth Form
2000
2001
2002
2003
2004
2005
2006
2007

thanks
nick

Okay, you need to add another sub OU to your constants; like this:

Code:

	Const DOMAIN = "dc=cordeauxcc,dc=internal"
	Const TOPLEVEL_OU = "ou=Establishments"
	Const LEVEL1_OU = "ou=CHS"
	Const LEVEL2_OU = "ou=Students"
	Const LEVEL3_OU = "ou=Sixth Form"

Then change the code further down so that you can run the program for the Student OU or the sub OUs. If you have an entry for LEVEL3_OU to match the name of your sub OUs within Students it will work on that sub OU, or leave as "" to run it against the Students OU only (hope that makes sense!).

Change code to:

Code:

	If LEVEL3_OU = "" Then
		strOUPath = LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
	Else
		strOUPath = LEVEL3_OU & "," & LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
	End If

Here is the complete code, in case that's confusing:

Code:

'==========================================================================
' NAME: ExportDetails.vbs
'
' AUTHOR: Jim Williams
' DATE  : 29/02/2008
'
' COMMENT: Gets username, email and USNChanged values for users in OU
'==========================================================================
Option Explicit

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Dim objShell
Set objShell = CreateObject("Wscript.Shell")
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Set log file path to current directory
Dim strLogFilePath, openFile
strLogFilePath = objShell.CurrentDirectory & "\ExportDetails.csv"

'Check if file exists, if not; create and add headings
If (objFSO.FileExists(strLogFilePath)) Then
	WScript.Echo "The Export File already exists. Rename, or move the file, and run again."
	WScript.Echo "Exiting..."
	WScript.Quit
	'Exit
Else
	Set openFile = objFSO.OpenTextFile(strLogFilePath, ForWriting, True)
	openFile.WriteLine "Username, Email, USNChanged"
	openFile.Close
End If

Dim strReport
strReport = GetUserInfo()

'write report to file
Set openFile = objFSO.OpenTextFile(strLogFilePath, ForAppending, True)
openFile.Write strReport
openFile.Close
WScript.Echo "Complete. Export file written to " & strLogFilePath

'tidy up
Set objShell = Nothing
Set objFSO = Nothing

Function GetUserInfo()
	Const DOMAIN = "dc=cordeauxcc,dc=internal"
	Const TOPLEVEL_OU = "ou=Establishments"
	Const LEVEL1_OU = "ou=CHS"
	Const LEVEL2_OU = "ou=Students"
	Const LEVEL3_OU = "ou=Sixth Form"
	
	Dim objOU, strOUPath, user
	Dim objUSNChanged, dblUSNChanged
	Dim strMyReport
	
	On Error Resume Next
	
	If LEVEL3_OU = "" Then
		strOUPath = LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
	Else
		strOUPath = LEVEL3_OU & "," & LEVEL2_OU & "," & LEVEL1_OU & "," & TOPLEVEL_OU & "," & DOMAIN
	End If
	
	Set objOU = GetObject _
		("LDAP://" & strOUPath)
	For Each user In objOU
		Set objUSNChanged = user.Get("uSNChanged")
		dblUSNChanged = Abs(objUSNChanged.HighPart * (2^32) + objUSNChanged.LowPart)
		strMyReport = strMyReport & user.sAMAccountName & ", " & user.mail & ", " & dblUSNChanged & VbCrLf
	Next
	'tidy and return
	Set objOU = Nothing
	Set objUSNChanged = Nothing
	GetUserInfo = strMyReport
	
End Function

**tri_94** · 24th April 2008, 03:05 PM

hi there, tried that and it worked fine for the Sixth Form and also 2007, yet it didn't work for 2006 or 2005. so I've exported the entire AD to csv and uploaded it adusers.rar.

hope someone can work out where I've gone wrong

thanks
nick

**altecsole** · 24th April 2008, 03:19 PM

Originally Posted by tri_94

hi there, tried that and it worked fine for the Sixth Form and also 2007, yet it didn't work for 2006 or 2005. so I've exported the entire AD to csv and uploaded it adusers.rar.

hope someone can work out where I've gone wrong

thanks
nick

Looking at your AD structure I can't see any reason why it wouldn't work.

Did you set the Const correctly? You need to make sure you have the 'ou=' bit in as well. So, for 2005, you should have:

Code:

Const LEVEL3_OU = "ou=2005"

**tri_94** · 24th April 2008, 03:35 PM

hi there, yeah got it working, i wasnt waiting long enough for the export. many thanks for all your help

cheers
nick

**altecsole** · 24th April 2008, 03:42 PM

Originally Posted by tri_94

hi there, yeah got it working, i wasnt waiting long enough for the export. many thanks for all your help

cheers
nick

No problem, glad I could help.

LinkBack

Thread Tools

Search Thread

Exporting From Active Directory

Thanks to altecsole from:

Thanks to altecsole from:

Thanks to altecsole from:

Similar Threads

Active Directory question

"Active Directory time error" when binding OS X Server 10.5 to Active Directory

Active Directory Problem

active directory all messed up

PDA and Active Directory

Thread Information

Users Browsing this Thread

Posting Permissions