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 !

[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