Hack 23 Store and Display Contact Information in Active Directory

Using a script and an Access database, you can store detailed contact information in Active Directory and display it as an HTML page.

Would you like to store all your employee contact information in Active Directory and then be able to display that information on an intranet page? Where I work, there are a number of individuals maintaining lists of user information. The telecom person maintains an Excel spreadsheet of employee names, phone numbers, and office locations. The Web person maintains a similar list for the Internet page. There's another list of sorts in a public folder on our Exchange server. I thought there must be a better way to get this information out that doesn't require quite so many people doing similar tasks.

Figure 2-7 shows my solution to this challenge.

Figure 2-7. HTML interface for Access database

I created an Access database named EmployeeInfo.mdb, with fields for the information I'd like to make available. I then created a .vbs script named ExportAdUsers.vbs, which processes Active Directory user accounts that meet a specified criterion and exports the account information to the database. The information in the database is accessible via the Data Access Page shown in Figure 2-7.

While developing this solution, I found that I needed to be able to list information for employees who might not have an Active Directory user account. The database is open, so a designated person can maintain information for such employees.

Each time Active Directory account information is updated, the script should be run again to update the Access database. I added a field to the database that contains a value that differentiates records that were manually entered from records that were created by running the script. To be sure that no duplicates exist in the database, prior to performing each export, the script deletes all records that are indicated as being exported from Active Directory.

The Code

Type the following VBScript into Notepad (with Word Wrap turned off) and save it with a .vbs extension as ExportAdUsers.vbs:

Option Explicit



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' File:     ExportADUsers.vbs

' Updated:  Dec 2003

' Version:  1.0

' Author:   Dan Thomson, myITforum.com columnist

'           I can be contacted at dethomson@hotmail.com

'

' Usage:    This script should be run using cscript.

'           cscript ExportADUsers.vbs

'

' Input:    None

'

' Notes:    This script exports all users whose accounts are not disabled,

'           not expired, or do not have NoExport in their Notes section.

'           There is also a constant "Users2Skip" to which you should add

'           any names which should not be exported.

'

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



On Error Resume Next



' The name of the Access database to use

Const AccessDatabase = "EmployeeInfo.mdb"

' The name of the Access table to use

Const AccessTable    = "tblEmployeeInfo"



' List of users who should NOT be exported

' This list should contain the user's logon name

' Separate each name by a comma

Const Users2Skip     = "Guest"



' Constant for the account being disabled

Const ADS_UF_ACCOUNTDISABLE = 2

' Constant for the search to search subtrees

Const ADS_SCOPE_SUBTREE     = 2



Const adOpenStatic     = 3

Const adLockOptimistic = 3



' General variable declarations

Dim objConnectionDB, objRecordsetDB

Dim objConnectionAD, objCommandAD, objRecordsetAD

Dim dtStart

Dim strSQL

Dim objRootDSE, strDNSDomain

Dim strDN, intUAC, strSam, strDisplayName, strManagerDN, dtExpireDate

Dim blnProcessUser

Dim objUser, objManager



' Get the start time of the script

dtStart = TimeValue(Now( ))



'Create and open ADO connection to the Access database

Set objConnectionDB = CreateObject("ADODB.Connection")

Set objRecordsetDB  = CreateObject("ADODB.Recordset")



' Open the database

objConnectionDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                     "Data Source=" & AccessDatabase & ";"



' Open the recordset

objRecordsetDB.Open AccessTable, objConnectionDB, adOpenStatic, adLockOptimistic



' Define the SQL statement used to clear out previous

' user info which was exported from AD

strSQL = "DELETE FROM " & AccessTable & " WHERE ImportedFromAD = 'True'"



Wscript.Echo "Removing previously exported records from the " & _

    AccessDatabase & " database."

objConnectionDB.Execute strSQL, , 129



' Determine the DNS domain from the RootDSE object.

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain   = objRootDSE.Get("defaultNamingContext")



' Create and open an ADO connection to AD

Set objConnectionAD = CreateObject("ADODB.Connection")

Set objCommandAD    = CreateObject("ADODB.Command")



objConnectionAD.Provider = "ADsDSOObject"

objConnectionAD.Open "Active Directory Provider"



' Set connection properties

With objCommandAD

  .ActiveConnection = objConnectionAD



  ' Use SQL syntax for the query

  ' This retrieves all values named in the SELECT section for

  ' user accounts which do not have the Notes section = NoExport.

  ' The recordset is sorted ascending on the displayName value.

  .CommandText = _

             "Select userAccountControl, distinguishedName," & _

             " sAMAccountname, displayName" & _

             " FROM 'LDAP://" & strDNSDomain & "'" & _

             " WHERE objectCategory = 'person' AND" & _

             " objectClass = 'user' AND info <> 'NoExport'" & _

             " ORDER BY displayName"



  .Properties("Page Size")     = 1000

  .Properties("Timeout")       = 30

  .Properties("Searchscope")   = ADS_SCOPE_SUBTREE

  .Properties("Cache Results") = False

End With



Wscript.Echo "Running the query to find users."

Set objRecordSetAD = objCommandAD.Execute



' Move to the first record in the recordset

objRecordSetAD.MoveFirst



' Loop until we reach the end of the recordset

Do While NOT objRecordsetAD.EOF

  ' Blank out/reset a few variables..just in case.

  strDN  = ""

  intUAC = ""

  strSam = ""

  strDisplayName = ""

  strManagerDN   = ""

  dtExpireDate   = ""

  blnProcessUser = True



  ' Get the userAccountControl value. This lets us, among other things,

  ' determine if the account is disabled.

  intUAC = objRecordsetAD.Fields("userAccountControl")



  ' Process user if account is not disabled.

  If (NOT intUAC AND ADS_UF_ACCOUNTDISABLE) Then



    ' Get the user's logon name

    strSam         = objRecordsetAD.Fields("sAMAccountname")



    ' Determine if the user is included in the list of logon names to skip.

    If Instr(UCase(Users2Skip), UCase(strSam)) Then blnProcessUser = False



    ' Get the user's display name

    strDisplayName = objRecordsetAD.Fields("displayName")



    ' Set boolean value to skip this user if the user's display name is

    ' blank.

    If strDisplayName = "" Then blnProcessUser = False



    ' If our simple checks went ok, we can now process this user.

    If blnProcessUser = True Then



      ' Get the distinguished name of this user

      ' The syntax is something like:

      '           CN=Joe E. Law,OU=Sales,OU=US,DC=mydomain,DC=local

      strDN = objRecordsetAD.Fields("distinguishedName")



      ' Bind to the user object

      Set objUser = GetObject("LDAP://" & strDN & "")



      ' Process the user

      With objUser

        Wscript.Echo "Processing user: " & strDisplayName



        ' Get the user's account expiration date

        dtExpireDate = CDate(.AccountExpirationDate)



        ' Process the user if the user's account expiration date is not

          passed

        If (dtExpireDate = "") OR _

          (dtExpireDate = CDate("01/01/1970")) OR _

          (dtExpireDate >= Date( )) Then



        'Add new record to the Access database

        objRecordsetDB.AddNew

          ' Get user data from AD and populate the new record in the

          ' Access database



          ' You can use the .Get("xxx") or .xxx formats to retrieve the data

          ' All fields on the left MUST exist in the Access table

          objRecordsetDB("FirstName")       = .Get("givenName")

          objRecordsetDB("MiddleName")      = .initials

          objRecordsetDB("LastName")        = .sn

          objRecordsetDB("DisplayName")     = .displayName

          objRecordsetDB("Description")     = .description

          objRecordsetDB("OfficeLocation")  = .physicalDeliveryOfficeName

          objRecordsetDB("WorkPhone")       = .telephoneNumber

          objRecordsetDB("Email")           = .mail

          objRecordsetDB("WebPage")         = .wwwHomePage

          objRecordsetDB("Street")          = .streetAddress

          objRecordsetDB("POBox")           = .postOfficeBox

          objRecordsetDB("City")            = .l

          objRecordsetDB("StateOrProvince") = .st

          objRecordsetDB("PostalCode")      = .postalCode

          objRecordsetDB("CountryOrRegion") = .co

          objRecordsetDB("HomePhone")       = .homePhone

          objRecordsetDB("Pager")           = .pager

          objRecordsetDB("MobilePhone")     = .mobile

          objRecordsetDB("FaxNumber")       = .facsimileTelephoneNumber

          objRecordsetDB("Notes")           = .info

          objRecordsetDB("Title")           = .title

          objRecordsetDB("Department")      = .department

          objRecordsetDB("CompanyName")     = .company



          ' Get the distiguished name of the manager

          strManagerDN = .manager

          ' If manager value is not blank then process

          If strManagerDN <> "" Then

            ' Bind to manager's account

            Set objManager = GetObject("LDAP://" & strManagerDN & "")

              ' Populate the Access database with the display name of the

                manager

              objRecordsetDB("Manager")     = objManager.displayName

            ' Release this object reference

            Set objManager = Nothing

          End If



          ' Define that this record was exported from AD

          objRecordsetDB("ImportedFromAD")  = "True"



        ' Commit the record

        objRecordsetDB.Update



        ' Release this object reference

        Set objUser = Nothing

        End If

      End With

    End If

  End If



 ' Move to the next record in the AD recordset

  objRecordsetAD.MoveNext

Loop



' Close the Access database recordset

objRecordsetDB.Close

' Close the Access database connection

objConnectionDB.Close



' Release these object references

Set objRecordsetDB = Nothing

Set objConnectionDB = Nothing



' Close the AD recordset

objRecordsetAD.Close

' Close the AD connection

objConnectionAD.Close



' Release these object references

Set objRecordsetAD = Nothing

Set objConnectionAD = Nothing



' Let the user know how long this process took

WScript.Echo "The script completed in approximately " & _

             Second(TimeValue(now( )) - dtStart) & _

             " seconds."



' That's all folks!

Wscript.Quit

Running the Hack

This database and script version has been tested on various versions of Windows (the minimum requirements tested were Windows 2000 Service Pack 2 running Internet Explorer 5 with Microsoft Windows Script v5.5 participating in a small Active Directory domain). Though this solution works for me, your results may vary due to environmental differences. I saved these items in a directory named C:\EmployeeInfo. If you save them somewhere else on your system, you will need to modify the Data Access Page connection string in the EmployeeInfo.htm file. This can be done from within Access or by editing the .htm file directly. Also, the script and database should be in the same directory. If they are in different directories, you should edit the AccessDatabase constant in the script to point to the proper location where the database is saved.

To run the script, simply type cscript ExportAdUsers.vbs from the command line from the current directory in which the script is found. The script, database, and HTML form page are all available from the O'Reilly web site.

Figure 2-8 shows a sample session on running the script.