Users

SELECT user_name,

       directory_id,

       display_name,

       email_address,

       dir.directory_position AS position

  FROM cwd_user usr

  JOIN cwd_directory dir

    ON dir.id = usr.directory_id

 WHERE user_name LIKE 'myuser%'

 ORDER BY dir.directory_position,

          user_name;

To include the last authenticated date...

SELECT u0.user_name,

       TO_TIMESTAMP(CAST(att.attribute_value AS BIGINT)/1000) AS lastAuthenticated,

       u0.directory_id,

       u0.display_name,

       u0.email_address

  FROM cwd_user u0

  JOIN cwd_user_attributes att

    ON u0.id = att.user_id

 WHERE u0.user_name LIKE 'myuser%''

   AND att.attribute_name = 'lastAuthenticated'

UNION

SELECT u1.user_name,

       NULL AS lastAuthenticated,

       u1.directory_id,

       u1.display_name,

       u1.email_address

  FROM cwd_user u1

 WHERE u1.user_name LIKE 'myuser%''

   AND u1.user_name NOT IN (SELECT u2.user_name

                              FROM cwd_user u2

      JOIN cwd_user_attributes a2

                                ON u2.id = a2.user_id

                             WHERE u2.user_name LIKE 'myuser%'

                               AND a2.attribute_name = 'lastAuthenticated')

ORDER BY 1;

Inactive Users

PostgreSQL

SELECT d.directory_name AS "Directory",

       u.user_name      AS "Username",

       u.active         AS "Active",

       u.email_address  AS "E-mail",

       TO_TIMESTAMP(CAST(attribute_value AS BIGINT)/1000) AS "Last Login"

  FROM cwd_user u

  JOIN (SELECT DISTINCT child_name

          FROM cwd_membership m

          JOIN licenserolesgroup gp

            ON m.parent_name = gp.GROUP_ID) AS m

    ON m.child_name = u.user_name

  JOIN (SELECT *

          FROM cwd_user_attributes ca

         WHERE attribute_name = 'login.lastLoginMillis') AS a

    ON a.user_id = u.id

  JOIN cwd_directory d

    ON u.directory_id = d.id

 WHERE u.active=1

 ORDER BY "Last Login" ASC;

Bibliography