export AD attribute

Posted on 2010-01-07
Last Modified: 2012-05-08
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.
Question by:nav2567
    LVL 38

    Accepted Solution

    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 | dsget user -dn -iptel

    Open in new window

    LVL 38

    Expert Comment

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

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

    Open in new window

    LVL 52

    Expert Comment

    by:Manpreet SIngh Khatra
    You can also get the complete data into a CSV file ...... meaning all attribute information and then filter

    csvde -f C:\user.csv
    LVL 52

    Assisted Solution

    by:Manpreet SIngh Khatra
    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"
    LVL 9

    Assisted Solution

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


    Author Comment

    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
    LVL 38

    Expert Comment

    By default, dsquery only retrieves the first 100 results.  Include -limit 0 before the first pipe to bypass this.

    Author Comment

    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?  
    LVL 38

    Expert Comment

    Yes, you can use dsmod with the -iptel switch.

    Author Closing Comment

    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.

    Featured Post

    Don't lose your head updating email signatures!

    Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users should you!

    Join & Write a Comment

    Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager
    The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now