Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Workflow condition to check logged in user is in a group

Manju
New Contributor II
New Contributor II

Hi Team, 

Workflow should allow  the privilege access request for auto approval if the user is from particular usergroup, else request should go for one level manager approval. 

Tried below  query, but it is giving error as the list contains multiple users from usergroup .  Please suggest the right approach.  

${ARSREQUEST.REQUESTOR}= select users.username from users where users.username is not null and users.userkey in (select userkey from usergroup_users where USER_GROUPKEY='2'

Thanks, 

Manju

16 REPLIES 16

sk
All-Star
All-Star

Use below condition in if/else block with groovy as expression language if outcome of this block is true then requestor is member of user group otherwise not

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.usergroupkey.id = 2 and ugu.userkey.id= requestedby?.id ")?.size() != 0)


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Manju
New Contributor II
New Contributor II

Hi Saathvik, 

Tried the above suggested query. Request ID is not getting generated.  With &quote entry in condition, workflow approval is not successful

Manju_0-1680007633352.png

Manju_1-1680007748877.png

Regards, 

Manjunatha V

 

 



@Manju : Can you provide the respective logs from server to understand the issue better where it is failing?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Manju
New Contributor II
New Contributor II

Hi Saathvik,   Here comes the log entries related to request. 

2023-03-28T20:46:41+05:30-arsms-{"log":"2023-03-28 15:16:40.580 DEBUG [] 7 --- [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Pool stats (total=25, active=0, idle=25, waiting=0) \n","stream":"stdout","time":"2023-03-28T15:16:40.580806544Z"}
 
2023-03-28T20:46:40+05:30-windows-connectorms-{"log":"15:16:40 [Debug] Quartz.Core.QuartzSchedulerThread Batch acquisition of 0 triggers\n","stream":"stdout","time":"2023-03-28T15:16:40.104761456Z"}

Regards, 

Manju

Try below I don't in your screenshot " are added in subquery so please try below

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.usergroupkey.id = 2 and ugu.userkey.id= requestedby?.id")?.size() != 0)


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Manju
New Contributor II
New Contributor II

Hi Saathvik, 

As implementation partner we do not have access to catapult.  ECM related detailed errors are not coming up in logviewer. For endusers, is there any option to change the debug level from application frontend to get detailed errors. 
Managed to fetch logs from catapult ECM logs. 
Looks like system is not accepting "and" condition in subquery.   

2023-03-31 14:16:46,705 [http-nio-8080-exec-92] ERROR services.WorkflowService - Exception in workflow service
org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:127)
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:115)
at org.jbpm.pvm.internal.script.ScriptManager.evaluateExpression(ScriptManager.java:87)
at org.jbpm.pvm.internal.el.ScriptExpression.evaluateInScope(ScriptExpression.java:48)
at org.jbpm.pvm.internal.el.Expression.evaluate(Expression.java:108)
at org.jbpm.pvm.internal.model.ExpressionCondition.evaluate(ExpressionCondition.java:41)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.findTransitionUsingConditions(DecisionConditionActivity.java:62)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.execute(DecisionConditionActivity.java:47)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.execute(DecisionConditionActivity.java:43)
at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)
at org.jbpm.pvm.internal.model.ExecutionImpl.fire(ExecutionImpl.java:582)
at org.jbpm.pvm.internal.model.ExecutionImpl.take(ExecutionImpl.java:487)
at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:123)
at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:53)
at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperation(ExecutionImpl.java:632)
at org.jbpm.pvm.internal.model.ExecutionImpl.start(ExecutionImpl.java:217)
at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:63)
at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:36)
at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42)
at org.jbpm.pvm.internal.tx.SpringCommandCallback.doInTransaction(SpringCommandCallback.java:45)
at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:49)
at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.executeInNewEnvironment(EnvironmentInterceptor.java:53)
at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:40)
at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:56)
at org.jbpm.pvm.internal.svc.ExecutionServiceImpl.startProcessInstanceByKey(ExecutionServiceImpl.java:71)
at com.saviynt.ecm.services.WorkflowService.workflowaccessreqStart(WorkflowService.groovy:1057)
at com.saviynt.ws.JbpmapiService.workflowaccessreqStartMS(JbpmapiService.groovy:64)
at com.saviynt.ecm.ws.JbpmapiController$_closure4.doCall(JbpmapiController.groovy:83)
at grails.plugin.springsecurity.rest.RestTokenValidationFilter.processFilterChain(RestTokenValidationFilter.groovy:118)
at grails.plugin.springsecurity.rest.RestTokenValidationFilter.doFilter(RestTokenValidationFilter.groovy:84)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:133)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.lang.Thread.run(Thread.java:750)
Caused by: javax.script.ScriptException: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:152)
at javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:264)
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:123)
... 39 more
Caused by: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:349)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:146)
... 41 more
Caused by: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at Script11.run(Script11.groovy:1)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:346)
... 42 more
Caused by: org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
... 44 more
2023-03-31 14:16:46,706 [http-nio-8080-exec-92] DEBUG println.PrintlnToLogger - Println :: | Error org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]

Thanks, 

Manju

 

suresh_ravuri
New Contributor III
New Contributor III

Our client has a similar requirement, is it working ?

Nidhins27
Regular Contributor
Regular Contributor

Please share the solution if found.

please use the below query format

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.user_groupkey = 2 AND ugu.userkey= '${requestedby?.id}'")?.size() != 0)
Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

NageshK
Saviynt Employee
Saviynt Employee

@naveenss Thank you for the query. I have validated this in the latest version 23.6 and it is working well. Just one thing to note is that we have to make sure the expression language is selected as groovy in the workflow for this condition. Else workflow evaluation will fail. 

@Manju @suresh_ravuri please make a note of that

Thanks,

Nagesh K

Thank you @NageshK for your response.

We are in a plan to implement manager approval workflow for JIT access request, How can we implement this manager workflow for a specific access method ? 

suresh_ravuri
New Contributor III
New Contributor III

Hi @NageshK 

Is it possible to enable manager's approval workflow only for JIT requests ?

@suresh_ravuri : Can you please elaborate the requirement like what type of target/endpoint? Because we are able to check the JIT account condition for different purpose for that we are able to build a logic wanted to check if that will fit for your requirement

Also other JIT Do you have credential and credentialless accounts on same endpoint?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

suresh_ravuri
New Contributor III
New Contributor III

We are planning to configure JIT account access method for Windows, Unix and MSSQL endpoints. 

Users are configured with credentials and credential-less access methods on the same endpoint

Use-case: For any user who attempts to create a JIT account request to Windows/Unix/MSSQL endpoints, it should trigger the manager's approval workflow and for other access methods, it should be auto-approval

@suresh_ravuri : Thanks for providing more information. Since you have credential-less accounts as well along with JIT, It would be tricky to differentiate those two type of requests. Can you share the sample output for below three queries for both JIT type request and credential-less request 

1. select * from ars_requests where jbpmprocessinstanceid like '%<request_number>%';

2. select * from request_access where requestkey in (select requestkey from ars_requests where jbpmprocessinstanceid like '%<request_number>%');

3. select * from request_access_attrs where request_access_key in (select request_accesskey from request_access where requestkey in (select requestkey from ars_requests where jbpmprocessinstanceid like '%<request_number>%'));

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

NageshK
Saviynt Employee
Saviynt Employee

@suresh_ravuri @sk the only difference between JIT and non-JIT is the account config in accounts object. Here is the workflow I tried in 23.7 and it worked. It should work in older versions too. Please try and let me know if it worked. 

NageshK_0-1689087955961.png