![]() |
PerkinElmer Informatics Support Forum | ![]() |
Topic Title: System Query Topic Summary: User Management Created On: 1/24/2018 2:49 PM Status Post and Reply |
Linear : Threading : Single : Branch |
![]()
|
![]() 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; | |
![]() |
|
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? |
|
|
|
![]() |
|
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 |
|
|
|
![]() |
|
Here is a query if user groups are not needed - just users, status, and last login: SELECT DISTINCT p.username, ------------------------- -PerkinElmer Technical Support |
|
|
|
![]() |
FuseTalk Basic Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.