Manager Approvals in FootPrints 12

One of the most critical of features in v11 missing from v12 is manager approvals. Fortunately, there is a workaround in Microsoft SQL Server that is sufficient for most Windows Active Directory environments.

This post will document how to implement this workaround. Later we will explore some of the limitations of the workaround. The implementation requires three major steps, each of which are described below.

  1. Link an ADSI server to the FootPrints database server.
  2. Create a view of the LDAP server in the FootPrints database.
  3. Configure the FootPrints Address Book to use the new database view.

When complete, FootPrints 12 address book queries will be made against LDAP via the ADSI connector in SQL Server, which will also provide the Manager’s login ID in a format (sAMAccountName) that is usable to the FootPrints 12 approval engine.

Link the ADSI Server

The blog post How to: Use SQL Server to query Active Directory describes how to perform this step, with a modification. For brevity we will not repeat the details in that post. You will perform these configurations in the database server used by FootPrints 12.

Before attempting this process, you will need a service account in AD whose login ID and password are known to you. Please do not use your own login because that password will probably be changed every 60-90 days. Administrator privileges are not required for this function.

In the Security tab, in the options under “For the login not defined in the list above, connections will:” if you use the choice “Be made using login’s current security context” as per the article, FootPrints will display error messages during contact searches because the Apache Tomcat service used by FootPrints does not have an appropriate security context.

Please choose instead “Be made using this security context” and enter the domain, login ID, and password for the AD service account.

Below is a summary of the settings that will be used in the Linked Server.

Linked server: ADSI
Server type: Other data source
Provider: OLE DB Provider for Microsoft Directory Services
Product name: Active Directory Services 2.5
Data source: adsdatasource
Provider string: ADSDSOObject
ADSI Linked Server General properties
ADSI Linked Server Security properties

Create a Database View

In the FootPrints database, generally fpscdb001, you will create a new view. You can paste the following query into the new view. Please note that the LDAP server name and domain names will need to be tailored to your environment. This view includes commonly used fields but additional fields may be required for your environment.

This query / view assumes the primary key of the address book is sAMAccountName (User ID). If you are using another field, such as email address, you will need to make appropriate changes to the upper select and join parameters.

SELECT usr.sAMAccountName, usr.givenName, usr.sn, usr.department, usr.mail, usr.displayName, usr.title, usr.telephoneNumber, usr.physicalDeliveryOfficeName, mgr.sAMAccountName as ManagerId, manager
FROM OPENQUERY
(ADSI, 'SELECT manager, distinguishedName, mail, sAMAccountName, displayName, sn, givenName, userPrincipalName, department, title, telephoneNumber, physicalDeliveryOfficeName
FROM ''LDAP://ldap-server.domain.com/DC=domain,DC=com''
WHERE objectClass = ''Person'' AND displayName = ''*'' ') as usr
LEFT OUTER JOIN OPENQUERY
(ADSI, 'SELECT distinguishedName, sAMAccountName
FROM ''LDAP://ldap-server.domain.com/DC=domain,DC=com''
WHERE objectClass = ''Person'' AND directReports = ''*'' ') as mgr
ON usr.manager = mgr.distinguishedName

You can now save the new view. When you do save the view, SQL Server Management Studio will ask you for the name of the view. You should provide a descriptive name such as v_AddressBook_LDAP.

An Example

Below is an example where I broke the view queries into 2 views: v_ADSI_Managers and v_ADSI_Users. The Users view will be used in the Address Book configuration (below) and depends on the Managers view.

v_ADSI_Managers

SELECT samAccountName, userPrincipalName, givenName, sn, displayName, mail, distinguishedName
FROM OPENQUERY(ADSI, 
'SELECT manager, distinguishedName, mail, samAccountName, displayName, sn, givenName, userPrincipalName
FROM ''LDAP://fp12/DC=footprints12,DC=local''
WHERE objectClass = ''Person'' AND displayName = ''*'' AND directReports = ''*''
')
AS managers

v_ADSI_Users

SELECT usr.sAMAccountName, usr.givenName, usr.sn, usr.department, usr.mail, usr.displayName, usr.title, usr.telephoneNumber, usr.physicalDeliveryOfficeName, usr.manager, 
mgr.samAccountName AS managerid
FROM OPENQUERY(ADSI, 
'SELECT manager, distinguishedName, mail, sAMAccountName, displayName, sn, givenName, userPrincipalName, department, title, telephoneNumber, physicalDeliveryOfficeName
FROM ''LDAP://fp12/DC=footprints12,DC=local''
WHERE objectClass = ''Person'' AND displayName = ''*'' ')
AS usr LEFT OUTER JOIN
dbo.v_ADSI_Managers AS mgr ON usr.manager = mgr.distinguishedName

Configure the Address Book

After confirming in Microsoft SQL Server Management Studio that your view runs successfully, you can now configure your Address Book to use a Dynamic SQL source. For this you will require System Administrator access to FootPrints and a mixed-mode account in SQL Server.

  1. Log in to FootPrints 12 as a System Administrator
  2. Open the Administration console
  3. In the Address Book container on the Main page of the Administration console, select Manage
  4. Edit the appropriate Address Book. Generally, this is the address book that is linked to one or more Workspace container(s).
  5. Edit the contact item named Contact
  6. In the navigation bar on the left, select Fields.
    1. Create a new field with the following values:
      1. Singular Name: Field Manager ID
      2. Field Type: Simple Text
      3. Permission: Optional
    2. Save
    3. In the bread crumb trail, navigate up one level to the Item:Contact
  7. In the navigation bar on the left, select Forms.
    1. Add the new Manager ID field to the Agent Web form
    2. Save the form
    3. In the bread crumb trail, navigate up one level to the Item:Contact
  8. In the navigation bar on the left, select Address Book Options
  9. Select the Dynamic SQL radio button and then Configure External Source. You will be taken to the configuration screen like the one below.
  10. Enter values for:
    1. Server Address: The name or FQDN of the SQL Server that stores the fpscdb001 database
    2. Server Port: 1433 (default value, unless it was changed by your DBA)
    3. Database Type: Sql Server
    4. Database: fpscdb001
    5. Server User ID: User ID for the mixed-mode account in SQL Server
    6. Server Password: Obtained from your DBA
  11. Click Connect
  12. In Table or View select the view you created earlier (for example dbo.v_AddressBook_LDAP)
  13. For each field under FootPrints Address Book, select the corresponding attribute. The attributes were chosen when you created the view.
  14. Click Save
  15. In the bread crumb trail navigate to the top level of the container
  16. Select Save and Publish
Address Book Dynamic SQL configuration panel

Configure the Workspace Item

Having configured the Address Book with a field Manager ID that maps to the ManagerID attribute in the v_AddressBook_LDAP view, you will now need to configure an identically named field, Manager ID, in the workspace-type record definition, and link the field to the Link Control.

  1. Repeat “Configure the Address Book” steps 1 through 7.1 for the Workspace record definition that requires the approval manager configuration.
  2. On the Agent Web form select the Ticket/Contact link control (see below).
  3. In the Control Properties (left) select the Configure button (see below)
    1. Select Linked Fields
    2. Under Available Fields select the new Manager ID field
    3. Select the right arrow to move the field to Selected Fields
    4. Select Save
Agent Web form link control
Link Control Configuration

In the Workflow Process you can now configure an approval state to use the Manager ID as a dynamic approver.

  1. Navigate to Workflow Processes
  2. Edit the appropriate workflow process
  3. Select the appropriate Approval State
  4. Under Approvers select Add/Remove
  5. Under the Dynamic tab select the Manager ID field
  6. Select Assign Approvers

Limitations

The performance of address book searches using this method appears to be equivalent to queries directly against LDAP. However, there are a few caveats and limitations.

  1. The method does not support approvals by all managers of all linked contacts. Only the most recently linked contact is supported, because the most recently linked contact is the one that populated the Manager ID field in the workspace record.
  2. The Manager ID field must at least be on the agent form and be editable at least by the agent. There is currently no way for this field to be read-only.
  3. By default this method supports only 1,000 records in the query. If the query to Active Directory returns more than 1,000 records, it will be truncated. There may be a method to override this in Active Directory, however.
  4. The query above is restricted to a single OU search in Active Directory. The UNION query command will be required to to perform simultaneous searches against multiple organizational units.