How to query unsynced users using SQL

Question

Hi all

Is there a way to query using SQL all unsynced users? I want to get a list of all users who has been deactivated (on our LDAP)

I'm also looking for a way how to delete those users...

Thanks a lot for any help

Ramy

Ramy · Accepted Answer

Hi

I think, i was able to create the query. I'm sharing it with you and any improvements are welcome :)

SELECT *
  FROM cwd_user u
 WHERE u.id NOT IN (
       SELECT u.id
         FROM cwd_user u
         JOIN cwd_membership m ON u.id = child_user_id 
         JOIN cwd_group g ON m.parent_id = g.id
         JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
         JOIN cwd_directory d on u.directory_id = d.id
        WHERE sp.PERMTYPE='USECONFLUENCE' 
          AND u.active = 'T' 
          AND d.active = 'T'
        GROUP BY u.id, d.directory_name
 );

Cheers

Ramy