Get list of endpoints whose resource owner is configured as username (example 10001)

[harishyara]

Hello All,

I need to get list of endpoints whose Resource Owner is configured as username (example 10001).

Please help me with query. 

Thank you.

[rushikeshvartak]

SELECT E.ENDPOINTNAME,
       U.USERNAME
FROM   ENDPOINTS E
       JOIN USERS U
         ON E.REQUESTOWNER = U.USERKEY
WHERE  U.USERNAME = '10001'; 

[harishyara]

Thank you for your help. Could you please let me know the query if user is present in User Group mapped as Resource Owner ?

[NM]

@harishyara you need to use usergroup table to get the required values.

[rushikeshvartak]

SELECT E.ENDPOINTNAME,
       CASE 
           WHEN E.REQUESTOWNERTYPE = 1 THEN U.USERNAME
           WHEN E.REQUESTOWNERTYPE = 2 THEN G.USER_GROUPNAME
           ELSE 'Unknown'
       END AS OwnerName,
       CASE 
           WHEN E.REQUESTOWNERTYPE = 1 THEN 'User'
           WHEN E.REQUESTOWNERTYPE = 2 THEN 'User Group'
           ELSE 'Unknown'
       END AS OwnerType
FROM   ENDPOINTS E
       LEFT JOIN USERS U
         ON E.REQUESTOWNER = U.USERKEY AND E.REQUESTOWNERTYPE = 1
       LEFT JOIN USER_GROUPS G
         ON E.REQUESTOWNER = G.USERGROUPKEY AND E.REQUESTOWNERTYPE = 2;