How to query unsynced users using SQL

[Ramy]

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 solution]

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

[Rebecca Häußler]

Hey all, 

we tested Ramy's query but it did not work for us as it still only displayed active users and not unsynced users. 
Therefore we contacted Atlassian Support and they sent us the following query which works like a charm :) 

select user_key, username, lower_username 
from user_mapping um 
where (lower(username) like '%%') and  
      user_key<>username and  
       (user_key<>lower_username and    
       not (exists (select 1 from cwd_user where um.username=user_name)) or  
       lower_username is null)  
order by username asc

 Best regards
Rebecca