How to get Inactive users in a community using SQL

Today, a user requested us to get a list of all inactive users in her community. The community had 29000 + members and they had never cleaned up. As we are running 5.5 CR3, the wsadmin command to remove inactive users is not available (CommunitiesService.removeInactiveUsers(String “Uuid”, boolean skipLastModBump).

So Database was the only solution. Here’s the command I used. I ended up with 3717 inactive users 🙂

select SNCOMM.MEMBER.COMMUNITY_UUID, SNCOMM.MEMBER.MEMBER_UUID,SNCOMM.MEMBER.ROLE,SNCOMM.MEMBERPROFILE.DIRECTORY_UUID, SNCOMM.MEMBERPROFILE.STATE, SNCOMM.MEMBERPROFILE.DISPLAY

from SNCOMM.MEMBER left join SNCOMM.MEMBERPROFILE

on SNCOMM.MEMBER.MEMBER_UUID = SNCOMM.MEMBERPROFILE.MEMBER_UUID

WHERE SNCOMM.MEMBER.COMMUNITY_UUID = '57e29316-1d6f-4c87-b348-327de7893b0d'

AND SNCOMM.MEMBERPROFILE.STATE = 1