Hack 30 Enumerate Group Membership to a CSV File

figs/moderate.gif figs/hack30.gif

Export a list of which users are in which groups to a comma-separated file that is suitable for opening in your favorite spreadsheet or database application.

Finding out which users belong to which groups is not a trivial task from the GUI. Using Active Directory Users and Computers (ADUC), you can view the Member Of tab of a user's properties sheet to see which groups the user belongs to but not which users belong to which group. The properties sheet of a group is more informative and has two tabs: Members, which shows which users belong to the group, and Member Of, which tells you if the group itself belongs to any other groups. Opening these properties sheets is a time-consuming process and doesn't always give you quick insight into users and the groups to which they belong.

But if you need a quick way of knowing what the members of different groups are, you can use VBScript. The script in this hack enumerates the groups in an Active Directory domain and places the information in a CSV file. The name of each group, the description of the group, the group's members (both full name and SAM account name), and whether that member is a user or group will all be placed into a CSV file called GroupMembers.csv, located in the directory in which the script is running. This script uses LDAP to query Active Directory. It won't run against an NT4 domain, although you should be able to run it from an NT4 workstation. If you are not running Windows 2000 Professional or later, this script requires ADSI 2.5.

The Code

To use this script, type it into Notepad (with Word Wrap disabled) and save it with a .vbs extension as GroupMembers.vbs:

On Error Resume Next

Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")

Set oFile = FileSystem.CreateTextFile("GroupMemebrs.csv", True)


strDC = "DC01GA.My.Domain.com" 'Substitute your AD domain server name

strRoot = "My.Domain.Com" 'Substitute your company/domain name

strDomain = "DC=MY,DC=DOMAIN,DC=COM"

Set DomainObj = GetObject("LDAP://" & strDC&"/CN=Users," & strDomain)

if Err.Number <0 then

wscript.echo "Failed to connect to " & strADName


end if

DomainObj.Filter = Array("group")

For Each GroupObj In DomainObj

If GroupObj.Class = "group" Then

oFile.WriteLine ("Group Membership for: " & MID(GroupObj.Name & "," 

& _ "Description - " & GroupObj.Description,4))

wscript.echo ("Group Membership for: " & MID(GroupObj.Name & vbTab & _


' "Description - " & GroupObj.Description,4))

set memberlist=GroupObj.Members

For Each member In memberlist

oFile.WriteLine MID(member.Name & "," & member.SAMAccountName & "," & _ member.Class,4)

wscript.echo MID(Vbtab & member.Name & " (" & member.Class & ")",5)


end if


set DomainObj = Nothing

set GroupObj = Nothing

if err.number<>0 then

wscript.echo CRLF

wscript.echo ("ERROR: "&err.number&" "&err.description & " from "&err.source)

wscript.echo CRLF

end if

Wscript.Echo "Done!!"


Running the Hack

Before you run the script, modify these three lines near the beginning:

strDC = "DC01GA.My.Domain.com" 'Substitute your AD domain server name

strRoot = "My.Domain.Com" 'Substitute your company/domain name

strDomain = "DC=MY,DC=DOMAIN,DC=COM"

For example, to query a domain controller named srv210.mtit.com in the mtit.com domain, change these lines to:

strDC = "srv210.mtit.com" 'Substitute your AD domain server name

strRoot = "mtit.com" 'Substitute your company/domain name

strDomain = "DC=MTIT,DC=COM"

Also note that the script lists only groups located in the Users container. To query other containers or organizational units, modify the following line accordingly:

Set DomainObj = GetObject("LDAP://" & strDC&"/CN=Users," & strDomain)

To run the hack, simply create a shortcut to it and double-click on the shortcut.

Figure 3-5 shows a sample of typical output for the script, with the CSV file imported into Excel to make it more readable. You can see that the Domain Admins group has members Bob Smith, Frank Jones, Jane Smith, and the default Administrator account.

Figure 3-5. A portion of sample output from running the GroupMembers.vbs script

?Hans Schefske