Unable to Restrict "View Existing Users" List

[ejeong]

Hello I applied advanced query filter like below but after I applied like below it's not showing us any user in the list. 

Can anyone confirm anything wrong in syntax?

[{"for":"RequestAccessforOthers","query":"select a from Users a"},{"for":"ViewExistingAccess","query":"select a from Users a where a.customproperty14='${users.customproperty14}"}]

[rohitkumarraj]

Could you elaborate what exactly you trying to achieve here?

[ejeong]

We have Workday orgCode in CP14, so i want to list only users with same orgCode 

[naveenss]

Looks like there is an error in the query. Please use the below updated one and try once

[{"for":"RequestAccessforOthers","query":"select a from Users a"},{"for":"ViewExistingAccess","query":"select a from Users a where a.customproperty14='${users.customproperty14}'"}]

[dgandhi]

Can you try below and see if it works?

[{"for":"RequestAccessforOthers","query":"select a from Users a"},{"for":"ViewExistingAccess","query":"select a from Users a where a.customproperty14=${users.customproperty14}"}]

[ejeong]

@dgandhi I treid that but it's still not working... returning nothing in user list... we are on 5.5 3.18, could it be defect on our version?

[Manu269]

@ejeong 

I checked this Understanding the SAV Role Parameters (saviyntcloud.com) and it does allow View Existing Access tile to be configured.

This should work :

[
{
"for": "RequestAccessforOthers",
"query": "select a from Users a"
},
{
"for": "ViewExistingAccess",
"query": "select a from Users a where a.customproperty14='${users.customproperty14}'"
}
]

Moreover nothing mentioned on release notes also : Release Notes v5.5 SP3.18 (saviyntcloud.com)

[ejeong]

@Manu269 

Here is what I did..

First, I can see list of users who has same cp14 with my saviynt user. 

and I applied JSON in SAV ROLE 

but I am still seeing zero outcome.

This is debug log I found 

2023-07-05/08:10:59.054 [{}] [https-jsse-nio-443-exec-18] INFO services.ArsRequestAllowedService - WhomToRequest is set to Advance Config Query for savRoleId 2 -> [{"for": "RequestAccessforOthers","query": "select a from Users a"},{"for": "ViewExistingAccess","query": "select a from Users a where a.customproperty14='SUP001083'"}]
2023-07-05/08:10:59.054 [{}] [https-jsse-nio-443-exec-18] DEBUG services.ArsRequestAllowedService - Query JSON:[[query:select a from Users a, for:RequestAccessforOthers], [query:select a from Users a where a.customproperty14='SUP001083', for:ViewExistingAccess]]
2023-07-05/08:10:59.054 [{}] [https-jsse-nio-443-exec-18] DEBUG services.ArsRequestAllowedService - userQueryStringBuilder formed for savRolesKey 2 ( a.customer.id in(1,2,-1) AND a.statuskey= 1 AND ( ( a.customproperty14='SUP001083' ) ) ) AND
2023-07-05/08:10:59.054 [{}] [https-jsse-nio-443-exec-18] DEBUG services.ArsRequestAllowedService - Qry - Req access for others -
SELECT distinct new Map(a.systemUserName as systemUserName,a.region as region,a.location as location,
a.companyname as companyname,
a.departmentNumber as departmentNumber,
a.departmentname as departmentname,
a.jobcodedesc as jobcodedesc,
a.costcenter as costcenter,
a.id as id,
a.id as USERKEY,
a.username as USERNAME,
a.firstname as FIRSTNAME ,
a.lastname as LASTNAME,
a.displayname as DISPLAYNAME,
a.city as CITY,
a.email as EMAIL ,
a.owner as MANAGER,
a.jobCode as JOBCODE,
a.enabled as enabled,
a.startdate as startdate,
a.enddate as enddate,
a.lastsyncdate as lastsyncdate ,
a.displayname as displayname,
a.statuskey as statuskey)
FROM Users a
WHERE a.statuskey in (0,1) AND a.statuskey= 1 AND ( a.customer.id in(1,2,-1) AND a.statuskey= 1 AND ( ( a.customproperty14='SUP001083' ) ) ) AND 1=1 )
2023-07-05/08:10:59.056 [{}] [https-jsse-nio-443-exec-18] DEBUG workflow.WorkflowmanagementController - total count usersInstanceTotal 0

[ejeong]

@Manu269 

I found one cluse..

when I logged in as ROLE_ENDUSER in lower envrionment. 

I can see two user only of all active users in reqeust access for others  or View existing access. do you know where this filter may be applied?

[naveenss · Accepted solution]

Have you attached any organizations to the SAV role? coz in the logs, I can see the below 

2023-07-05/08:10:59.054 [{}] [https-jsse-nio-443-exec-18] DEBUG services.ArsRequestAllowedService - Qry - Req access for others -
SELECT distinct new Map(a.systemUserName as systemUserName,a.region as region,a.location as location,
a.companyname as companyname,
a.departmentNumber as departmentNumber,
a.departmentname as departmentname,
a.jobcodedesc as jobcodedesc,
a.costcenter as costcenter,
a.id as id,
a.id as USERKEY,
a.username as USERNAME,
a.firstname as FIRSTNAME ,
a.lastname as LASTNAME,
a.displayname as DISPLAYNAME,
a.city as CITY,
a.email as EMAIL ,
a.owner as MANAGER,
a.jobCode as JOBCODE,
a.enabled as enabled,
a.startdate as startdate,
a.enddate as enddate,
a.lastsyncdate as lastsyncdate ,
a.displayname as displayname,
a.statuskey as statuskey)
FROM Users a
WHERE a.statuskey in (0,1) AND a.statuskey= 1 AND ( a.customer.id in(1,2,-1) AND a.statuskey= 1 AND ( ( a.customproperty14='SUP001083' ) ) ) AND 1=1 )

[ejeong]

@naveenss 

You were correct! thanks. We had ALL in the filed. I removed it and it's working as expected now!