Results 1 to 4 of 4

Thread: Export only Distribution List Members from Active Directory to Excel

  1. #1
    Join Date
    Jan 2006
    Posts
    181

    Export only Distribution List Members from Active Directory to Excel

    I want to export global distribution list members and not security groups, from an Active Directory/Exchange Group to an excel spreadsheet but it is not working and I dont have too much knowldge on scripting. I did try to run a query in AD but it strted to give me only the list of groups without the members. The distribution groups are in several sub OUs. Can anyone please help me out? Thanks.

  2. #2
    Join Date
    Jun 2006
    Posts
    623
    If you want to query for all distributiong groups then the filter would be like below:

    ((objectCategory=group)(!groupType:1.2.840.113556.1.4.803:=2147483648))

    You may have to retrieve the member attribute, which is multi-valued and also the DN or sAMAccountName of the group. For instance, a VBScript program using ADO could be similar to below:

    Option Explicit

    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName
    Dim arrMembers, strMember

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory domain.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"

    ' Filter on distribution groups.
    strFilter =
    "((objectCategory=group)(!groupType:1.2.840.113556.1.4.803:=2147483648))"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "distinguishedName,member"

    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute

    ' Enumerate the resulting recordset.
    Do Until adoRecordset.EOF
    ' Retrieve values and display.
    strName = adoRecordset.Fields("distinguishedName").Value
    arrMembers = adoRecordset.Fields("member").Value
    Wscript.Echo "Distribution Group: " & strName
    If IsNull(arrMembers) Then
    Wscript.Echo "-- <No Members>"
    Else
    For Each strMember In arrMembers
    Wscript.Echo "-- " & strMember
    Next
    End If
    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
    Loop

    ' Clean up.
    adoRecordset.Close
    adoConnection.Close

  3. #3
    Join Date
    Aug 2006
    Posts
    84
    You can also use the ADFind from joeware.net for this purpose and many others by running:

    adfind -b "fully_distinguished_name_of_the_group" member

  4. #4
    Join Date
    Sep 2005
    Posts
    193
    Check the below script but I dont know how to make it output the results in a CSV file:

    Set oRootDSE = GetObject("LDAP://RootDSE")
    strBase = "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;"
    strFilter = "(&(objectclass=group)(sAMAccountType=268435457));"
    strAttrs = "distinguishedName;"
    strScope = "subtree"

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Provider = "ADsDSOObject"
    objConn.Open "Active Directory Provider"
    Set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)

    objRS.MoveFirst
    Dim objGroup
    While Not objRS.EOF
    Set objGroup = GetObject("LDAP://" & objRS.Fields(0).Value)
    WScript.Echo
    "-------------------------------------------------------"
    WScript.Echo "Connected to group DN : " &
    objGroup.distinguishedName
    WScript.Echo "Group Name : " & objGroup.sAMAccountName
    WScript.Echo "Members...."
    groupMembers = objGroup.member
    If IsEmpty(groupMembers) Then
    'No groups.
    ElseIf (TypeName(groupMembers) = "String") Then
    Set objMember = GetObject("LDAP://" & groupMembers)
    WScript.Echo "...." & objMember.sAMAccountName
    Else
    For Each strGroup In groupMembers
    Set objMember = GetObject("LDAP://" & strGroup)
    WScript.Echo "...." & objMember.sAMAccountName
    Next
    End If
    WScript.Echo
    "-------------------------------------------------------"
    WScript.Echo ""
    objRS.MoveNext
    Wend

Similar Threads

  1. Export users in a security/distribution list to txt file?
    By Billie in forum Active Directory
    Replies: 3
    Last Post: 26-09-2008, 02:30 AM
  2. export AD Groups and Members in to Excel.
    By Lion in forum Windows Server Help
    Replies: 3
    Last Post: 27-05-2008, 12:09 PM
  3. Distribution Groups - export lists and members
    By Isaivalan in forum Active Directory
    Replies: 2
    Last Post: 24-07-2007, 11:00 AM
  4. Replies: 1
    Last Post: 18-05-2006, 01:01 AM
  5. Distribution List Members Export
    By BuvDeep in forum Active Directory
    Replies: 3
    Last Post: 02-05-2006, 07:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,638,381,917.11231 seconds with 18 queries