Export only Distribution List Members from Active Directory to Excel

[Donald]

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.

[Gusgr8]

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

[Soul Searching]

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

[anshusehgal]

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