Export only Distribution List Members from Active Directory to Excel

Anyone have a quick script for exporting global distribution list members (Not security groups) from an Active Directory/Exchange Group to an excel spreadsheet? I have basic knowledge on scripting.

I have tried to run a query in AD but it gave me only the groups without the members

The distribution groups are in several sub OUs

Any help would be much appreciated!

Thanks,
Patrick
chkaibanAsked:
Who is Participating?
 
bluntTonyHead of ICTCommented:
Hi. I've seperated out the section of code which echoes the user/groups that are members of the dist groups (the sub 'echoDetails').

There are three different echo statements. The first displays firstname and last name. The second, fullname. The third, CN. The problem with the first is that first and last name are not valid for groups, so will display blank.

At the moment the code will echo CN, which for users is usually full name.

Let us know if this helps or if you need it modifying...

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://" & Replace(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
		echoDetails(Replace(groupMembers,"/","\/"))
	Else
    	For Each strGroup In groupMembers
    		echoDetails(Replace(strGroup,"/","\/"))
    	Next
	End If
	WScript.Echo "-------------------------------------------------------"
	WScript.Echo ""
	objRS.MoveNext
Wend
 
Sub echoDetails(strMemberDN)
    'USE ONE OF THE THREE ECHO STATEMENTS HERE TO SUIT YOUR NEEDS...
     Set objMember = GetObject("LDAP://" & strMemberDN)
    'WScript.Echo "...." & objMember.givenName & " " & objMember.sn
    'WScript.Echo "...." & objMember.name
    WScript.Echo "...." & objMember.cn
End Sub

Open in new window

0
 
chkaibanAuthor Commented:
Thank you for the reply but these are for specific group under one OU but my issue is that I have more than 100 distribution group and I need to get all the members of each group.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
Mike KlineCommented:
You can run a query using adfind
http://www.joeware.net/freetools/tools/adfind/index.htm
adfind -default -f  samaccounttype=268435457 name member -nodn -csv > c:\Distrolistmembers.csv
Issue there is that the members are spit out as distinguished names.  There is a way to pipe adfind into adfind and have their name spit out but that is for one group.  Not sure about every group.  If I find a way I'll respond.
Thanks
Mike
0
 
bluntTonyHead of ICTCommented:
The below is VBScript which will do the trick. It's a bit quick and dirty, and to be more complete you might want to add error trapping, but the bones are there. Call it via cscript, e.g.
cscript myscript.vbs
It search from your domain root downwards.
This doesn't take into account group nesting, so only returns direct members of each group, be it a user or another group.
Let us know if this is any good...

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

Open in new window

0
 
Shift-3Commented:
For the sake of completeness, here's a batch script.

Run it on a 2003 server or on a workstation with the adminpak installed.


@echo off
setlocal enabledelayedexpansion
 
set report=report.csv
 
echo Group,Member>"%report%"
 
for /F "tokens=*" %%G in ('dsquery group -limit 0 ^| dsget group -dn -secgrp -scope -L') do (
 set line=%%G
 
 if "!line:~0,3!"=="dn:" set group=!line:~4!
 if "!line:~0,6!"=="scope:" set scope=!line:~7!
 
 if "!line!"=="secgrp: no" if "!scope!"=="global" (
  for /F "tokens=*" %%H in ('dsget group "!group!" -members') do echo "!group!",%%H>>"%report%"
 )
)

Open in new window

0
 
chkaibanAuthor Commented:
Shift-3:I  have tried your script it got me results but not all I got 14 out of 127
0
 
chkaibanAuthor Commented:
@ bluntTony: Your script worked perfectly I got the results in a CSV but is it possible to get the full name (First Name, Last Name instead of logon name?
0
 
Shift-3Commented:
If you want to recurse nested groups, change line 15 to this:

for /F "tokens=*" %%H in ('dsget group "!group!" -members -expand') do echo "!group!",%%H>>"%report%"

Open in new window

0
 
chkaibanAuthor Commented:
Thank you bluntTony it worked perfectly, there was some junk entries but no big deal I removed them from the excel sheet.
0
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.

All Courses

From novice to tech pro — start learning today.