Who deleted my Connections community?

I was asked by a customer who had deleted an important community. The SystemOut.log was of no help but DB2 holds the information I am after.

In the SNCOMM database is a table called EVENTLOG which the below screen shot was taken from using DBVisualizer. The community I focused on in my test environment is highlighted under COMMUNITY_UUID.

The screen shot shows when the community was created and then later on when Ben Williams (defined in the CREATED_BY column) added Joe Bloggs as a member (community.membership.added) and then when Ben changed Joe to be an owner (community.membership.updated).

The last two entries in the table including the important (community.removed) were created by a different ID as defined in the CREATED_BY column. You can compare this ID with the SNCOMM.MEMBERPROFILE table to compare with the MEMBER_UUID as shown in the second screen shot.

I did not have access to such a tool for the customer but got lucky by finding a bookmark referencing the deleted community which has the COMMUNITY_UUID in the URL. If you do not have the UUID then you would need to search the EVENT_METADATA column using a SQL statement like select * from sncomm.eventlog where event_metadata contains ‘community name’ BUT to run a contains statement the database needs an index unlike a like or = statement. You may want to engage your DBA at this point?

As I had the UUID I ran select * from sncomm.eventlog where community_uuid=’c436e443-bc78-4540-8907-af2a0f71c9c7′ > /tmp/deletedcommuntiy.txt which provided me with all the events similar to my screen shot below. I then ran select * from sncomm.memberprofile where member_uuid=’65977288-b975-4aba-bd47-c456a2eeebe5′ to give me all the details of the guilty party or just select display from sncomm.memberprofile where member_uuid=’65977288-b975-4aba-bd47-c456a2eeebe5′ for the display name.