unlicensed users

[Kota Sreenivasa Shravana Kumar]

Does anyone know, How do I get a list of unlicensed users in STASH using either CLI or database ? 

Thanks !

[Caterina Curti · Accepted solution]

Hi,

Another approach is to retrieve the list of licensed users using the method described on the How do I find which users count against my Bitbucket Server license? article.

This list can then be compared with the full list of users retrieved using the /REST/API/1.0/ADMIN/USERS REST API endpoint (Bitbucket Server - REST).

Cheers,

Caterina - Atlassian

[Jeff Thomas]

Hi,

This query is for Postgres so it may need to be adapted depending on your database, but it should return the unlicensed users in your Stash instance.

select * from ( (select child_name as user_name from cwd_membership where lower_parent_name not in (select group_name from sta_global_permission where group_name is not null) and membership_type='GROUP_USER' and group_type='GROUP') except (select slug as user_name from sta_global_permission
join sta_normal_user on sta_global_permission.user_id = sta_normal_user.user_id) ) as tmp;

[Kota Sreenivasa Shravana Kumar]

This is not working for Oracle database. This is throwing the following error. ERROR at line 1: ORA-00907: missing right parenthesis Appreciate, if you can send me the query for Oracle database. Thanks !

[Jeff Thomas]

I don't have an Oracle database readily available, but does this query work for you? select * from ( (select child_name as user_name from cwd_membership where lower_parent_name not in (select group_name from sta_global_permission where group_name is not null) and membership_type='GROUP_USER' and group_type='GROUP') minus (select slug as user_name from sta_global_permission join sta_normal_user on sta_global_permission.user_id = sta_normal_user.user_id) ) as tmp;

[Kota Sreenivasa Shravana Kumar]

No. This is also not working. I get the following error. ERROR at line 1: ORA-00933: SQL command not properly ended

[Kanwar]

Hi @Kota Sreenivasa Shravana Kumar ,

You can remove the "as tmp" at the end of the query and it should work:

select * from ( (select child_name as user_name from cwd_membership where lower_parent_name not in (select group_name from sta_global_permission where group_name is not null) and membership_type='GROUP_USER' and group_type='GROUP') minus (select slug as user_name from sta_global_permission join sta_normal_user on sta_global_permission.user_id = sta_normal_user.user_id) );