PerkinElmer Informatics Support Forum
Decrease font size
Increase font size
Topic Title: System Query
Topic Summary: User Management
Created On: 1/24/2018 2:49 PM
Status Post and Reply
Linear : Threading : Single : Branch
Topic Tools Topic Tools
View topic in raw text format. Print this topic.
Answer This question was answered by CS Gleb, on Friday, March 2, 2018 3:04 PM

Answer:

Here is the script to obtain users, status, and groups where they belong to:

SELECT distinct

p.username,

c1.name FULL_NAME,

decode(c1.is_active,

1, 'Active',

0, 'Inactive') status,

h.membership_name user_group

FROM env_user_hierarchy h ,

eln_references r,

eln_people p,

eln_collections c1

WHERE h.parent_type_name = 'User Group'

AND c1.section_set_key =h.user_key

AND c1.section_set_key =p.home_collection_key

AND h.membership_key = r.target_key

and p.username<>h.membership_name

AND r.is_active =1

ORDER BY

p.username,

c1.name,

decode(c1.is_active,

1, 'Active',

0, 'Inactive'),

h.membership_name;

 1/24/2018 2:49 PM
User is offline View Users Profile Print this message


tchau927

Posts: 2
Joined: 11/28/2017

I am a new ENB admin. I would like to know how to query the system to find user name, active vs inactive status, and what collections the users belong to. What tables would you join to search for this?

 3/2/2018 3:04 PM
User is offline View Users Profile Print this message


CS Gleb

Posts: 183
Joined: 2/24/2009

Answer Answer

Here is the script to obtain users, status, and groups where they belong to:

SELECT distinct

p.username,

c1.name FULL_NAME,

decode(c1.is_active,

1, 'Active',

0, 'Inactive') status,

h.membership_name user_group

FROM env_user_hierarchy h ,

eln_references r,

eln_people p,

eln_collections c1

WHERE h.parent_type_name = 'User Group'

AND c1.section_set_key =h.user_key

AND c1.section_set_key =p.home_collection_key

AND h.membership_key = r.target_key

and p.username<>h.membership_name

AND r.is_active =1

ORDER BY

p.username,

c1.name,

decode(c1.is_active,

1, 'Active',

0, 'Inactive'),

h.membership_name;



-------------------------

-PerkinElmer Technical Support

FuseTalk Basic Edition v4.0 - © 1999-2018 FuseTalk Inc. All rights reserved.