해당 쿼리문은 Mysql 쿼리로 작성되었습니다.
프로젝트 권한 별로 유저 정보 얻기
//프로젝트 역할에 설정된 유저정보 얻기 SELECT p.pname, pr.NAME, u.display_name FROM projectroleactor pra INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID INNER JOIN project p ON p.ID = pra.PID INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name; //프로젝트 역할에 할당된 그룹정보 얻기 SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cmem.child_name, u.display_name FROM projectroleactor pra INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID INNER JOIN project p ON p.ID = pra.PID INNER JOIN cwd_membership cmem ON cmem.parent_name = pra.roletypeparameter INNER JOIN app_user au ON au.lower_user_name = cmem.child_name INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name WHERE pra.roletype = 'atlassian-group-role-actor' order by p.pname;
유저 마지막로그인 및 active 상태 얻기
SELECT d.directory_name AS "Directory", u.user_name AS "Username", u.display_name AS "Full_Name", u.lower_email_address AS "Email_Address", u.active AS "Active", from_unixtime((cast(attribute_value AS UNSIGNED)/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 WHERE attribute_name = 'login.lastLoginMillis' ) AS a ON a.user_id = u.id JOIN cwd_directory d ON u.directory_id = d.id ORDER BY "Last Login" DESC;
유저 마지막로그인 및 active 상태 얻기(그룹추가 버전)
SELECT m.parent_name AS "Group", u.user_name AS "Username", u.display_name AS "Full_Name", u.active AS "Active", from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login" FROM cwd_user u JOIN ( SELECT DISTINCT child_name, parent_name FROM cwd_membership m ) AS m ON m.child_name = u.user_name JOIN ( SELECT * FROM cwd_user_attributes WHERE attribute_name = 'login.lastLoginMillis' ) AS a ON a.user_id = u.id JOIN cwd_directory d ON u.directory_id = d.id ORDER BY "Last Login" DESC;