Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Request Roles Query is not working with {currentUser} and {requestor}

yogarajk
New Contributor III
New Contributor III

Hi Team,

We have created 4 different enterprise roles. The roles have to be visible in ARS page based on employee type and requestor's SAV roles.

We have tried the below query for employee type:

and (substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser} ) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser})))

Below query for SAV role:

and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor})

Both the queries are working if we configured separately. but the same query is not working combined of both queries.

and ((substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser} ) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser}))) and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor}))

Error screenshot below:

yogarajk_0-1718171209422.png

Log message: {currentUser} binding variable is calculating in logs but {requestor} binding variable is not calculating.

yogarajk_0-1718173382770.png

 

Please assist us on the same.

Thanks,

Yogaraj K

11 REPLIES 11

PremMahadikar
All-Star
All-Star

Hi @yogarajk ,

Please try the below, its working for me.

and (substring_index (rl.customproperty1,',',1) = (select users1.employeeType from Users users1 where users1.id={currentUser}) or substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users2.employeeType from Users users2 where users2.id={currentUser})) and rl.customproperty3 = (select users3.customproperty36 from Users users3 where users3.id={requestor})

 

If this answers your question, please consider selecting Accept As Solution and hit Kudos

Hi @PremMahadikar ,

The query is not working for me. Below is the error log.

yogarajk_0-1718195660853.png

Thanks & Regards,

Yogaraj K

and (
(
substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser})
or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser})
)
and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor})
)


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

@yogarajk  use instead of '=' use 'in' and try

 

and ((
substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser})
or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser})
)
and rl.customproperty3 = (select users.customproperty36 from Users users where users.id in {requestor}))


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

PremMahadikar
All-Star
All-Star

@yogarajk ,

substring_index had a space. Corrected!

and (substring_index(rl.customproperty1,',',1) = (select users1.employeeType from Users users1 where users1.id={currentUser}) or substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users2.employeeType from Users users2 where users2.id={currentUser})) and rl.customproperty3 = (select users3.customproperty36 from Users users3 where users3.id={requestor})

 

yogarajk
New Contributor III
New Contributor III

Hi @Raghu @PremMahadikar @rushikeshvartak 

I tried with the above queries, but no luck. Still getting the same error message in ARS.

Thanks & Regards,

Yogaraj K

Query is working as expected in v24.5 

and ( ( substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser}) or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser}) ) and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor}) )

rushikeshvartak_0-1718257179524.png

rushikeshvartak_1-1718257281748.png

 

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Query is not working in V23.5. Is there any limitation or bug in V23.5 for Role Request Query?

Thanks & Regards,

Yogaraj K

Check audit log.


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Hi @rushikeshvartak ,

I tried with the below query, but no luck.

and ((substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser}) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser}))) and (substring_index (rl.customproperty3,',',1) = (select users.customproperty36 from Users users where users.id={requestor}) or (substring_index(substring_index(rl.customproperty3,',',2),',',-1)=(select users.customproperty36 from Users users where users.id={requestor}))))

Error message:

yogarajk_0-1718347776170.png

Thanks & Regards,

Yogaraj K

 

 

Validate same with 24.x once upgraded


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.