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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.