Who deleted my community?!? – the answer

I received a question today who was responsible for the deletion of their community. All I could find was this article with the wsadmin commands to restore communities and to get a list of soft deleted communities. That didn’t answer my question. In the end, while going through the tables in the SQL database, I came up with this query:

SELECT evt.[COMMUNITY_UUID]
      ,com.[NAME] AS COMMUNITY_NAME
      ,mem.[LOGINNAME] AS DELETED_BY
      ,evt.[CREATED] AS DATE_DELETED
FROM [SNCOMM].[SNCOMM].[EVENTLOG] evt 
  LEFT JOIN [SNCOMM].[SNCOMM].[COMMUNITY] com ON evt.COMMUNITY_UUID = com.COMMUNITY_UUID
  LEFT JOIN [SNCOMM].[SNCOMM].[MEMBERLOGIN] mem ON evt.CREATED_BY = mem.MEMBER_UUID
WHERE evt.EVENT_NAME='community.softdeleted' AND mem.[LOGINNAME] LIKE '%@%'

One big disadvantage is that this list only shows the communities that were deleted in the last 30 days. In other words, it doesn’t show all the communities which are currently soft deleted. I think the only way to find who deleted communities that were deleted between 30 and 90 days ago, is to grab the community database from a month or 2 months ago from your backup.