Fetch AccountKey in workflow( Emergency Access ID Request Workflow)

[AS5278]

Hi,

We have written a query in the Custom Assignment Block of the workflow to fetch the Rank 1 owner of the requested privileged account.

Below is the query:

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
WHERE ra.REQUESTKEY =${ARSREQUEST.id} AND ao.rank = 1;

This will work fine if user raises a request for a single account at a time.

But if we raise a request for let's say 2 accounts and both of these accounts have different Rank1  owners then the above query will give two users in result set. This is not what we want.

I figured out that since the workflow is parallel, for each requested account the workflow will be called separately. So if I make an addition to the above query like below it might work:

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
WHERE ra.REQUESTKEY =${ARSREQUEST.id} AND ao.rank = 1 and ao.accountkey=(select accountkey from accounts where accountid={Requested account's ID});

 The problem is how do I fetch the requested account's id or key in the workflow?.

Please guide.

Thanks,

Atul Singh

[rushikeshvartak]

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
JOIN request_access_attrs raa ON raa.REQUEST_ACCESS_KEY=ra.REQUEST_ACCESSKEY
AND raa.attribute_name='ACCOUNTKEY'
WHERE ra.REQUESTKEY =1366 AND ao.rank = 1 and ao.accountkey=raa.ATTRIBUTE_VALUE

[AS5278]

@rushikeshvartak 

It is still giving both the usernames:

Here, '5173' is the requestkey. In this request two CPAM accounts were requested and both of these accounts have 'different' Rank 1 owners. 

[rushikeshvartak]

What is ask here ?

[AS5278]

@ehrushikesh 

If the user requests for let's say two privileged accounts in a single request and each of these two accounts have different 'Rank 1' Owners.

For Ex:-

Account1 -> Rank 1 Owner is 'ABCD'

Account2 -> Rank 1 Owner is 'EFGH'

So the requirement is that, for 'Account 1' request should go for approval to only user 'ABCD' 

Similarly, for 'Account 2' request should go for approval to only user 'EFGH'.

But, with the current query what is happening is, for both these requested accounts it is going for approval to both the 'Rank1' owners i.e it is going for approval to 'ABCD' as well as 'EFGH' for each requested account. 

This is happening because in the above query we are performing the filter(WHERE clause) based on 'RequestKey'. But since the 2 requested accounts are part of the same request, the request key will be same for both and hence in the result set we are getting the 'Rank 1' owner of both the requested accounts.

The workflow is of type parallel. For each requested account the workflow will be called. So, it will be called twice as 2 accounts were requested in the request.

So, if first call is for 'Account 1', then the SQL query should return the 'Rank 1' owner of only 'Account 1'.

If call is for 'Account 2', SQL query should return the 'Rank1' owner of only 'Account 2'. 

[rushikeshvartak]

This can't be handled as we have same requestkey

[AS5278]

@rushikeshvartak Is there any way to fetch the emergency access account that has been requested for?.

Thanks.

[rushikeshvartak]

No please raise idea ticket