Issues with the Workday Recon RPT

[SriRanga]

Hi Team,

We need to get the users report, where a user have role and the Entitlements assigned but the user wont have any account for the Entitlement assigned.

there are users in enterprise roles that have Workday as an entitlement, but the users do not have any workday account associated with their ID

I have prepared below query but not giving corect result-

----------------------------------------------

select u.username, r.displayname as rolename from users u, role_user_account ra, roles r, endpoints e where u.userkey=ra.userkey and ra.rolekey=r.rolekey and r.ENDPOINTKEY=e.ENDPOINTKEY and e.displayname='workday'

and

u.username in(select u.username from users u, accounts a, endpoints e, user_accounts ua where u.userkey=ua.userkey and a.endpointkey=e.endpointkey and e.endpointname not in('workday'))

------------------------------------------------

the requirement is user who are having enterprise roles but there is no account for that user only role got assigned we need those users.

Thanks,

Amit Aware

[sudeshjaiswal]

Hello @SriRanga,

You May try the below query for your use case:

SELECT u.username,
       e.endpointname,
       r.role_name
FROM   users u
       JOIN role_user_account rua
         ON u.userkey = rua.userkey
       JOIN roles r
         ON rua.rolekey = r.rolekey
       JOIN endpoints e
         ON r.endpointkey = e.endpointkey
WHERE  r.status = 1
       AND NOT EXISTS (SELECT 1
                       FROM   role_user_account ru
                       WHERE  ru.rolekey = r.rolekey);  


Thanks,

[SriRanga]

@sudeshjaiswal -- can you brief on elaborating the above query so that I can understand how its working ?

Thanks

[sudeshjaiswal]

Hello @SriRanga,

The following SQL query retrieves the username, endpoint name, and role name from the `users`, `endpoints`, and `roles` tables. It utilizes `JOIN` clauses to combine data from these tables based on matching values in specified columns.
      The query includes a `WHERE` clause to filter the results, including only rows where the `status` column in the `roles` table equals 1 and where there are no matching `rolekey` values in the `role_user_account` table.

Thank

[SriRanga]

@sudeshjaiswal ,

We want below query for specific application named 'workday'

SELECT u.username,
       e.endpointname,
       r.role_name
FROM   users u
       JOIN role_user_account rua
         ON u.userkey = rua.userkey
       JOIN roles r
         ON rua.rolekey = r.rolekey
       JOIN endpoints e
         ON r.endpointkey = e.endpointkey
WHERE  r.status = 1
       AND NOT EXISTS (SELECT 1
                       FROM   role_user_account ru
                       WHERE  ru.rolekey = r.rolekey); 

Can you please help me with exactly where I can use additional condition (and e.displayname='workday') ?

Thanks

[sudeshjaiswal]

Hello @SriRanga

SELECT u.username,
e.endpointname,
r.role_name
FROM users u
JOIN role_user_account rua
ON u.userkey = rua.userkey
JOIN roles r
ON rua.rolekey = r.rolekey
JOIN endpoints e
ON r.endpointkey = e.endpointkey
WHERE r.status = 1
AND e.displayname='workday'
AND NOT EXISTS (SELECT 1
FROM role_user_account ru
WHERE ru.rolekey = r.rolekey);


Thanks,