LiveCycle - LDAP Directory Synch Verification

One of the questions that consistently come up during LiveCycle ES installations is how one would go about verifying LiveCycle's synchronization with the corporate LDAP directory. If you have access to a database querying tool such as Aquafold AquaDataStudio, Microsoft Visual Studio, Oracle SQL Developer or Quest TOAD, you can run some queries against the LiveCycle database. Of these tools, AquaDataStudio supports all of the database platforms LiveCycle supports, runs on Windows, Linux and Solaris, and supports JDBC.

- run the following query against table EDCPRINCIPALDOMAINENTITY to determine the id of the LiveCycle User Manager domain you have created for your LDAP directory (replace lc_db_usr with your schema name):
SELECT id, commonname, domainname, syncstate
FROM lc_db_usr.EDCPRINCIPALDOMAINENTITY
ORDER BY domainname
The id will look something like this:
E69D93E4-C969-F1A7-105D-41C786157625

- Using that id, run the following query against table EDCPRINCIPALENTITY to determine the number of users that have been synched (replace lc_db_usr with your schema name):
SELECT COUNT(*) AS Expr1
FROM lc_db_usr.EDCPRINCIPALENTITY
WHERE (refdomainid = 'id_from_first_query') AND (principaltype = 'USER')

- for more details on the users, run this (replace lc_db_usr with your schema name):
SELECT canonicalname, commonname, description, email, originalname, status
FROM lc_db_usr.EDCPRINCIPALENTITY
WHERE (refdomainid = 'id_from_first_query') AND (principaltype = 'USER')
ORDER BY canonicalname

- run the following query against table EDCPRINCIPALENTITY to determine the number of groups that have been synched (replace lc_db_usr with your schema name):
SELECT COUNT(*) AS Expr1
FROM lc_db_usr.EDCPRINCIPALENTITY
WHERE (refdomainid = 'id_from_first_query') AND (principaltype = 'GROUP')

- for more details on the groups, run this (replace lc_db_usr with your schema name):
SELECT canonicalname, commonname, description, email, originalname, status
FROM lc_db_usr.EDCPRINCIPALENTITY
WHERE (refdomainid = 'id_from_first_query') AND (principaltype = 'GROUP')
ORDER BY canonicalname

These queries have been tested against SQL Server 2005 from Microsoft Visual Studio 2005.