Active users showing as inactive in All Connections search

A customer was seeing some users marked as inactive when using the All Connections search but when clicking through to the user’s profile they were active and active in communities and all over areas of Connections.

Looking into the database tables I found that the “state” of these users were correct, for example, in the EMPINST.GIVEN_NAME a particular user had a PROF_USRSTATE equalling 0 which means he’s active. In the EMPINST.EMPLOYEE table affected users had their email addresses which are normally removed when they are made inactive.

After some investigation I found that by simply activating them would mark them as active without any changes to the various tables in PEOPLEDB.

This got me thinking that the problem was an index issue and without knowing how many people were affected I suggested that the customer recreate the index. I provided them with steps of how to back it up, delete it from the file system and create a new one but even after the index created users were still showing as inactive.

Thankfully I had access to the Control Center and decided to look at all the PEOPLEDB tables, none were useful. I then started looking at the next logical database, HOMEPAGE. Interestingly, in the HOMEPAGE.PERSON table there is a column called STATE and the affected users had a value of 1 in that column. Running the following command changed the STATE to 0 and then searching for the user using the All Connections search showed him as active.

wsadmin.bat -lang jython -port 8879
execfile(“D:\IBM\WebSphere\AppServer\profiles\AppSrv01\config\bin_lc_admin\profilesAdmin.py”)
ProfilesService.activateUserByUserId(“E4BB9E9D-43D3-B5A4-8025-7433003EFACB”,email=”ben.williams@acme.com”, displayName=”Ben Williams”)

Going further I had to identify how many users were affected and the below query gave me the column values I needed to activate users who were marked inactive.

SELECT PERSON.DISPLAYNAME, PERSON.EXID, PERSON.USER_MAIL_LOWER FROM HOMEPAGE.PERSON AS PERSON WHERE PERSON.USER_MAIL_LOWER IS  NOT  NULL  AND PERSON.STATE = 1

The above query helped but there were still a number of users that were not in HOMEPAGE.PERSON and are in PEOPLEDB. These people were showing as inactive in the All Connections search BUT had never logged into Connections and hence their email addresses had not populated the HOMEPAGE database. These I had cross referenced manually as I don’t have the know how to build a query over different databases 😦

There is a bit of history here. The customer is importing users manually via populate_from dn_file because they want to control who is being added until their Connections 4 environment has been signed off for production and a custom TDI assembly line has been created. A few months ago sync_all_dns was run accidentally which meant that a 1000 or so users had to be identified and then removed from Connections. I believe that this (in some) way caused these problems.

Advertisements