Query that provides user's role and account information

[sharu]

Hi Team,

Can you please help me out with the SQL query that results user's having all the roles and accounts that are provisioned.

Thanks,

Sharanya V

[rushikeshvartak]

SELECT r.role_name,u,username,a.name,a.status
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey

[sharu]

Hi @rushikeshvartak ,

Thanks for the reply, In the same query can we also check for endpoints provisioned for the user along with roles and account information.

If yes , can you please update the same

Thanks,

Sharanya

[rushikeshvartak]

join with arstasks table and endpoints table

[rushikeshvartak]

SELECT r.role_name,u,username,a.name,a.status,a.arstaskkey
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey

[sharu]

@rushikeshvartak Can you please share ,e the update query

Thanks,

Sharanya V

Sharanya V

[Raghu]

@sharu  please check below query:

SELECT
R.ROLE_NAME,
U.USERNAME,
A.NAME,
A.STATUS,
A.ARSTASKKEY,
E.ENDPOINTNAME
FROM
ROLES R
JOIN
ROLE_USER_ACCOUNT RUA ON RUA.ROLEKEY = R.ROLEKEY
JOIN
USERS U ON U.USERKEY = RUA.USERKEY
JOIN
ACCOUNTS A ON A.ACCOUNTKEY = RUA.ACCOUNTKEY
JOIN
ARSTASKS AR ON AR.ACCOUNTKEY = A.ACCOUNTKEY
JOIN
ENDPOINTS E ON AR.ENDPOINT = E.ENDPOINTKEY