Using dynamic input declaration in Actionable Analytics

[kushalpatadia]

Hi Team,

We have enabled Saviynt for Saviynt REST configuration from Global Config and imported SAV Roles as entitlements for that endpoint.

We are trying to provision SAV role as entitlement to a particular user via actionable analytics (SQL query) and when the report is run, it should provide a popup where username can be manually selected from dropdown and for that specific selected user, the Provision Access action should create Add Access task for the mentioned SAV Role.

Attaching the query we are trying to use within the analytics but when trying to save the report, it gives below error.

We have included all these columns within the query but still get the error.

Any idea on how can we pass dynamic input declaration in actionable analytics? Kindly check the query and advise if we are doing something wrong.

Best Regards,

Kushal

[rushikeshvartak]

Use below query

SELECT DISTINCT u.username AS 'UNAME',
                u.firstname,
                u.lastname,
                ep.endpointname         AS endpoint_name,
                ev.entitlement_value    AS 'ENVAL',
                Now()                   AS timestamp,
                ev.entitlement_valuekey AS entvaluekey,
                acc.accountkey          AS acctkey,
                acc.NAME                AS accName,
                u.userkey               AS userKey
FROM            users u
INNER JOIN      user_accounts ua
ON              u.userkey=ua.userkey
INNER JOIN      accounts acc
ON              ua.accountkey=acc.accountkey
INNER JOIN      account_entitlements1 ae1
ON              acc.accountkey=ae1.accountkey
INNER JOIN      endpoints ep
ON              acc.endpointkey=ep.endpointkey
INNER JOIN      entitlement_types et
ON              ep.endpointkey=et.endpointkey
INNER JOIN      entitlement_values ev
ON              et.entitlementtypekey=ev.entitlementtypekey
WHERE           ep.endpointname='Saviynt'
AND             et.entitlementname='SAVRoles'
AND             ev.entitlement_value='ROLE_SAV_AUDITOR_READONLY'
AND             u.username IN (${UNAME});

[kushalpatadia]

Hi @rushikeshvartak ,

Getting the same error even after using your query. No error in logs as well. Attached the same.

[rushikeshvartak]

Please share configuration screenshot also make sure query is visible

[kushalpatadia]

@rushikeshvartak please find attached configuration screenshot.

[rushikeshvartak]

SELECT DISTINCT u.username AS 'UNAME',
                u.firstname,
                u.lastname,
                ep.endpointname         AS endpoint_name,
                ev.entitlement_value    AS 'ENVAL',
                Now()                   AS timestamp,
                ev.entitlement_valuekey AS entvaluekey,
                acc.accountkey          AS acctKey,
                acc.NAME                AS accName,
                u.userkey               AS userKey
FROM            users u
INNER JOIN      user_accounts ua
ON              u.userkey=ua.userkey
INNER JOIN      accounts acc
ON              ua.accountkey=acc.accountkey
INNER JOIN      account_entitlements1 ae1
ON              acc.accountkey=ae1.accountkey
INNER JOIN      endpoints ep
ON              acc.endpointkey=ep.endpointkey
INNER JOIN      entitlement_types et
ON              ep.endpointkey=et.endpointkey
INNER JOIN      entitlement_values ev
ON              et.entitlementtypekey=ev.entitlementtypekey
WHERE           ep.endpointname='Saviynt'
AND             et.entitlementname='SAVRoles'
AND             ev.entitlement_value='ROLE_SAV_AUDITOR_READONLY'
AND             u.username IN (${UNAME});

[kushalpatadia]

@rushikeshvartak still getting the same error after using the new query. Attaching the config screenshot.

The only difference from your previous query and new query was 'acctKey' declaration I believe.

[rushikeshvartak]

Remove dynamic part and validate

[kushalpatadia]

@rushikeshvartak the query attached in the post which we were trying and the query you shared both work when we remove the dynamic input part.

As a functionality, does saviynt support use of such dynamic declarations within the actionable analytics SQL query?

[rushikeshvartak]

Saviynt does not support dynamic declarations within the actionable analytics SQL query

Please raise idea ticket