Here’s a way to automate the download of data from Active Directory (AD), specifically group members, into ACL using adfind and the ACL Execute command.
I’ll walk you through it step-by-step.
This was posted before ACL released their own Active Directory driver, which I still haven’t figured out.
Even if you don’t use ACL, you might gain a better understanding of AD and LDAP in general….
Since this is a long post, I’ve divided it up as follows:
- Project Description – what I’m trying to do and why I automated my downloads.
- adfind – the tool used to download the members of Active Directory groups (and more!).
- Active Directory (AD) Primer – a little background regarding AD.
- Using adfind in the Command Prompt – describes the queries used for the downloads.
- Automating the Download – how to use the ACL Execute command to run adfind.
- Troubleshooting the EXECUTE command.
- Other notes.
1. Project Description
Before I describe how I did it, let me provide a little context as to why I wanted to automate this.
I have a shared folder that is secured by 2 Active Directory (AD) groups that consist of 2 types of users: the first type always need access to the folder, and the second type needs access for a limited period of time (while working on a specific project). I have an approved list of both types of users.
Both types of users are assigned to different AD groups, and those groups are assigned to the shared folder. Once a month, I obtain a list of IDs currently in those 2 AD groups, and compare it to those users on the approved lists. Then I follow up any exceptions with the owner of the folder to determine whether those users (type 2 temporary users) still need their access. If not, I remove those users from the type 2 group. Type 1 users are removed only if they leave the department or company.
This involves a lot of manual steps that I didn’t want to do every month, so I scripted the entire project.
I needed to find a command-line tool that I could use to query Active Directory. I came across a tool called adfind that was mentioned by Microsoft Technet (scroll down near the bottom of the page to the heading titled, Some Third-Party Gems). That article pointed me to joeware.net/freetools/adfind (scroll down near the middle of the page to the Download Now button).
The tool downloads as adfind.zip, which contains 2 files, adcsv.pl and adfind.exe. The former file is a perl script that automatically converts the adfind output to comma separated value (CSV) format, while the latter is the tool itself. I suggest you create a c:\adfind directory, and unzip the tool to that directory.
Once you extract the two files to a directory, you are ready to go; no installation is necessary. And since this is a command-line tool, it only works in a command prompt. You cannot use the tool by double-clicking the adfind.exe file–if you do, a command prompt will open and quickly close.
To use the tool, open a command prompt and navigate to the directory in which you saved the tool–in my case, as noted above, the directory is c:\adfind.
I like to save command-line tools in a folder close to the root of C:\ and don’t recommend burying it real deep (like c:\Program Files\tools\free\ adfind, which is 4 directories down from the root of C:\). Many tools, including ACL, won’t work right if the path consists of too many directory levels or characters.
adfind, like many other AD tools, does not require administrator credentials to run, as most domains are configured to allow anyone who can log into the domain to access most domain data such as users, groups, or other AD objects. If your domain is more secured (this is rare), the process I describe in this post will not work for you unless the account you run ACL under has the proper permissions.
In this post, I’m going to show you how to download the members of an AD group, but adfind can used to download just about anything in AD. To access help with adfind, open Command Prompt, change to the directory containing adfind, and type adfind (see below).
Before we discuss using adfind in a command prompt, you need to first understand some details about how data is stored and referenced in Active Directory.
3. Active Directory (AD) Primer
Before you can create a query with adfind, you need to know how to find what you’re looking for. For the purposes of this post, the AD object we are focusing on will be an AD group.
In AD (or any LDAP directory), objects are referred to by their Distinguished Name (DN). The parts of a distinguished name, delimited by commas, uniquely identify the object and where in the AD tree the object exists. The parts of a DN are as follows (color-coded to help identify the same parts in the itauditsecurity domain graphic and the examples further below):
The domain is the top-level container under which all the other AD objects exist. OUs are also containers into which you can put other OUs or CNs. CNs are objects, which can be a user, a group, or a hardware component.
It might help to compare the AD hierarchy to a computer, folders, and files. All the OUs (folders) and CNs (files) exist under a single domain (computer). Just as a folder can contain multiple folders or files, OUs can contain other OUs or CNs. And as every file has to exist in a folder somewhere, every CN has to be in an OU. And just as a file may be buried under several layers of folders, CNs can be buried under several layers of OUs.
An example of an AD domain is shown at this link for the free Softerra LDAP Browser. I use this tool all the time and highly recommend it.
Also, keep the following in mind:
- As an example, assume the domain is organized as shown at right (the text colors correspond to the DN parts graphic immediately above).
- To identify an object (user, group, hardware component), you have to list all the components that make up that object, but you list it in reverse order. For example, the DN of the GRC group (highlighted in yellow at right) is cn=GRC,ou=Audit,ou=Security,ou=Headquarters,DC= itauditsecurity,DC=com.
- Another example: the DN of the Intranet group is cn=Intranet,ou=Sharepoint,ou=Security, ou=Headquarters,DC=itauditsecurity,DC=com.
- CNs, OUs, and DNs are separated by commas, but without any spaces between them.
- Domains with multiple components have multiple DCs, which have to be listed separately. Since my domain has 2 components (itauditsecurity and com), you have to list them separately as shown in the bullets above. If the domain was blog.auditmonkey.com, the domain components would have to be listed as DC=blog,DC=auditmonkey,DC=com.
- To query AD, you have to specify all the parts of the DN that define the object you want to download. In this case, I want to download the members of the AD group called GRC.
4. Using adfind in the Command Prompt
Some of you may not use ACL and just want to know how to run adfind; that’s okay, and if so, the queries below should help.
For those of you who want to automate adfind downloads, it will be helpful to just show you some adfind examples before discussing how to automate it with ACL. Also, you always want to ensure that your adfind command is working and providing the data you want BEFORE you try launching it inside ACL.
Assume the following:
- You know how to run commands in the Command Prompt (formerly known as disk operating system (DOS)). To open the Command Prompt, click the Start button, and type cmd (for command) in the Search/Run box, and press Enter
- You want to download the members of the GRC group, and the group’s DN is cn=GRC,ou=Audit,ou=Security,ou=Headquarters,DC=itauditsecurity,
- You created a c:\adfind directory on your computer and extracted the 2 files in adfind.zip to that directory
- You want to save the results of the query in a file called GRCusers.csv
The adfind query you would use is listed below, but please note:
– I bolded some text to make it easier to distinguish between different parts of the query.
– Although the query is shown wrapped, it has to be one continuous line, not two lines.
– No spaces between CNs, OUs, and DCs.
c:\adfind>adfind -b cn=GRC,ou=Audit,ou=Security,ou=Headquarters,DC=itauditsecurity,DC=com cn member >> "GRCusers.csv"
Here’s an explanation of each part of the query:
This is the command prompt and the directory that the command prompt is currently in; it should be the same as the directory in which you saved the adfind.exe.
Command that starts the adfind tool.
adfind switch the indicates you are searching for a distinguished name (DN).
The distinguished name you are searching for, consisting of a CN, one or more OUs, and the DCs.
The objects in the DN that you want saved in a file. If the object is an AD group as it is in this case, cn is the AD group name (GRC), and member is the user IDs that are members of that group. You can add other objects to the query, but you must separate each of the objects by a space.
The output redirect symbol (>>) that indicates the results should not be shown on the screen, but saved to a file. The file (in this case, GRCusers.csv) is saved in the same folder that the command prompt is in (in this case, c:\adfind). The file name must be enclosed in double quotes.
The output file, GRCusers.csv, would look like this:
Using server: server1.itauditsecurity.com:389
Directory: Windows Server 2008 R2
>description: GRC users with write access
>member: CN=JON316,OU=DIV01,OU=DEPT200,OU=Personal ID,DC=itauditsecurity,DC=com
>member: CN=ROM323,OU=DIV02,OU=DEPT201,OU=Personal ID,DC=itauditsecurity,DC=com
>member: CN=ROM623,OU=DIV03,OU=DEPT205,OU=Personal ID,DC=itauditsecurity,DC=com
1 Objects returned
Regarding the output file:
- The server that answered the query is server1 on the itauditsecurity.com domain.
- Directory is the server’s operating system.
- dn is the fully distinguished name of the AD object queried, consisting of the user ID’s CN, OUs, and DCs. The CN and OUs correspond to each individual ID, which are different.
- description is the text entered into AD when the group was created.
- The users belonging to the GRC group are listed in the lines that begin with “>member”, such as JON316, ROM323, and ROM623.
- whenChanged is the last time the members or the group description changed.
Likewise, if I wanted to download the members of the Intranet group (from the domain hierarchy graphic above) to a file called Intranetusers.csv, I’d use this query:
c:\adfind>adfind -b cn=Intranet,ou=Sharepoint,ou=Security,ou=Headquarters,DC=itauditsecurity,DC=com cn member >> "Intranetusers.csv"
When using adfind in a command prompt, keep these in mind:
- Make sure you change to the directory in which adfind resides before executing it. In my case, when I open a command prompt, I have to change the directory to c:\adfind.
- Change the CN to the name of the group you are targeting, and change the OUs and DCs accordingly.
- CNs, OUs, and DCs are not case sensitive, but the syntax has to be correct.
- If you don’t know the CN, OUs, or DCs, talk to your server/Active Directory support person or use the free LDAP Browser to view your domain.
Many other query examples for adfind are listed in a Microsoft Technet article.
5. Automating the Download with ACL’s Execute command
ACL Execute Command Overview
The EXECUTE command allows you to start and run Windows and other applications or processes from within ACL. Applications do not run inside ACL, but ACL can launch the applications and interact with the results. If you can run the application on your computer, you can usually run it from ACL using the EXECUTE command.
EXECUTE allows you to do things like copy, move, delete, or encrypt files on your laptop, server, or network drive using the Command Prompt. It can also run other applications; I’ve even seen it used to call users on their mobile phones and display in an ACL table whether they answered the call.
To use the EXECUTE command, you type EXECUTE and then the Windows command (application command) into an ACL script. When using EXECUTE to run Command Prompt commands, you have to enclose the command prompt command in single or double quotes (I use single quotes–see the red ones below).
Running adfind via Execute
To run the same adfind command listed above in step #4 in ACL, use this command (although shown wrapped, it has to be one continuous line):
EXECUTE 'cmd /c c:\adfind>adfind -b cn=GRC,ou=Audit,ou=Security,ou=Headquarters,DC=itauditsecurity,DC=com cn member >> "GRCusers.csv"'
Here’s an explanation of each part of the query:
The ACL EXECUTE command.
<Red single quotes>
Notice that the remainder of the command text is enclosed in red single quotes. The first one is before “cmd” and the second one is at the very end, after “GRCusers.csv”
The command that starts the Command Prompt.
This switch closes the Command Prompt after the EXECUTE command is finished running.
The same command shown in step #4 above, shown in teal text.
So now you know how to use the EXECUTE command with the adfind command to download group members from AD. Of course, you still need to script the importing of the file, cleaning up the data, and the analysis you want to do on the data. For importing data, see Create a File Import Script.
6. Troubleshooting the EXECUTE command
What do you do when the adfind command runs well in the Command Prompt, but when you try to run it via EXECUTE, it doesn’t work?
First, the most common error is syntax. Make sure you are using the correct adfind switches and have the AD parameters correct (the name of the group and the OUs and DCs). Then ensure you are using the correct syntax for the EXECUTE command–see ACL Help for this command.
Another problem is that the download appears to work, but the resulting file has no data in it. That usually means that the command you used does not correctly specify the path where the adfind.exe is located. In other words, the tool resides in c:\adfind but your command references c:\adffind. Correcting the directory path usually fixes this problem.
Also, when you run the EXECUTE command, an application return code is entered in the ACL log. If the command is successful, the code is 0 (zero), as shown below; if unsuccessful, the code is 1.
19:53:02 - 04/17/2015 EXECUTE 'cmd /c c:\adfind>adfind -b cn=GRC,ou=Audit,ou=Security,ou=Headquarters,DC=itauditsecurity,DC=com cn member >> "GRCusers.csv"' Application return code : 0
7. Other Notes
Other free AD tools exist that can be used in a similar manner, like Microsoft’s ldifde.exe or ADSI Edit (adsiedit.msc), but I found these tools harder to obtain (they come with server operating systems) and use. ACL also has a script in ScriptHub (requires ACL license and support) called Domain_User_Listing_CSVDE that downloads all domain users, but I haven’t tried it. I found adfind much easier to obtain and use.
Let me know if you have any questions.