Solved

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

Posted on 2011-02-18
17
2,873 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 41

Expert Comment

by:Amit
Comment Utility
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 41

Expert Comment

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

Expert Comment

by:Amit
Comment Utility
0
 
LVL 41

Expert Comment

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

ADFIND and MEMBER Of
0
 

Author Comment

by:siber1
Comment Utility
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 41

Expert Comment

by:Amit
Comment Utility
You need to buy a tool Hyena.

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

Expert Comment

by:connectex
Comment Utility
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
Comment Utility
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
Are your corporate email signatures appalling?

Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

 
LVL 13

Expert Comment

by:connectex
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
appreciate all the assistance. this will work fine.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
In this video we show how to create an Accepted Domain in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Ac…
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now