User Update rule - Advanced Query issue

[Abhinav]

'Hi All,

I am facing issue in Advanced Query of User update rule, I was referring to post Solved: Department Change Notification - Saviynt Forums - 74636.

In my scenario, I have to send Email to users whenever CP40 updates to 'Yes' and User is having account in one of the below THREE applications (with specific Ent value/CP1/ Ent type, defined below): -

App1 (when user have ev.entitlement_value IN ('ENt1', 'ENt2', 'ENt3') and ENTITLEMENTType Name = 'Type1')

App2 (when ev.CP1 IN ('Access'))

App3 (when ev.description like 'High'))

I am trying to use below query:- (##a.customproperty40 isupdated## and

account in one of the below THREE applications (with specific Ent value/CP1/ Ent type, defined below): -

App1 (when user have ev.entitlement_value IN ('ENt1', 'ENt2', 'ENt3') and ENTITLEMENTType Name = 'Type1')

App2 (when ev.CP1 IN ('Access'))

App3 (when ev.description like 'High'))

I am trying to use below query:- (##a.customproperty40 isupdated## AND (a.id in (select uacc.userkey from Accounts acc, User_accounts uacc, Users u, Endpoints e, Account_entitlements1 ae, entitlement_types et, entitlement_values ev where acc.id=uacc.accountkey and e.id=acc.endpointkey and acc.id=ae.accountkey and ev.id=ae.entitlement_valuekey and ev.id=et.ENTITLEMENTTYPEKEY and acc.status IN ('1', 'Manually Provisioned') AND ((e.ENDPOINTNAME = 'App1' AND et.ENTITLEMENTNAME = 'Type1' AND ev.entitlement_value IN ('Ent1', 'Ent2', 'Ent3')) OR (e.ENDPOINTNAME = 'App2' AND ev.CUSTOMPROPERTY1 IN ('Access')) OR (e.ENDPOINTNAME = 'App3' AND ev.description LIKE '%High%')))))

I tried to update the CP40 value of user who are meeting the conditions. But no user rule is getting triggered. 

I tried to trim down condition and run this:- ((##a.customproperty40 isupdated##) and a.customproperty40 = 'Yes' AND (a.id in (select uacc.userkey from Accounts acc, User_accounts uacc, Users u, Endpoints e, Account_entitlements1 ae, entitlement_types et, entitlement_values ev where acc.id=uacc.accountkey and e.id=acc.endpointkey and acc.id=ae.accountkey and ev.id=ae.entitlement_valuekey and ev.id=et.ENTITLEMENTTYPEKEY and acc.status IN ('1', 'Manually Provisioned') AND ((e.ENDPOINTNAME = 'App1' AND et.ENTITLEMENTNAME = 'Type1' AND ev.entitlement_value IN ('Ent1', 'Ent2', 'Ent3')) OR (e.ENDPOINTNAME = 'App2' AND ev.CUSTOMPROPERTY1 IN ('Access')) OR (e.ENDPOINTNAME = 'App3' AND ev.description LIKE '%High%')))))

I tried to update the CP40 value of user who are meeting the conditions. But no user rule is getting triggered. 

I tried to trim down condition and run this:- ((##a.customproperty40 isupdated##) and a.customproperty40 = 'Yes' and a.statuskey=1 and a.id in (select ua.userkey from Accounts acc,User_accounts ua,Account_entitlements1 ae,Entitlement_values ev, Endpoints e where
acc.id=ua.accountkey
and acc.id=ae.accountkey
and ev.id=ae.entitlement_valuekey
and acc.endpointkey=e.id
and acc.status = 1 and ev.entitlement_value like 'Ent1' and e.endpointname IN ('App1','App2','App3')))

but still not working.

Any help/suggestion is much appreciated. 

I am trying to use accounts_entitlements1 table in query. is this exposed?

 any suggestions plz

Thanks,

[This post has been edited by a Moderator. We discourage the @ mention of other forum users or employees unless they have already involved themselves on the forum post.]

[Amit_Malik]

Saviynt doc - https://docs.saviyntcloud.com/bundle/KBAs/page/Content/Obtaining-the-attribute-details-from-tables-o...

entitlement_types et, entitlement_values

These two tables in your query are not as per HQL syntax. Refer HQL class name in above doc

[Abhinav]

Thanks @Amit_Malik , this was one of the issue with the query.

[rushikeshvartak]

Did you validated logs after updating user ? Share logs in text format

[stalluri]

@Abhinav 

Account_entitlements1 is exposed in the Technical and User Update rules via HQL class

((##a.customproperty40 isupdated##) 
AND a.customproperty40 = 'Yes' 
AND a.statuskey = 1 
AND a.id IN (
    SELECT ua.userkey 
    FROM Accounts acc
    JOIN User_accounts ua ON acc.id = ua.accountkey
    JOIN Account_entitlements1 ae ON acc.id = ae.accountkey
    JOIN Entitlement_values ev ON ev.id = ae.entitlement_valuekey
    JOIN Endpoints e ON acc.endpointkey = e.id
    WHERE acc.status IN ('1', 'Manually Provisioned')
    AND (
        (e.endpointname = 'App1' 
         AND ev.entitlement_value IN ('Ent1', 'Ent2', 'Ent3') 
         AND ev.entitlement_type = 'Type1') 
        OR 
        (e.endpointname = 'App2' 
         AND ev.CUSTOMPROPERTY1 = 'Access') 
        OR 
        (e.endpointname = 'App3' 
         AND ev.description LIKE '%High%')
    )
))

Test for Each Application: Start by testing each application condition individually. Use this query for App1 to ensure the rule is working for that application:

((##a.customproperty40 isupdated##) 
AND a.customproperty40 = 'Yes' 
AND a.id IN (
    SELECT ua.userkey 
    FROM Accounts acc
    JOIN User_accounts ua ON acc.id = ua.accountkey
    JOIN Account_entitlements1 ae ON acc.id = ae.accountkey
    JOIN Entitlement_values ev ON ev.id = ae.entitlement_valuekey
    JOIN Endpoints e ON acc.endpointkey = e.id
    WHERE acc.status IN ('1', 'Manually Provisioned')
    AND e.endpointname = 'App1' 
    AND ev.entitlement_value IN ('Ent1', 'Ent2', 'Ent3')
    AND ev.entitlement_type = 'Type1'
))