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, groups where they belong to, and the time of last login:

SELECT DISTINCT p.username,

c1.name FULL_NAME,

st.name status,

h.membership_name user_group,

MAX(s.start_time) last_login

FROM env_user_hierarchy h ,

eln_references r,

eln_people p,

eln_collections c1,

eln_sessions s,

eln_states st

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

AND h.membership_name <>c1.name

AND p.primary_key =s.owner_key(+)

and c1.state_key=st.primary_key

GROUP BY p.username,

c1.name,

st.name,

h.membership_name

ORDER BY p.username,

c1.name,

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 online View Users Profile Print this message


CS Gleb

Posts: 184
Joined: 2/24/2009

Answer Answer

Here is the script to obtain users, status, groups where they belong to, and the time of last login:

SELECT DISTINCT p.username,

c1.name FULL_NAME,

st.name status,

h.membership_name user_group,

MAX(s.start_time) last_login

FROM env_user_hierarchy h ,

eln_references r,

eln_people p,

eln_collections c1,

eln_sessions s,

eln_states st

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

AND h.membership_name <>c1.name

AND p.primary_key =s.owner_key(+)

and c1.state_key=st.primary_key

GROUP BY p.username,

c1.name,

st.name,

h.membership_name

ORDER BY p.username,

c1.name,

h.membership_name;



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

-PerkinElmer Technical Support



Edited: 5/8/2018 at 10:53 PM by CS Gleb

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