Custom Query Not working in Workflow

[AshishD]

Hi,

We have the following requirement. So we are maintaining a dataset which consists two columns: Column 1 consists of the SAV Role name and Column 2 consists of the endpoint name. So when a request is raised for an endpoint mentioned in the dataset, users who are owners of the corresponding SAV Role should receive the approval notification.

We have a custom query that fetches correct results in analytics (does not work in data analyser because of the sav role table). However, this query does not work in the Workflow.

NOTE : This workflow is used for Service Accounts

The error is : Exception occured while fetching requestid = java.lang.NullPointerException: Cannot invoke method get() on null object.

Query : SELECT u.userkey AS id FROM savroles sr, users u, user_savroles usr,ars_requests ar1, users um WHERE um.USERKEY=u.USERKEY and u.statuskey = 1 and usr.userkey= u.userkey and sr.rolekey = usr.rolekey and sr.ROLENAME = (select attribute2 from dataset_values where datasetkey = (select datasetkey from dataset where datasetname = 'AccountApprovers') and attribute1 in (select ep.endpointname from request_access_attrs raa, ars_requests ar, request_access ra, endpoints ep WHERE ar.REQUESTKEY = ra.REQUESTKEY and ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and raa.attribute_value = ep.endpointkey and ar.requestkey=${ARSREQUEST.id} and raa.attribute_name = 'ENDPOINT')) and ar1.requestkey=${ARSREQUEST.id}

If we remove the highlighted part of the query and hardcode the SAV Role name, then this works. But we are using the dataset so that in future we will have more applications with the same requirement, then we can use the same workflow and will just have to maintain the dataset.

The below query works but it would lead us to create multiple workflows for various SAV Roles for different endpoints : 

SELECT distinct u.userkey,u.firstname AS id FROM savroles sr, users u, request_access ra, user_savroles usr, users um WHERE um.USERKEY=u.USERKEY and u.statuskey = 1 and usr.userkey= u.userkey and sr.rolekey = usr.rolekey and sr.ROLENAME ='ROLE_SAP_Service_Account_Approvers' and ra.requestkey=${ARSREQUEST.id}

Any issue in the query? Any help is appreciated

[Amit_Malik]

Hi @AshishD , 

Savroles also have custom properties. Will it help if you store endpoint in one of the CP and not use datasets. I think your use case can be achieved with it.

Let me know !!

This forum confirms datasets are not exposed in workflows. https://forums.saviynt.com/t5/identity-governance/dataset-in-workflow/m-p/27945#M14974

And, alternative for you to use it in dynamic attribute query and then use the dynamic attribute in workflows

[AshishD]

Hi Amit,

I was breaking down the query to understand the problem. The following query below which is part of the main query, works: This uses the dataset table and it works. But in this case we will have to maintain several datasets for every new application that comes up with the same requirement and new workflows. So the below query isnt the requirement, just a trobleshooting step

SELECT distinct u.userkey,u.firstname AS id FROM savroles sr, users u, request_access ra, user_savroles usr, users um WHERE um.USERKEY=u.USERKEY and u.statuskey = 1 and usr.userkey= u.userkey and sr.rolekey = usr.rolekey and sr.ROLENAME =(select attribute2 from dataset_values where datasetkey = (select datasetkey from dataset where datasetname = 'AccountApprovers')) and ra.requestkey=${ARSREQUEST.id}

Fetching the endpointname via the request is causing the issue in the query.

Also, thanks for the workaround. But we need to rule out this option to go ahead with another approach.

Also, when a request is submitted for a particular endpoint, based on the endpointname the SAVRole is fetched.

Storing the endpointname in the SAVRole will be a reverse approach wherein we first submit the request but how do we fetch the SAVRole and it's owners?

[rushikeshvartak]

• inner query will not evaluate dynamic variable ${ARSREQUEST.id}

[AshishD]

Hi Rushi,

Thanks for the update, is there anyway we can calculate the endpointname in the query without explicitly mentioning the ${ARSREQUEST.id} in the inner query ? Any workaround?

[rushikeshvartak]

Simplify your logic keeping query in dynamic attribute (you can make it hidden)

[AshishD]

Hi,

We have a similar query wherein the instead of members of sav role, the members of an AD Group get approval notifications.

The query in workflow is similar and it's working there. Below is the query working in another application. But this query isn't working. Any idea?

I have hardcoded ep.endpointkey as well in the following query, but still does not work.

The query working in other application:

SELECT u.userkey FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts acc ON acc.accountkey = ua.accountkey join account_entitlements1 ae on acc.accountkey=ae.accountkey join entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey JOIN endpoints ep ON acc.endpointkey = ep.endpointkey AND ev.entitlement_value = (select attribute2 from dataset_values where datasetkey = (select datasetkey from dataset where datasetname = 'AccountApprovers') and attribute1 in (select ep.endpointname from request_access_attrs raa, ars_requests ar, request_access ra, endpoints ep WHERE ar.REQUESTKEY = ra.REQUESTKEY and ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and raa.attribute_value = ep.endpointkey and ar.requestkey=${ARSREQUEST.id} and raa.attribute_name = 'ENDPOINT')) and ep.endpointkey=4

[rushikeshvartak · Accepted solution]

Try below

SELECT u.userkey
FROM users u
JOIN user_savroles usr ON usr.userkey = u.userkey
JOIN savroles sr ON sr.rolekey = usr.rolekey
WHERE u.statuskey = 1
AND sr.ROLENAME = (
    SELECT attribute2 
    FROM dataset_values 
    WHERE datasetkey = (
        SELECT datasetkey 
        FROM dataset 
        WHERE datasetname = 'AccountApprovers'
    ) 
    AND attribute1 IN (
        SELECT ep.endpointname 
        FROM request_access_attrs raa
        JOIN ars_requests ar ON ar.REQUESTKEY = ra.REQUESTKEY
        JOIN request_access ra ON ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY
        JOIN endpoints ep ON raa.attribute_value = ep.endpointkey
        WHERE ar.requestkey = ${ARSREQUEST.id}
        AND raa.attribute_name = 'ENDPOINT'
    )
)

[AshishD]

Hi Rushi,

This worked. Just some syntax errors in the nested query in Joins but finally it worked. Thanks for the effort.

[Amit_Malik]

Have it in Dynamic attribute.

[NM]

Hi @AshishD , dynamic attribute is the only viable solution here.