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

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

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
siber1
Asked:
siber1
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
AmitIT ArchitectCommented:
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
 
AmitIT ArchitectCommented:
If you don't find DSA.MSC in Exchange. Login to DC and do all below steps mentioned above.
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
AmitIT ArchitectCommented:
You can play with two tools at http://joeware.net/freetools/

ADFIND and MEMBER Of
0
 
siber1Author Commented:
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
 
AmitIT ArchitectCommented:
You need to buy a tool Hyena.

http://www.systemtools.com/hyena/ad_main.htm
0
 
connectexCommented:
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
 
siber1Author Commented:
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
 
connectexCommented:
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
 
siber1Author Commented:
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
 
prashanthdCommented:
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
 
prashanthdCommented:
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
 
siber1Author Commented:
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
 
connectexCommented:
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
 
siber1Author Commented:
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
 
connectexCommented:
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
 
siber1Author Commented:
appreciate all the assistance. this will work fine.
0

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

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