IBM Connections : a way to automate profile pic deletions for disabled users

One of my IBM Connections customers has asked if was possible to remove profile pics of the disabled users that wasn't using Connectons at least from X months.

This task is not available out of the box but luckily there is a lot of way to accomplish this request using IBM TDI or shell scripts.

After some evaluation I choose to use 2 different scripts, the first one executed on db server and the second one on Connections DMGR.

DB Scripts
This environment is running on Db2 , so this should be adjusted if you need to use it on different db servers but  could give you the idea about where are the infos , all available inside Profiles databases PEOPLEDB on Db2.

This is the select I've used in my script

select EMPINST.EMPLOYEE.PROF_GUID FROM EMPINST.EMPLOYEE
INNER JOIN EMPINST.PHOTO ON EMPINST.PHOTO.PROF_KEY = EMPINST.EMPLOYEE.PROF_KEY INNER JOIN EMPINST.PROFILE_LAST_LOGIN ON EMPINST.PROFILE_LAST_LOGIN.PROF_KEY = EMPINST.EMPLOYEE.PROF_KEY
WHERE EMPINST.EMPLOYEE.PROF_STATE='1' and timestampdiff (64, CURRENT TIMESTAMP -EMPINST.PROFILE_LAST_LOGIN.PROF_LAST_LOGIN) > X"

where X is the number of the month this disabled user with photo are not using Connections Profiles.

to format the output I've insert  an | awk   after the select

 awk "{print \"ProfilesService.deletePhotoByUserId('\"\$1\"')\" }" >> clearphotos.py

and I've obtained a result like the following wit a user per line


...
ProfilesService.deletePhotoByUserId('11111111-222A-AA22-1122-11B222B11B22')
...

To have the script ready to be executed inside wsadmin we could add on top of the script the following 2 lines:

echo "batchMode = 1" > clearphotos.py
echo "execfile('profilesAdmin.py')" >> clearphotos.py

batchMode =1 is necessary if you have a multi node environment  (click here for details. )

I've shared this script here  , in my publich GitHub repo..

Script on Connections DMGR

This script is a common shell script so I'm not sharing it here but I've made this 3 steps:

1. clearphotos,py copy from DBserver to DMGR server
2. execurion of the script inside wsadmin ( wsadmin.sh -lang jython -user connAdmin -password password -f clearphotos.py )
3. log of the operation executed

To close this request I've scheduled the 2 scripts on the 2 servers using crontab scheduling the databse script some minutes before the other.