Skip to main content

System Query

Comments

2 comments

  • The Customer

    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;

    0
  • The Customer

    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;

    0

Please sign in to leave a comment.