Solved

need a quick way to export all distribution groups and members using script

Posted on 2011-02-18
17
2,941 Views
Last Modified: 2012-06-21
hello, looking for a command that i can run from a domain controller to quickly export out all distribution groups and members to an output file.
this is on a win2003 domain controller, exchange 2003 backend.

thanks in advance.

S.
0
Comment
Question by:siber1
  • 6
  • 5
  • 4
  • +1
17 Comments
 
LVL 42

Expert Comment

by:Amit
ID: 34928186
Login to your Exchange server.
Goto Run>Type dsa.msc hit enter
You will see a Saved Queries Icon
Right Click on that and select New>Query
Give the Name like Mailenable DL
Click on Define Query
Under Find> Select Exchange Recipients and select Mail-Enabled Groups
Uncheck others
Click Ok twice
Click on Action Menu and select Refresh. Right hand side you have the results
Now Goto View and click Add/Remove Column and customise the view more
Right click on the created query and export the results to .csv

Hope this helps
0
 
LVL 42

Expert Comment

by:Amit
ID: 34928193
If you don't find DSA.MSC in Exchange. Login to DC and do all below steps mentioned above.
0
 
LVL 42

Expert Comment

by:Amit
ID: 34928241
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Expert Comment

by:Amit
ID: 34928270
You can play with two tools at http://joeware.net/freetools/

ADFIND and MEMBER Of
0
 

Author Comment

by:siber1
ID: 34928488
Thanks Amit, however, I still need a script that will pull all distribution groups and list all members of each group.
the links you sent refer to exporting the members of a single group, we have over 2000  ; )

thx
0
 
LVL 42

Expert Comment

by:Amit
ID: 34928571
You need to buy a tool Hyena.

http://www.systemtools.com/hyena/ad_main.htm
0
 
LVL 13

Expert Comment

by:connectex
ID: 34931011
Check out this article: http://exchangeis.com/blogs/exchangeis/archive/2005/07/14/35.aspx. The output may not be as clean as you'd like but it will export the information. Also another option is using LDIFDE. This command will export all groups: ldifde -f groups.txt -r "(objectClass=group)" -l name,member. That's all groups in AD but it's format may be more to your liking.
0
 

Author Comment

by:siber1
ID: 34931315
thx. yes i saw that article, but when i run this command that it suggest:
csvde -f c:\temp\DistributionLists.csv -p subtree -l cn,mail,member  -r "(|(&(objectCategory=Group)(objectClass=Group)(|(groupType=8)(groupType=4)(groupType=2)))(objectCategory=ms-Exch-Dynamic-Distribution-List)(objectClass=msExchDynamicDistributionList))" -j c:\temp

i get zero results exported. strange.
the ldifde would be nice if it exported the display name, or even sAMAccountName, rather than the DN of each group member.
0
 
LVL 13

Expert Comment

by:connectex
ID: 34931563
Two things:

1. Did you run it on a DC?

2. It should export the DN. If it didn't if you had the same name in multiple OUs you wouldn't know which one it was.
0
 

Author Comment

by:siber1
ID: 34933671
thx. yes, i did run it on a DC, yet was showing zero results. however the LDAP query that articl provides does work. its only when incorporated into the vbscript that it fails to export any results.
2. sAMAccountName is guaranteed unique, this would be the preferred export value to supply to the regular user. [a DN is not really the format they were looking for]
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 34941764
Hi,

You can try the following vb script
'On Error Resume Next

Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN
Const ADS_SCOPE_SUBTREE = 2

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

' If a Group was found - Retrieve the distinguishedName
Do While Not objRecordSet.EOF 
    strDN = "LDAP://" & objRecordSet.Fields("distinguishedName").Value
    
    Set objGroup = GetObject(strDN)
    objGroup.GetInfo
    gname = objGroup.Get("name")
    arrMemberOf = objGroup.GetEx("member")
    
    WScript.Echo "Group Name - " & objRecordSet.Fields("name").Value
    For Each strMember In arrMemberOf          
        Set objuser = GetObject("LDAP://"& strmember)
        uname=objuser.samaccountname          
        WScript.Echo uname
    Next        
    objRecordSet.MoveNext
Loop

Open in new window

0
 
LVL 12

Assisted Solution

by:prashanthd
prashanthd earned 250 total points
ID: 34941792
Minor correction, test below code
'On Error Resume Next

Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN
Const ADS_SCOPE_SUBTREE = 2

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

' If a Group was found - Retrieve the distinguishedName
Do While Not objRecordSet.EOF 
    strDN = "LDAP://" & objRecordSet.Fields("distinguishedName").Value
    
    Set objGroup = GetObject(strDN)
    objGroup.GetInfo
    gname = objGroup.Get("name")
    arrMemberOf = objGroup.GetEx("member")
    
    WScript.Echo "Group Name - " & ucase(gname)
    For Each strMember In arrMemberOf          
        Set objuser = GetObject("LDAP://"& strmember)
        uname=objuser.samaccountname          
        WScript.Echo uname
    Next        
    objRecordSet.MoveNext
Loop

Open in new window

0
 

Author Comment

by:siber1
ID: 34945446
hi prash, when i run this in our lab i get the following error:
line 35
char: 5
The directory property cannot be found in the cache
8000500D
0
 
LVL 13

Expert Comment

by:connectex
ID: 34946058
This prashanthd's code with a few modifications. First change is uncomment line 1. Second is to only list the distribution groups (types 2, 4, and 8). Third is a slight change to provide a little better output.


On Error Resume Next

Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN
Const ADS_SCOPE_SUBTREE = 2

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

' If a Group was found - Retrieve the distinguishedName
Do While Not objRecordSet.EOF 
    strDN = "LDAP://" & objRecordSet.Fields("distinguishedName").Value
    
    Set objGroup = GetObject(strDN)
    objGroup.GetInfo
		gtype = objGroup.Get("grouptype")
    If (gtype = 2 Or gtype = 4 Or gtype = 8) Then
      gname = objGroup.Get("name")
      arrMemberOf = objGroup.GetEx("member")
      WScript.Echo "Group Name - " & ucase(gname)
      For Each strMember In arrMemberOf          
        Set objuser = GetObject("LDAP://"& strmember)
        uname=objuser.samaccountname          
        WScript.Echo "  " & uname
      Next
      WScript.Echo        
    End If
    objRecordSet.MoveNext
Loop

Open in new window

0
 

Author Comment

by:siber1
ID: 34946233
thanks connectex, this is looking much better. is there a way to modify the script to list what type of group this is? This way we can sort on dist. or security group.
also, is there a way that i can add additional columns? for example, OU that the group resides in etc.
it currently just lists one after the other in a single column, we are trying to format in a more presentable report format. listing all fields on the top column and each group under.
0
 
LVL 13

Accepted Solution

by:
connectex earned 250 total points
ID: 34946569
It's already been modified to only list groups types 2, 4, and 8. Therefore it should be only showing distribution groups. So you shouldn't need to sort them by type. As for adding additional information. Any information from AD get be added. First you must know the value name needed. You can get this easily by running ldifde.exe and looking up the object in question. Then you can add lines like:

gDescription = objGroup.Get("Description")
WScript.Echo "Description - " & gDescription

Note this are basically the same as lines 36 and 37. As for formatting the output that's a hard one. First I'd need to know what information you wanted output and then I'd have to have a layout plan so I could modify the code to do it. With a bit of studying the code, I think you may be able to complete rest yourself.

The code below will also the full location of the group object.
On Error Resume Next

Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN
Const ADS_SCOPE_SUBTREE = 2

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT distinguishedName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

' If a Group was found - Retrieve the distinguishedName
Do While Not objRecordSet.EOF 
    strDN = "LDAP://" & objRecordSet.Fields("distinguishedName").Value
    
    Set objGroup = GetObject(strDN)
    objGroup.GetInfo
		gtype = objGroup.Get("grouptype")
    If (gtype = 2 Or gtype = 4 Or gtype = 8) Then
      gname = objGroup.Get("name")
      WScript.Echo "Group    - " & ucase(gname)
      WScript.Echo "Group DN - " & objRecordSet.Fields("distinguishedName").Value
      arrMemberOf = objGroup.GetEx("member")
      For Each strMember In arrMemberOf          
        Set objuser = GetObject("LDAP://"& strmember)
        uname=objuser.samaccountname          
        WScript.Echo "  " & uname
      Next
      WScript.Echo        
    End If
    objRecordSet.MoveNext
Loop

Open in new window

0
 

Author Closing Comment

by:siber1
ID: 34946827
appreciate all the assistance. this will work fine.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question