I want to create a 4 column CSV like the one below with reporting extracted from pending task
Column 1: Entitlement value in Add/Remove Access
Column 2: UPN of Entitlement value
Column 3: UPN of Accounts
Column 4: ACTION (Add Access or Remove Access)
SELECT ev.entitlement_value AS 'GROUP_NAME', ev.customproperty7 AS 'MAIL_ADDRESS',a.name AS 'USER', CASE WHEN at.TASKTYPE='1' THEN 'ADD ACCESS' WHEN at.TASKTYPE='2' THEN 'REMOVE ACCESS' else at.tasktype end as 'ACTION' FROM arstasks at, entitlement_values ev, accounts a, users u, user_accounts ua WHERE at.accountname = a.name AND at.entitlement_valuekey = ev.entitlement_valuekey AND a.accountkey = ua.accountkey AND ua.userkey = u.userkey AND at.endpoint = '3' AND at.status = 1 AND ev.customproperty8 = 'true' AND ev.customproperty10 = 'true' AND ev.customproperty11 is NULL AND (at.tasktype = 1 OR at.tasktype = 2)
However, this does not extract the task of assigning an Entitlement to a Service Account. This is because ua.accountkey and u.userkey are not retained for a Service Account.
Therefore, I have slightly modified the above so that you do not have to look at ua.accountkey and u.userkey if the account type is' Shared Account'.
Therefore, I have slightly modified the above so that you do not have to look at ua.accountkey and u.userkey if the account type is' Shared Account'.
SELECT ev.entitlement_value AS 'GROUP_NAME', ev.customproperty7 AS 'MAIL_ADDRESS', a.name AS 'USER', CASE WHEN at.TASKTYPE='1' THEN 'ADD ACCESS' WHEN at.TASKTYPE='2' THEN 'REMOVE ACCESS' else at.tasktype end as 'ACTION' FROM arstasks at, entitlement_values ev, accounts a, users u, user_accounts ua WHERE at.accountname = a.name AND at.entitlement_valuekey = ev.entitlement_valuekey AND (a.accountkey = ua.accountkey OR a.accounttype = 'Shared Account') AND (ua.userkey = u.userkey OR a.accounttype = 'Shared Account') AND at.endpoint = '3' AND at.status = 1 AND ev.customproperty8 = 'true' AND ev.customproperty10 = 'true' AND ev.customproperty11 is NULL AND (at.tasktype = 1 OR at.tasktype = 2)
However, running this reporting returns too many records to finish.
How can I write a report to extract Add Access for the service account of pending task as shown in the CSV image above?
