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?
 
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
 
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.