How to output the home directory of a list of domain users

Discussion in 'Server Networking' started by Spin, Mar 14, 2008.

  1. Spin

    Spin Guest

    Gurus,

    Given a list of users in the domain (say, users.txt) is there a dsquery
    command that will output their home directory into a nice to read format?
     
    Spin, Mar 14, 2008
    #1
    1. Advertisements

  2. Here is an example of all users w/o a home directory using the freeware
    utility adfind from joeware.net

    adfind -f
    "(&(objectCategory=person)(objectClass=user)(!(homeDirectory=*)))" -b
    "dc=yourdomain,dc=com" samaccountname homeDirectory -list
     
    Paul Bergson [MVP-DS], Mar 14, 2008
    #2
    1. Advertisements

  3. Spin

    lforbes Guest

    Dim objExcel
    Dim objRecordSet
    Dim u
    Dim c
    Dim strName
    Dim strPath
    Dim root
    Dim ou
    Dim TextXL
    Dim CRLF
    dim oArgs
    Dim grp
    Dim ObjUser

    Const ADS_SCOPE_SUBTREE = 2

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

    objCommand.Properties("Page Size") = 1000
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

    set oArgs=wscript.arguments
    If oArgs.Count = 0 Then
    TextXL = InputBox("This scripts reads an Excel spreadsheet and adds"
    & _
    "users from the Windows NT DS via ADSI." & CRLF & CRLF & _
    "Before starting, change the DS root in the EXCEL spreadsheet to
    match " & _
    "your DS." & CRLF & CRLF & _
    "Type in the path of a file containing users to add or delete" & CRLF
    & CRLF & _
    "Sample Add User file: ADDUSERS.XLS" & CRLF & _
    "Sample Delete User file: DELUSERS.XLS" & CRLF)
    'Else file containing users is the first argument
    Else
    TextXL = oArgs.item(0)
    End If

    If TextXL = "" Then
    WScript.Echo "No input file provided. Stopping the script now."
    WScript.Quit(1)
    End If

    Set objExcel = CreateObject("Excel.Application")
    objExcel.workbooks.open TextXL
    objExcel.Visible = True

    i = 1

    '50
    objCommand.CommandText = _
    "SELECT ADsPath, givenName, SN, homedrive, samAccountName,
    telephoneNumber, mail, description, department FROM 'LDAP://dc=domain,
    dc=local' WHERE objectCategory='user'"
    Set objRecordSet = objCommand.Execute

    objRecordSet.MoveFirst
    Do Until objRecordSet.EOF
    strPath = objRecordSet.Fields("ADsPath").Value
    Set objUser = GetObject(strPath)
    objExcel.Cells(i,1) = objUser.samAccountName
    objExcel.Cells(i,2) = objUser.SN
    objExcel.Cells(i,3) = objUser.givenName
    objExcel.Cells(i,4) = objUser.homedrive
    i = i + 1
    objRecordset.MoveNext
    Loop

    objConnection.Close
     
    lforbes, Mar 14, 2008
    #3
  4. Spin

    SunTzu77 Guest

    Hi Spin,

    I found the following helpful when I wanted to do a similar exercise.

    If you haven't sorted it by Monday when I am back in work I will copy the
    modified version in this post for you that just brings back the samAccount
    name and the Home Directory. into an excel spreadsheet.

    http://www.wisesoft.co.uk/Scripts/display_script.aspx?id=133
     
    SunTzu77, Mar 14, 2008
    #4
  5. You probably want objUser.homeDirectory as well as objUser.homeDrive. And,
    if you retrieve the attribute values with ADO, there is no need to bind to
    the user object (which slows down the script). A big advantage of ADO is
    that it retrieves all attribute values in one operation, without the need to
    bind to each AD object. I would suggest (in part):
    ==========
    ' Write column headings in first row of spreadsheet.
    objExcel.Cells(1, 1).Value = "Logon Name"
    objExcel.Cells(1, 2).Value = "Last Name"
    objExcel.Cells(1, 3).Value = "First Name"
    objExcel.Cells(1, 4).Value = "Home Drive"
    objExcel.Cells(1, 5).Value = "Home Directory"

    objCommand.CommandText = "SELECT sAMAccountName, sn, givenName, homeDrive,
    homeDirectory " _
    & "FROM 'LDAP://dc=domain,dc=local' " _
    & "WHERE objectCategory='person' AND objectClass='user'"
    Set objRecordSet = objCommand.Execute

    ' Start adding users in row 2.
    i = 2
    Do Until objRecordSet.EOF
    objExcel.Cells(i, 1).Value = objRecordSet.Fields("sAMAccountName").Value
    objExcel.Cells(i, 2).Value = objRecordSet.Fields("sn").Value
    objExcel.Cells(i, 3).Value = objRecordSet.Fields("givenName").Value
    objExcel.Cells(i, 4).Value = objRecordSet.Fields("homeDrive").Value
    objExcel.Cells(i, 5).Value = objRecordSet.Fields("homeDirectory").Value
    i = i + 1
    objRecordset.MoveNext
    Loop

    ' Clean up.
    objRecordSet.Close
    objConnection.Close
    ============
    If desired, you could retrieve the DNS domain name ("dc=domain,dc=local" in
    the example) programmatically from the RootDSE object. For example:
    ============
    ' Determine the DNS domain from the RootDSE object.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")

    objCommand.CommandText = "SELECT sAMAccountName, sn, givenName, homeDrive,
    homeDirectory " _
    & "FROM 'LDAP://" & strDNSDomain & "' " _
    & "WHERE objectCategory='person' AND objectClass='user'"
     
    Richard Mueller [MVP], Mar 15, 2008
    #5
  6. Spin

    lforbes Guest

    Thanks. I posted and then tried to edit but MS boards don't let you do that.
    I bind to the user object with this script simply because I use it 99% of the
    time to pull the user info and then put info back into AD like if I want to
    change 1000 emails or 1000 descriptions to individual ones from an excel
    sheet. I just modified it for his purpose.

    I have 2500 AD users and it runs pretty fast so I don't worry about slowing
    it down any.

    Cheers,
    Lara
     
    lforbes, Mar 15, 2008
    #6
  7. Spin

    Spin Guest

    <snipped>

    Hiya Rich:

    I get the following error when running your script:

    ---------------------------
    Windows Script Host
    ---------------------------
    Script: C:\Documents and Settings\Admin\Desktop\Retrieve HomDrv of user.vbs
    Line: 2
    Char: 1
    Error: Object required: 'objExcel'
    Code: 800A01A8
    Source: Microsoft VBScript runtime error
     
    Spin, Mar 16, 2008
    #7
  8. Spin

    lforbes Guest

    Spin,

    Try my script. It should work as-is if you change your network in the script.
    Just open a blank xls document. With vbscript you can just drop it on the
    vbs file and it will open.

    You don't have to worry about the lack of time taken to bind to users. I
    have 2500 users and it takes no time.

    You need to modify the following line to include homedirectory as well.

    "SELECT ADsPath, givenName, SN, homedrive, homedirectory, samAccountName,
    telephoneNumber, mail, description, department FROM 'LDAP://dc=domain,
    dc=local' WHERE objectCategory='user'"
    Set objRecordSet = objCommand.Execute

    objRecordSet.MoveFirst
    Do Until objRecordSet.EOF
    strPath = objRecordSet.Fields("ADsPath").Value
    Set objUser = GetObject(strPath)
    objExcel.Cells(i,1) = objUser.samAccountName
    objExcel.Cells(i,2) = objUser.SN
    objExcel.Cells(i,3) = objUser.givenName
    objExcel.Cells(i,4) = objUser.homedirectory
     
    lforbes, Mar 16, 2008
    #8
  9. Spin

    Spin Guest

    I've not sorted it yet. If you're still willing, I'll take you up on your
    offer. :)
     
    Spin, Mar 17, 2008
    #9
  10. Look for the old Tool from Somarsoft called DumpSec (formerly DumpACL).

    SystemTools.com - Windows NT/2000/XP/2003 System Management Software
    http://www.somarsoft.com/



    --
    Phillip Windell
    www.wandtv.com

    The views expressed, are my own and not those of my employer, or Microsoft,
    or anyone else associated with me, including my cats.
    -----------------------------------------------------
     
    Phillip Windell, Mar 17, 2008
    #10
  11. Sorry, what I posted was meant to modify the earlier script that was posted
    by Iforbes. What I posted is not complete. The error is raised because the
    object reference objExcel was never created in a "Set objExcel" statement.
    The snippet I posted should replace the following in the original program:
    =========
    objCommand.CommandText = _
    "SELECT ADsPath, givenName, SN, homedrive, samAccountName,
    telephoneNumber, mail, description, department FROM 'LDAP://dc=domain,
    dc=local' WHERE objectCategory='user'"
    Set objRecordSet = objCommand.Execute

    objRecordSet.MoveFirst
    Do Until objRecordSet.EOF
    strPath = objRecordSet.Fields("ADsPath").Value
    Set objUser = GetObject(strPath)
    objExcel.Cells(i,1) = objUser.samAccountName
    objExcel.Cells(i,2) = objUser.SN
    objExcel.Cells(i,3) = objUser.givenName
    objExcel.Cells(i,4) = objUser.homedrive
    i = i + 1
    objRecordset.MoveNext
    Loop

    objConnection.Close
    ============
    I frankly did not check the first part of the code in the message. My main
    point was that you probably wanted to add the value of the
    objUser.homeDirectory attribute. homeDrive is the drive letter assigned,
    such as "H:", while homeDirectory is the path to the home directory. A less
    important point was that it was not necessary to bind to the user objects,
    since ADO can retrieve all of the values needed. A final minor point is that
    the clause "objectCategory='user'" retrieves both user and contact objects.
    Contact objects do not have homeDrive and homeDirectory attributes, so you
    could get errors if you bind to the object and attempt to retrieve these
    values. The filter clause "objectCategory='person' AND objectClass='user'"
    will only retrieve user objects.

    --
    Richard Mueller
    Microsoft MVP Scripting and ADSI
    Hilltop Lab - http://www.rlmueller.net
     
    Richard Mueller [MVP], Mar 17, 2008
    #11
  12. Spin

    Spin Guest

    I need this to work against only a subset of my domain users, not everyone.

    Spin
     
    Spin, Mar 18, 2008
    #12
  13. Spin

    Spin Guest

    <snipped>

    I created a blank Excel document. I closed it. I dropped this blank
    document onto the VB script of your code from your first post (I did modify
    my dc=domain to dc=my actual domain name). But I got this error:

    ---------------------------
    Windows Script Host
    ---------------------------
    Script: C:\Documents and Settings\Administrator.ALPHA\Desktop\AD
    HomeDrives.vbs
    Line: 28
    Char: 77
    Error: Expected ')'
    Code: 800A03EE
    Source: Microsoft VBScript compilation error
     
    Spin, Mar 18, 2008
    #13
  14. The single line of code I gave you will take you 5 minutes to convert and do
    exactly what you want. Pull the ! and the () that surround it and it will
    give you all users with home directories. Then just place the attributes
    you want reported at the end and redirect the output and you are done. I
    think you are working way to hard for a simple answer.
     
    Paul Bergson [MVP-DS], Mar 19, 2008
    #14
  15. Spin

    Spin Guest

    "I think you are working way to hard for a simple answer."

    Reminds me of when I was in the Army and the drill sergeants used to say
    "Private, you don't gotta make the job harder than it really is." It
    always used to be so funny the way they said it, in all seriousness but a
    humorous subtext between the lines. Paul, I really need to go to a
    scripting class!
     
    Spin, Mar 19, 2008
    #15
  16. Spin

    lforbes Guest

    Where does it put it though? Does it dump it to Excel or text or does it just
    echo it on the screen? I get pretty annoyed with the MS Scripting Guys pages
    because all their scripts have wscript echo in them to list the data like
    that is something actually worthwhile. Yes, I want to click OK 2500 times
    and still have no data at the end.

    I have had to convert all the scripts to Excel. It may not be pretty but the
    script will pull ANY user attributes into an Excel Sheet and then using the
    username will also put any info from Excel back into Active Directory. You
    can modify it for any OU but simply adding the OU into the Domain properties
    LDAP. It came in handy when I had to input email address for 2500 users.
     
    lforbes, Mar 20, 2008
    #16
  17. Spin

    lforbes Guest

    Hi,

    The error is pretty self explanatory. Count from the top 28 lines and then
    see if you are missing a ")"

    For the OU just specify in the LDAP domain section. OU=MyOU, DC=Domain,
    DC=local

    Cheers,
    Lara
     
    lforbes, Mar 20, 2008
    #17
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.