A few weeks ago I spruiked the many reasons why IT must keep its company address book in top shape, as a single source of truth for many purposes. Today I'll bring the how, with several automated tools to do the job.

In a previous instalment of The Wired CIO I expressed my dismay at the state of many a global address list - or GAL - around the world where position titles, contact details and other fields are missing or wrong.

Having an up-to-date and fully populated address book means company staff can look up contact information from their mail client as well as smartphones and tablets out on the field. It means the printed company phone directory can be generated without lengthy administrative time spent on maintaining an always-out-of-date spreadsheet or document.

It means e-mail signature can be constructed for users upon login and with no manual intervention. This enforces consistency and accuracy - and even makes the whole system self-policing. If the GAL drives e-mail signatures, staff are motivated to advise of changes and updates because it affects them directly.

I spoke of why this was so important but left the "how" for today. Clearly, this is not a moment too soon because some reactions to the previous story give me concern for the state of systems administration in various sectors.

Specifically, on certain external forums so-called sysadmins complained, "oh great, so we're supposed to do data entry, hey?" Ironically, this is the very type of IT worker I criticised, who blissfully remains unaware of business problems and the opportunities IT has to transform the organisation's culture.

At the risk of arrogance, to my mind a reaction such as that reflects not only unwillingness to tackle problems but also in fact shallowness about how to perform their own job.

After all, let's face it - if you are a technical person, but when faced with the task of updating a body of data can only see one option, namely manually keying in the information, then something is wrong.



Nevertheless, I'm here to help. Here are automated tools you need to know. One caveat: these are geared towards Microsoft Windows Server. Linux admins can certainly follow along and will be able to find equivalents online.

Two tools that any Windows admin needs under their belt are command-line utilities, csvde.exe and ldifde.exe. These perform similar, yet different, functions on Active Directory (AD).

The first, CSVDE - which means Comma Separated Value Data Exchange - will export and import AD data and works with - as you may guess - csv text files. Such files are easily edited using Microsoft Excel, can be viewed with Notepad and are ripe for processing through various text tools.

Executing the command csvde -f out.csv will give a complete dump of your AD structure, with the first line (row) containing field names. While there are a great many fields this can be viewed within Excel.

Make note of the field names because you can filter output to only include specific fields and also only rows which match a criteria.

As an example, the following command might be useful within a scheduled task to generate a nightly dump of staff phone numbers and e-mail addresses which are then formatted to make an accurate, up-to-date phone directory on the corporate Intranet.

csvde -f out.csv -r "(objectCategory=person)" -m -l cn,title,mail,telephoneNumber,ipPhone,mobile,department

As well as exporting, csvde can import via the -i switch. A lot of care must be taken with this but you can appreciate the power exists to bulk-create new accounts or contacts with some simple manipulation of Excel and careful matching of columns to AD fields.

As I previously stated, I believe in a fully-populated GAL with position titles, office addresses, phone and mobile details and others. Consequently, your company's phone directory can be generated with little effort via csvde and some minor formatting. There is no need for multiple people throughout the organisation to be manually maintaining out-of-date listings.

In a similar vein, ldifde.exe - the LDAP Data Interchange executable - performs batch operations against AD. Like csvde.exe, this application will export and import AD information but using an open standard file format defined for LDAP directories in general, rather than the generic CSV format. In this case, the data is less easily edited using apps which do not understand that file format but is transportable among different operating systems so long as they adhere to LDAP and LDIF specifications.

With a fully-populated GAL your users can enjoy having their e-mail signatures automatically generated when they log in. Not only does that benefit them, but it also makes the whole system self-policing. Users are more likely to notify changes in their details when it affects them so visibly. This creates a cycle whereby using the GAL to generate information leads to it proactively being kept accurate.

The program I've been using for five years is Symprex Email Signature Manager but other options will exist and will follow similar principles.

Using this program you can create template e-mail signatures that contain logos, contact information, disclaimers and other elements. Nicely, you may create conditional aspects of your signature. For example, you may only show the text "Mobile: " for users where the mobile phone number is actually defined in their AD record.

I advocate creating security groups within AD for each type of signature required within your business. Next, allocate users to these security groups. Lastly, deploy signatures based on security groups.

When you create new logins ensure their contact details are entered in AD, and that they are assigned to an appropriate signature security group. Provided you are invoking this program on user login your users will find their e-mail signatures just appear as if by magic. There is no wasteful lost time or inconsistent branding or other problems that all arise when staff need to handle their own signatures.

Bulk AD editing in Excel

These have been good tools already, but you might still not be satisfied. A simple program by WiseSoft may come in useful. Their application Bulk AD Users allows you - or, more appropriately, Human Resources - to edit user account in Microsoft Excel.

This program will allow AD records to be pulled in that match defined criteria, then be edited freely within Excel, then loaded back into AD. Of course, Excel offers search and replace, formulas to concatenate or look up data from elsewhere.

As an alternative to commercial products you may prefer to simply code your own routines. This offers maximum flexibility and can pipeline into, or from, actions on your Intranet or other facilities.

Helpful code snippets online can point you in the right direction with maintaining AD via scripting languages - for instance, updating user attributes from CSV and updating Active Directory. In fact, when combined with csvde and ldifde such scripting really becomes straightforward text processing. An example of putting it all together is provided on Microsoft Technet.

So, that's that. There are no excuses left why your company's address book can't be up to date.

Be sure to come back for more of The Wired CIO.