asked on

Export primary SMTP and Business Phone from AD.

Can anyone provide a script to create a CSV file which lists all users, their primary SMTP and their business phone number pulled from AD?

SOLUTION

Mike Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Psy053

Here is the VBScript equivalent of Mikes solution.

All you need to do is change Example.Local to match you NB Domain Name, use notepad to save the script to something like FindUsers.vbs, and then run from the command line: CScript FindUsers.vbs

Const ADS_SCOPE_SUBTREE = 2
Const ForWriting = 2
strDomain = "Example.Local"
objCSVFile = "C:\Users.csv"

Set objFSO = CreateObject("Scripting.FilesystemObject")
set objLogFile = objFSO.OpenTextFile(objCSVFile, ForWriting, True)
objLogFile.WriteLine ("Account Name,Phone Number,Email Address")

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCOmmand.ActiveConnection = objConnection

objCommand.CommandText = "Select sAMAccountName, telephoneNumber, " & _
"Mail from 'LDAP://" & strDomain & "' " & _ 
"Where objectCategory='person' AND objectClass='user'" 

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
	strAccountName = objRecordSet.Fields("sAMAccountName").Value
	strPhone = objRecordSet.Fields("telephoneNumber").Value
	strMail = objRecordSet.Fields("Mail").Value
	objLogFile.WriteLine (strAccountName & "," & strPhone & "," & strMail)
   	objRecordSet.MoveNext
Loop

objLogFile.Close

Open in new window

SimonBrook

ASKER

Hi,

Thanks for these. However I would like first/surname in a field in the CSV instead of the account name.

What amendments would I need to make?

Thanks,

ASKER CERTIFIED SOLUTION

Psy053

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Psy053

If you would prefer Mikes option, I'm sure the command would be something like:

adfind -default -f "&(objectcategory=person)(objectclass=user)" givenName sn telephonenumber mail -nodn -csv > c:\users.csv

Mike Kline

That is exactly right to add those attributes, you can have it list any attribute. If you want to know what the attributes are behind the GUI this is a good page

http://www.selfadsi.org/user-attributes-w2k3.htm

Thanks

Mike