Access Query

[Kaushik1]

Hi,

We are working on preparing an access query with below conditions:

1. User should not be able to see endpoint on ARS page when there is an already existing request in pending state.

2.User should not be able to see endpoint on ARS page when there is task with source 'Request' in new state.

3. User should not be able to see endpoint on ARS page when there is no account present for user in that endpoint.

We are trying to use below query which is working when it is used separately but not working upon joining all three of them.

where users.userkey not in (select a.userkey from arstasks a where a.ENDPOINT='' and a.status=1 and a.SOURCE in ('REQUEST') UNION select ra.userkey from ars_requests ars, REQUEST_ACCESS RA where ars.REQUESTKEY=RA.REQUESTKEY and ars.status=1 and ars.ENDPOINTASCSV='') and (${requestee.id} in (select userkey from user_accounts where accountkey in (select accountkey from accounts where endpointkey = '')))

[rushikeshvartak]

#1 enable endpoint flag

Block any request due to existing request/task in-flight

other query is correct validate using data analyzer

[Kaushik1]

Using Block inflight functionality, task created from analytics/ rules will also not allow user to raise request. We want user to raise request if task is created via rules/ analytics. Hence, block task request in flight cannot be used.

We tried using the above query, but it is allowing user to raise request even though task created from request is in new state. 

[Raghu]

@Kaushik1  may i know new state means request inprogress right?

[Kaushik1]

When we try to raise request for a user whose old request is approved and task created from that particular request is still in new state, then ideally as per the access query, it should not allow user to raise a new request until task is completed (source of task - request). However, it is still allowing user to raise a new request.

[Raghu]

@Kaushik1  use Block any request due to existing request/task in-flight and enable it if already request in progress any type of reequest it wont allow to requets , did you tried?

[Kaushik1]

As mentioned in previous notes, Using Block inflight functionality, task created from analytics/ rules will also not allow user to raise new request. We want to allow user to raise new request if task created via rules/ analytics is in new state. Hence, block task/request in flight cannot be used.

[Raghu]

@Kaushik1okay got it thank you.

can you give below query and pass that entitlment value or valukey try it

where ${requestor.id} in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY and ae1.accountkey = a.accountkey and ae1.entitlement_valuekey = (select entitlement_valuekey from entitlement_values where entitlement_value='XXX'))

[rushikeshvartak]

Below query works for me 

select username from users where users.userkey not in (select a.userkey from arstasks a where a.ENDPOINT=17 and a.status=1 and a.SOURCE in ('REQUEST') UNION select ra.userkey from ars_requests ars, REQUEST_ACCESS RA where ars.REQUESTKEY=RA.REQUESTKEY and ars.status=1 and ars.ENDPOINTASCSV='Costar') and ('57644' in (select userkey from user_accounts where accountkey in (select accountkey from accounts where endpointkey = '')))

Access Query

where users.userkey not in (select a.userkey from arstasks a where a.ENDPOINT=17 and a.status=1 and a.SOURCE in ('REQUEST') UNION select ra.userkey from ars_requests ars, REQUEST_ACCESS RA where ars.REQUESTKEY=RA.REQUESTKEY and ars.status=1 and ars.ENDPOINTASCSV='ABCD') and (${requestee.id} in (select userkey from user_accounts where accountkey in (select accountkey from accounts where endpointkey = 17)))

Update bold information in access query / if this does not working share logs when you application select page loaded for user

[Kaushik1]

Hi,

In data analyser we are not getting the user which is as expected. 

select userkey from users where userkey not in (select a.userkey from arstasks a where a.ENDPOINT=355 and a.status=1 and a.SOURCE in ('REQUEST') UNION select ra.userkey from ars_requests ars, REQUEST_ACCESS RA where ars.REQUESTKEY=RA.REQUESTKEY and ars.status=1 and ars.ENDPOINTASCSV='Workday Access') and (1031 in (select userkey from user_accounts where accountkey in (select accountkey from accounts where endpointkey = 355))) and userkey=1031

However, if we add it to access query and check the same scenario where task is in new state and task source is request. It is still allowing user to raise request.

Test case performed:

1. Endpoint is not populating on ARS form for user not having account in workday access endpoint which is expected.

2. It is allowing user to raise request if user is having account and task is in new state(source-request).

Below is the logs snapshot:

We think it is trying to exclude the user in first query and exclude the same in another query because of which it is not working. example - exclude 1301 and include 1301

[rushikeshvartak]

SELECT userkey
FROM users
WHERE userkey = 1031
AND (
userkey NOT IN (
SELECT a.userkey
FROM arstasks a
WHERE a.ENDPOINT = 355
AND a.status = 1
AND a.SOURCE IN ('REQUEST')
)
OR
userkey NOT IN (
SELECT ra.userkey
FROM ars_requests ars
INNER JOIN REQUEST_ACCESS RA ON ars.REQUESTKEY = RA.REQUESTKEY
WHERE ars.status = 1
AND ars.ENDPOINTASCSV = 'Workday Access'
)
)
AND 1031 IN (
SELECT userkey
FROM user_accounts
WHERE accountkey IN (
SELECT accountkey
FROM accounts
WHERE endpointkey = 355
)
);

[Kaushik1]

Hi @rushikeshvartak ,

Could you please share the general access query that can be tested as even the old query was working fine in data analyzer/reports?

[itinjic]

Here is a modified version of your access query that combines all three conditions:

WHERE users.userkey NOT IN ( SELECT a.userkey FROM arstasks a WHERE a.ENDPOINT = '' AND a.status = 1 AND a.SOURCE IN ('REQUEST') UNION SELECT ra.userkey FROM ars_requests ars, REQUEST_ACCESS RA WHERE ars.REQUESTKEY = RA.REQUESTKEY AND ars.status = 1 AND ars.ENDPOINTASCSV = '' ) AND ( ${requestee.id} NOT IN ( SELECT userkey FROM user_accounts WHERE accountkey IN (SELECT accountkey FROM accounts WHERE endpointkey = '') ) OR NOT EXISTS ( SELECT 1 FROM ars_requests ars, REQUEST_ACCESS RA WHERE ars.REQUESTKEY = RA.REQUESTKEY AND ars.status = 3 AND ars.ENDPOINTASCSV = '' AND ra.userkey = ${requestee.id} ) )

This query should meet your requirements. It checks for each condition separately and combines them using NOT IN and NOT EXISTS clauses.

[Kaushik1]

Hi,

The query mentioned above is allowing users to raise request who do not have account in that endpoint. Users having account in that endpoint are not getting the endpoint on ARS page.

Also , we tried modifying the query below, which again is not restricting user to raise request if task is in new state.

WHERE users.userkey NOT IN ( SELECT a.userkey FROM arstasks a WHERE a.ENDPOINT = '' AND a.status = 1 AND a.SOURCE IN ('REQUEST') UNION SELECT ra.userkey FROM ars_requests ars, REQUEST_ACCESS RA WHERE ars.REQUESTKEY = RA.REQUESTKEY AND ars.status = 1 AND ars.ENDPOINTASCSV = '' ) AND ( ${requestee.id} IN ( SELECT userkey FROM user_accounts WHERE accountkey IN (SELECT accountkey FROM accounts WHERE endpointkey = '') ) OR EXISTS ( SELECT 1 FROM ars_requests ars, REQUEST_ACCESS RA WHERE ars.REQUESTKEY = RA.REQUESTKEY AND ars.status = 3 AND ars.ENDPOINTASCSV = '' AND ra.userkey = ${requestee.id} ) )

[rushikeshvartak]

Check below query in data analyzer

SELECT
*
FROM
users
WHERE
users.userkey NOT IN (
SELECT a.userkey
FROM arstasks a
WHERE a.ENDPOINT = '' AND a.status = 1 AND a.SOURCE IN ('REQUEST')
UNION
SELECT ra.userkey
FROM ars_requests ars
JOIN REQUEST_ACCESS RA ON ars.REQUESTKEY = RA.REQUESTKEY
WHERE ars.status = 1 AND ars.ENDPOINTASCSV = ''
)
AND (
${requestee.id} IN (
SELECT userkey
FROM user_accounts
WHERE accountkey IN (
SELECT accountkey
FROM accounts
WHERE endpointkey = ''
)
)
OR EXISTS (
SELECT 1
FROM ars_requests ars
JOIN REQUEST_ACCESS RA ON ars.REQUESTKEY = RA.REQUESTKEY
WHERE ars.status = 3 AND ars.ENDPOINTASCSV = '' AND ra.userkey = ${requestee.id}
)
)
AND NOT EXISTS (
SELECT 1
FROM arstasks at
WHERE at.userkey = users.userkey AND at.status =1
);