Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Assistance on Query || Reportee Access Details

Manu269
All-Star
All-Star

Hello Team,

We are working on preparing Runtime Analytics to prepare the reportee details.

Query :

'SELECT
u.username AS 'USERNAME',
m.username AS 'MANAGER USERNAME',
CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS 'USER STATUS',
accounts.name AS 'ACCOUNT NAME',
endpoints.ENDPOINTNAME AS 'ENDPOINT NAME',
CASE WHEN accounts.STATUS IS NULL THEN 'NOT AVAILABLE' WHEN accounts.STATUS = 1 THEN 'ACTIVE' ELSE accounts.STATUS END 'ACCOUNT STATUS',
ev.entitlement_value AS 'ENTITLEMENT NAME'
FROM
users u
LEFT JOIN user_accounts ua ON ua.userkey = u.userkey
LEFT JOIN accounts ON accounts.accountkey = ua.accountkey
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN account_entitlements1 ae ON ae.accountkey = accounts.ACCOUNTKEY
LEFT JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m ON m.userkey = u.manager
WHERE
u.statuskey = 1
AND accounts.STATUS IN (
'1', 'Active', 'Manually Provisioned'
)
AND m.username ='Manish';

 

Results :

Manu269_0-1713929116056.png

Assistance Required :

If you see above screenshot, we are able to get the details as required.

Anyhow the ask is to club the data for particular endpoint and entitlements in 1 row.

i.e.

In above case, arun is a reportee for manager Manish, he has access to 4-5 endpoints.

For each endpoints he has access to 2-3 entitlements.

Is it possible to print in 1 line to get access details of 1 endpoint and assocaited entitlement access.

Please assist.

 

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.
1 REPLY 1

rushikeshvartak
All-Star
All-Star

 

 

rushikeshvartak_0-1713929419055.png

SELECT    u.username AS 'USERNAME',
          m.username AS 'MANAGER USERNAME',
          CASE u.statuskey
                    WHEN '0' THEN 'INACTIVE'
                    WHEN '1' THEN 'ACTIVE'
          END                    AS 'USER STATUS',
          accounts.NAME          AS 'ACCOUNT NAME',
          endpoints.endpointname AS 'ENDPOINT NAME',
          CASE
                    WHEN accounts.status IS NULL THEN 'NOT AVAILABLE'
                    WHEN accounts.status = 1 THEN 'ACTIVE'
                    ELSE accounts.status
          END 'ACCOUNT STATUS',
          group_concat(ev.entitlement_value separator ', ') AS 'ENTITLEMENT NAMES'
FROM      users u
LEFT JOIN user_accounts ua
ON        ua.userkey = u.userkey
LEFT JOIN accounts
ON        accounts.accountkey = ua.accountkey
LEFT JOIN endpoints
ON        endpoints.endpointkey = accounts.endpointkey
LEFT JOIN account_entitlements1 ae
ON        ae.accountkey = accounts.accountkey
LEFT JOIN entitlement_values ev
ON        ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et
ON        et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m
ON        m.userkey = u.manager
WHERE     u.statuskey = 1
AND       accounts.status IN ('1',
                              'Active',
                              'Manually Provisioned')
AND       m.username = '84809'
GROUP BY  u.username,
          m.username,
          u.statuskey,
          accounts.NAME,
          endpoints.endpointname,
          accounts.status;

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.