• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2797
  • Last Modified:

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
0
chkaiban
Asked:
chkaiban
  • 4
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now