How to query unsynced users using SQL

Ramy December 2, 2021

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

1 answer

1 accepted

0 votes
Answer accepted
Ramy December 6, 2021

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
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 9, 2023

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 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
7
TAGS
AUG Leaders

Atlassian Community Events