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?
LVL 1
SimonBrookAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike KlineCommented:
I really like adfind by Joe Richards for this
http://www.joeware.net/freetools/tools/adfind/index.htm

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

Thanks

Mike
0
Psy053Commented:
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

0
SimonBrookAuthor Commented:
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,
0
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

Psy053Commented:
I have modified the script. It should now give the first name and last name.

Please note that the script will put the first name and last name into their own columns, if you want them in the same column:
Replace this line in the script:
objLogFile.WriteLine (strGivenName & "," & strSN & "," & strPhone & "," & strMail)
With this:
objLogFile.WriteLine (strGivenName & " " & strSN & "," & strPhone & "," & strMail)


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 givenName, sn, 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
	strGivenName = objRecordSet.Fields("givenName").Value
	strSN = objRecordSet.Fields("sn").Value
	strPhone = objRecordSet.Fields("telephoneNumber").Value
	strMail = objRecordSet.Fields("Mail").Value
	objLogFile.WriteLine (strGivenName & "," & strSN & "," & strPhone & "," & strMail)


   	objRecordSet.MoveNext
Loop

objLogFile.Close

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Psy053Commented:
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


0
Mike KlineCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.