• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

export AD attribute

what tool I can use to display the distingushed name and IP phone field of an user?  Or export these info to a file?

Thanks in advance.
  • 4
  • 3
  • 2
  • +1
3 Solutions
You can do this using dsquery and dsget, as in the example below.  Run the command on a 2003 server or a workstation with the adminpak installed.

dsquery user -samid user.name | dsget user -dn -iptel

Open in new window

You could add a redirection operator to output to a text file.

dsquery user -samid user.name | dsget user -dn -iptel -L > output.txt

Open in new window

Manpreet SIngh KhatraSolutions Architect, Project LeadCommented:
You can also get the complete data into a CSV file ...... meaning all attribute information and then filter

csvde -f C:\user.csv
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Manpreet SIngh KhatraSolutions Architect, Project LeadCommented:
You could even try with

CSVDE -s <domaincontroller> -r "(objectClass=user)" -d "OU=my,OU=org,OU=unit,DC=domain,dc=com" -f c:\scripts\myfile.csv -l "DN, IPphone"
you can try this script, this script export AD Attributes to exel

Dim ObjWb 
 Dim ObjExcel 
 Dim x, zz 
 Set objRoot = GetObject("LDAP://RootDSE") 
 strDNC = objRoot.Get("DefaultNamingContext") 
 Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the Domain using LDAP using ROotDSE 
 Call ExcelSetup("Sheet1") ' Sub to make Excel Document 
 x = 1 
 Call enummembers(objDomain) 
 Sub enumMembers(objDomain) 
 On Error Resume Next 
 Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's 
 For Each objMember In objDomain ' go through the collection 
 If ObjMember.Class = "user" Then ' if not User object, move on. 
 x = x +1 ' counter used to increment the cells in Excel 
    objwb.Cells(x, 1).Value = objMember.Class 
    ' I set AD properties to variables so if needed you could do Null checks or add if/then's to this code 
    ' this was done so the script could be modified easier. 
 SamAccountName = ObjMember.samAccountName 
 Cn = ObjMember.CN 
 FirstName = objMember.GivenName 
 LastName = objMember.sn 
 initials = objMember.initials 
 Descrip = objMember.description 
 Office = objMember.physicalDeliveryOfficeName 
 Telephone = objMember.telephonenumber 
 EmailAddr = objMember.mail 
 WebPage = objMember.wwwHomePage 
 Addr1 = objMember.streetAddress 
 City = objMember.l 
 State = objMember.st 
 ZipCode = objMember.postalCode 
 Title = ObjMember.Title 
 Department = objMember.Department 
 Company = objMember.Company 
 Manager = ObjMember.Manager 
 Profile = objMember.profilePath 
 LoginScript = objMember.scriptpath 
 HomeDirectory = ObjMember.HomeDirectory 
 HomeDrive = ObjMember.homeDrive 
 AdsPath = Objmember.Adspath 
 LastLogin = objMember.LastLogin 
 zz = 1 ' Counter for array of 2ndary email addresses 
 For each email in ObjMember.proxyAddresses 
     If Left (email,5) = "SMTP:" Then 
 Primary = Mid (email,6) ' if SMTP is all caps, then it's the Primary 
     ElseIf Left (email,5) = "smtp:" Then 
        Secondary(zz) = Mid (email,6) ' load the list of 2ndary SMTP emails into Array. 
        zz = zz + 1 
     End If 
 ' Write the values to Excel, using the X counter to increment the rows. 
 objwb.Cells(x, 2).Value = SamAccountName 
 objwb.Cells(x, 3).Value = CN 
 objwb.Cells(x, 4).Value = FirstName 
 objwb.Cells(x, 5).Value = LastName 
 objwb.Cells(x, 6).Value = Initials 
 objwb.Cells(x, 7).Value = Descrip 
 objwb.Cells(x, 8).Value = Office 
 objwb.Cells(x, 9).Value = Telephone 
 objwb.Cells(x, 10).Value = EmailAddr
 objwb.Cells(x, 11).Value = WebPage 
 objwb.Cells(x, 12).Value = Addr1 
 objwb.Cells(x, 13).Value = City 
 objwb.Cells(x, 14).Value = State 
 objwb.Cells(x, 15).Value = ZipCode 
 objwb.Cells(x, 16).Value = Title 
 objwb.Cells(x, 17).Value = Department 
 objwb.Cells(x, 18).Value = Company 
 objwb.Cells(x, 19).Value = Manager 
 objwb.Cells(x, 20).Value = Profile 
 objwb.Cells(x, 21).Value = LoginScript 
 objwb.Cells(x, 22).Value = HomeDirectory 
 objwb.Cells(x, 23).Value = HomeDrive 
 objwb.Cells(x, 24).Value = Adspath 
 objwb.Cells(x, 25).Value = LastLogin 
 objwb.Cells(x,26).Value = Primary 
 ' Write out the Array for the 2ndary email addresses. 
 For ll = 1 To 20 
 objwb.Cells(x,26+ll).Value = Secondary(ll) 
 ' Blank out Variables in case the next object doesn't have a value for the property 
 SamAccountName = "-" 
 Cn = "-" 
 FirstName = "-" 
 LastName = "-" 
 initials = "-" 
 Descrip = "-" 
 Office = "-" 
 Telephone = "-" 
 EmailAddr = "-" 
 WebPage = "-" 
 Addr1 = "-" 
 City = "-" 
 State = "-" 
 ZipCode = "-" 
 Title = "-" 
 Department = "-" 
 Company = "-" 
 Manager = "-" 
 Profile = "-" 
 LoginScript = "-" 
 HomeDirectory = "-" 
 HomeDrive = "-" 
 Primary = "-" 
 For ll = 1 To 20 
 Secondary(ll) = "" 
    End If 
    ' If the AD enumeration runs into an OU object, call the Sub again to itinerate 
    If objMember.Class = "organizationalUnit" or OBjMember.Class = "container" Then 
        enumMembers (objMember) 
    End If 
 End Sub 
 Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds Column heads to the 1st row 
 Set objExcel = CreateObject("Excel.Application") 
 Set objwb = objExcel.Workbooks.Add 
 Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName) 
 Objwb.Name = "Active Directory Users" ' name the sheet 
 objExcel.Visible = True 
 objwb.Cells(1, 2).Value = "SamAccountName" 
 objwb.Cells(1, 3).Value = "CN" 
 objwb.Cells(1, 4).Value = "FirstName" 
 objwb.Cells(1, 5).Value = "LastName" 
 objwb.Cells(1, 6).Value = "Initials" 
 objwb.Cells(1, 7).Value = "Descrip" 
 objwb.Cells(1, 8).Value = "Office" 
 objwb.Cells(1, 9).Value = "Telephone" 
 objwb.Cells(1, 10).Value = "Email" 
 objwb.Cells(1, 11).Value = "WebPage" 
 objwb.Cells(1, 12).Value = "Addr1" 
 objwb.Cells(1, 13).Value = "City" 
 objwb.Cells(1, 14).Value = "State" 
 objwb.Cells(1, 15).Value = "ZipCode" 
 objwb.Cells(1, 16).Value = "Title" 
 objwb.Cells(1, 17).Value = "Department" 
 objwb.Cells(1, 18).Value = "Company" 
 objwb.Cells(1, 19).Value = "Manager" 
 objwb.Cells(1, 20).Value = "Profile" 
 objwb.Cells(1, 21).Value = "LoginScript" 
 objwb.Cells(1, 22).Value = "HomeDirectory" 
 objwb.Cells(1, 23).Value = "HomeDrive" 
 objwb.Cells(1, 24).Value = "Adspath" 
 objwb.Cells(1, 25).Value = "LastLogin" 
 objwb.Cells(1, 26).Value = "Primary SMTP" 
 End Sub 
 MsgBox "Done" ' show that script is complete

Open in new window

nav2567Author Commented:
I appreciate for everyone's feedback.   I am still exploring which option is best to use.
I am not sure if it is my all_employees_group has too many members, I am not able to use the following to display the info I need.  If I specify another group with less members, it works.  Please advise.  
dsquery group -name all_employees_group |dsget group -members |dsget user -dn -iptel
By default, dsquery only retrieves the first 100 results.  Include -limit 0 before the first pipe to bypass this.
nav2567Author Commented:
Thanks.   I also realize using a -c switch will bypass the errors.
Last question to everyone, if I need to update the ip phone field for all users, can I use the similar trick?  
Yes, you can use dsmod with the -iptel switch.
nav2567Author Commented:
The suggestion by Shift-3 seems to work better for our issue.  But also appreciate the other two members jump in and help.

Thanks everyone again.
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.

Join & Write a Comment

Featured Post

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.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now