Creating a users directory in FileMaker from Active Directory

Last update: 11/12/2013 1:03:34 PM

Introduction

In this quick tutorial, I will show you one method to take an Active Directory users list, export it out and create a directory that can be browsed through for the entire organization or enterprise that the network covers. To do this, we'll use a bit of PowerShell to export the data, and then FileMaker Pro to create the directory. The reason for FileMaker is that this is an application that makes it easy to handle data in a local database and present it in a convenient layout. It also has powerful scripting and exporting facilities that you can use to improve your directory.

In order to follow along, you will need an Active Directory server. It could be on a Windows Server 2008, 2012, or any similar release. You will also need FileMaker Pro in order to create the directory. You can get a free trial at http://www.filemaker.com/. You don't need to have any coding experience in order to accomplish the various steps we will cover. If you would like to get more familiar with PowerShell, feel free to check out my PowerShell tutorial.

Exporting data

Before we can start building the directory, we will need to export data from your Active Directory Domain Controller. Log into your Windows Server where your users are, and start a PowerShell session. You can do that by clicking on Start and entering powershell in the Search bar.

Once you're in PowerShell, you will first need to import the Active Directory module:

<powershell>
Import-Module -name ActiveDirectory

Now, we need to get a list of users with the Get-ADUser cmdlet, and export them to CSV with Export-Csv. Before doing so however, you need to determine which users you want to export. Usually, your users are typically all within a central OU or domain. In this example, we will export all of the users of the Sith Empire in my Domain Controller:

<powershell>
Get-ADUser -filter * -searchbase "OU=Empire,DC=SithEmpire,DC=local" -properties * | Export-Csv users.csv -notypeinformation

As you can see, here we export a single OU called Empire from the SithEmpire.local domain. Replace this with your own setup. We use a star for the filter since we want everything, but you may replace it with a specific query, such as 'ObjectClass -eq "user"' to restrict the listing to users only.

The Export-Csv command simply specifies the file name to export, and also tells PowerShell not to add type information because that uses an extra line in the file which other applications won't be able to deal with. If you want to make sure that your information was exported correctly before you go ahead and work on the import, you can use the following command:

<powershell>
Import-Csv users.csv | Format-Table

This will tell PowerShell to show you a table of the file's content. Now that all of our user information has been exported, it's time to build the directory.

Creating the form

Open up FileMaker Pro and start a new database from a starter solution. You can create the form yourself by starting a new blank file, but you will need to manually create fields and map out the layout. FileMaker comes with a predefined solution called Personnel Records that we will use.

Once you make your file, you should see the template on your screen, along with the Field Picker. We won't cover customization too much, but you can add or remove fields, change the layout, and more. As you will see in the next section, the fields in Active Directory are not exactly the same as the ones in the FileMaker template. Your own organization may also not be using all these fields.

You can use the Field Picker to add or remove fields as you need. For the layout, you can click on the Edit Layout button at the top right of the toolbar to edit it. Here you can drag fields from the Field Picker onto your form, then use the Inspector in order to change things like text color, font size, and more.

For now, let's work on importing the data.

Importing data

If you go to File and then Import Records, you can select the CSV file you made in the previous section. This will give you the import window where you will map Active Directory names onto the field names of your directory. This is where you need to carefully map entries so that the values go to the right fields.

This is how I mapped them, from a default Active Directory schema to the default FileMaker template:

You can drag the fields up or down to configure your mapping. Once done, make sure you put a check mark near Don't import first record then click the Import button. This should fill your database up, and each user should now have a record inside of your directory.

You can now browse through the records using the album icon on the left of the toolbar, and search for users using the search functions. Here is an example of what a user looks like:

Conclusion

From here, you can edit any user you want, add entries, profile photos, and so on from within FileMaker. If you share the database file, others can also view the directory. With a FileMaker Server, you could also use this platform to publish your directory to a web page, for example as an intranet directory in your organization.

From the File menu, you can also do things like exporting to PDF or HTML files, or even publish on a network. From here, you may want to run your PowerShell script with the Task Scheduler and import users on a regular basis to keep the directory up to date, and if you feel adventurous, you could even write a script in FileMaker to export that data back into Active Directory using the Import-Csv cmdlet. Possibilities are endless!



© 2008-2017 Patrick Lambert - All resources on this site are provided under the MIT License - You can contact me at: dendory@live.ca