Skip to end of metadata
Go to start of metadata

해당 쿼리문은 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;
  • No labels