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

Here is a query if user groups are not needed - just users, status, and last login:

SELECT DISTINCT p.username,
c1.name FULL_NAME,
st.name status,
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 c1.section_set_key =h.user_key
AND c1.section_set_key =p.home_collection_key
AND r.is_active =1
AND p.primary_key =s.owner_key(+)
and c1.state_key=st.primary_key
GROUP BY p.username,
c1.name,
st.name
ORDER BY p.username,
c1.name;

-PerkinElmer Technical Support