Exporting From Active Directory

tri_94 · April 22, 2008

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 · April 22, 2008

What is your domains functional level?

altecsole · April 22, 2008

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.

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 · April 22, 2008

sorry new to this part, what do you mean?

altecsole · April 22, 2008

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 · April 22, 2008

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 · April 23, 2008

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 · April 23, 2008

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.

'==========================================================================
' 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

Edited April 23, 2008 by altecsole

tri_94 · April 24, 2008

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 · April 24, 2008

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:

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:

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:

'==========================================================================
' 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 · April 24, 2008

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

adusers.rar

altecsole · April 24, 2008

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:

Const LEVEL3_OU = "ou=2005"

tri_94 · April 24, 2008

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

cheers

nick

altecsole · April 24, 2008

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.

Exporting From Active Directory

Recommended Posts

tri_94

Geoff

altecsole

tri_94

altecsole

Geoff

tri_94

altecsole

tri_94

altecsole

tri_94

altecsole

tri_94

altecsole

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Confirm acknowledgement of parental consent requirement for the Additional Google services

Lenovo Tablet Firmware

Outloook 365 - Classic

Home

Forums

What's New