“Using CSVDE – A real world example” is the second of five follow up posts, from my original post “Top 5 tools an Exchange Administrator should not be without!”  in this post as promised I will give you a real world example on the use of the tool.

CSVDE is primarily used for the bulk export and import of Active Directory objects. As you would have properly guessed CSVDE exports and imports objects into and out via a CSV file. A benefit of using CSVDE is that exports can be opened in a spreadsheet such as Excel for manipulation.

You may have noted that CSVDE has a cousin, called LDIFDE, unlike CSVDE, LDIFDE  is primarily used to modify or delete Active Directory Objects, the other main difference is that LDIFDE uses the LDIF file format for the export and import of objects. Both CSVDE and LDIFDE ship with Windows Server 2003.

Before I begin my example I have copied from the command line a subset of CSVDE help, (CSVDE /?)

General Parameters
-i                Turn on Import Mode (The default is Export)
-f filename       Input or Output filename
-s servername    The server to bind to (Default to DC of computer's domain)
-v                Turn on Verbose Mode
-c FromDN ToDN   Replace occurences of FromDN to ToDN
-j path           Log File Location
-t port           Port Number (default = 389)
-u                Use Unicode format
-?                Help

Export Specific
-d RootDN        The root of the LDAP search (Default to Naming Context)
-r Filter         LDAP search filter (Default to "(objectClass=*)")
-p    SearchScope  Search Scope (Base/OneLevel/Subtree)
-l list           List of attributes (comma separated) to look for in an LDAP search
-o list           List of attributes (comma separated) to omit from input.
-g                Disable Paged Search.
-m                Enable the SAM logic on export.
-n                Do not export binary values

Import

-k                The import will go on ignoring 'Constraint Violation' and 'Object Already Exists' errors
 
Authentication

-a UserDN [Password | *]              Simple authentication
-b UserName Domain [Password | *]    SSPI bind method

Note: When performing operations with CSVDE, and you do not use the above authentication parameters, operations will be performed with the credentials of the account currently logged in using the SSPI bind method.

Well on to business, today I will show you how to export a CSV file/list of distribution groups along with each group’s members.

To perform this query we need to create an LDAP query so that instead of retrieving all objects from Active Directory we only retrieve the groups we are interested in. I can tell you we will need to search for objects with an objectCategory of group and an objectClass of group. You might then say there are two different types of groups, Security, and Distribution. but I only want a list of distribution groups. To do this you need to know the groupType attribute, now depending on the scope of the group (Domain Local, Global & Universal), Table 1 Below lists the groupType attributes for both Distribution and Security groups.

Note: If you include the query based distribution group there is actually three types of groups.

Below is the LDAP filter you will to export a list of Distribution Groups and their members for distribution groups with a Domain Local, Global or Universal Group Scope.

(&(objectCategory=Group)(objectClass=Group)(|(groupType=8)(groupType=4)(groupType=2)))

If you wanted to you could also include Query Based distribution groups, but this would not include members of the Query Based Distribution Groups (QBDG), this is because of the way this type of group works, it does not contain a multi valued member attribute of members, instead it uses an LDAP query to determine its members. The LDAP filter listed below includes QBDG

  (|(&(objectCategory=Group)(objectClass=Group)(|(groupType=8)(groupType=4)(groupType=2)))(objectCategory=ms-Exch-Dynamic-Distribution-List)(objectClass=msExchDynamicDistributionList))

Now to string the command together,

Go to Start | Run | Type cmd press Enter

In the cmd window type or copy/paste the following command and press enter:
 
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

You will now find a csv file in c:\temp named DistributionLists.csv which contains the list of exported group and members.

If you are not logged onto your server with an account that has rights to the domain from which you want to export distribution lists from you will need to use the –b parameter.

If you need to run this command against a specific server you should specify the –s parameter.

Figure 1 below is what you should see if the command has ran successfully.

Figure 1 “CSVDE.exe has successfully ran”

From here you can open the csv file in excel so that the data can formatted in an a more easily viewable format