Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Dynamic Attribute of type Multiple Select From SQL Query Config for Requestable Entitlement in ARS

TimoR
Regular Contributor
Regular Contributor

I have created a dynamic attribute "DepartmentEntitlements" of the type "Multiple Select From SQL Query" which returns multiple values.

I want to use this dynamic attribute in the entitlement type: "Config for Requestable Entitlement in ARS".

How can I do this?

I have multiple values of entitlement_valuekey in my dynamic attribute, for example: 4, 7, 23, 34

My idea of using it in "Config for Requestable Entitlement in ARS" was this:

 
ev.id IN (${DepartmentEntitlements})
 
But when I enter this, Saviynt throws an error: Invalid SQL syntax error
 
When I enter this with single quotes:
 
ev.id IN ('${DepartmentEntitlements}')
 
Saviynt does not complain, but this is incorrect and does not what I want it to do. Is there any way to work around the syntax check? Because it clearly is wrong in this case.
8 REPLIES 8

Raghu
All-Star
All-Star

@TimoR  try below

ev.ENTITLEMENT_VALUEKEY IN ('${DepartmentEntitlements}')


https://forums.saviynt.com/t5/identity-governance/not-show-entitlements-without-owner-in-ars/m-p/399...

 

https://forums.saviynt.com/t5/identity-governance/need-specific-entitlements-visible-to-specific-use...

 


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

PremMahadikar
All-Star
All-Star

Hi @TimoR ,

I see from your request that 'DepartmentEntitlement' has entitlement value= 4, 7, 23, 34

Your code is right!

ev.id IN ('${DepartmentEntitlements}')

 I have tested this, and it works fine: ev.id in ('${ent}')

PremMahadikar_1-1714659317643.png

PremMahadikar_2-1714659358824.png

What is incorrect? what is exactly needed here?

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

TimoR
Regular Contributor
Regular Contributor

It's not working for me most of the time. I have a dynamic attribute with a complex query that returns all entitlement valuekeys of all Active Directory accounts of users with the same manager as the requestee, to only suggest those.

Usually the dynamic attribute is empty, even though the same query for the same user ID as the requestee returns values in the data analyzer.

I'm wondering if there is a limit on how many values a dynamic attribute can handle? I cannot find any errors in the error log, though.

  • Please share logs
  • How many entitlements are listed here ?
  • Share logs when you select dynamic attribute on step 2

Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

The dynamic attribute DepartmentEntitlements has this default value:

SELECT entitlement_valuekey as id FROM entitlement_values ev 
  WHERE (ev.customproperty24 = -2147483646 OR ev.customproperty24 = -2147483640) 
  AND ev.status = 1 
  AND ev.entitlement_valuekey IN 
    (SELECT entitlement_valuekey FROM account_entitlements1 WHERE accountkey IN 
      (SELECT accountkey FROM accounts WHERE endpointkey = 4 AND status = 1 AND accountkey IN 
        (SELECT accountkey FROM user_accounts WHERE userkey IN 
          (SELECT userkey FROM users WHERE manager = 
            (SELECT manager FROM users WHERE userkey = ${requestee})))))

If I replace ${requestee} with a user ID and run it in data analyzer, it always runs fine and returns all entitlements of colleagues with the same manager.

But if I go to ARS and do Request Access for Others and try this for the same user, most of the time the dynamic attribute doesn't show any entitlement IDs or even if it does, doesn't show any entitlements.

I am attaching the log files of the time where I tried it with a user that gets over 1000 entitlement results with this query in data analyzer, but none in ARS.

Always use join . 
SELECT ev.entitlement_valuekey AS id
FROM entitlement_values ev
JOIN account_entitlements1 ae ON ev.entitlement_valuekey = ae.entitlement_valuekey
JOIN accounts a ON ae.accountkey = a.accountkey
JOIN user_accounts ua ON a.accountkey = ua.accountkey
JOIN users u ON ua.userkey = u.userkey
JOIN users u_manager ON u.manager = u_manager.userkey
WHERE ev.customproperty24 IN (-2147483646, -2147483640)
AND ev.status = 1
AND a.endpointkey = 4
AND a.status = 1
AND u_manager.userkey =  ${requestee}


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

rushikeshvartak
All-Star
All-Star

Please confirm Request form UI here ?

Modern / NEO ?


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

The UI we use is Neo, yes.