deprovision access based on user attribute

[Roua]

Hello,
we need to assign the role_org "entitelments" based on user.employeeclass and we did that by creating technical rules to check the users.employeeclass and assign the right entitlement, however, if the employeeclass is updated for example from 123 to 678. the account will get the new entitlemnts for 678 but the old entitlements for 123 won't be removed, how do i ensure it gets removed?
because if i add deprovision access action in the technical rule i think this will remove all the access the account has right? and the new one for the 678 wouldn't get assigned? cause user should only have one ent only

also is there another way to assign the entitlements for example with a config file that would say if users.employeeclass = "123" then assign to this rule. ?
since i also have a condition where for some users the role_org in not in the employeeclass and i have to compare user attributes if they match with entitelments attributes for example: 
users.CUSTOMPROPERTY4 == entitlements.customProperty8
and users.ORGUNITID == entitlements.customProperty9

is there any other way to do it other than if statements or creating many technical rules since there are A LOT ?

Thank you!

[rushikeshvartak]

• Use actionable report

[Roua]

@rushikeshvartak 
yes thank you i was already on it and i tried the following in data analyzer it worked: 

SELECT 
    u.userKey AS userKey,
    ev.entitlement_valuekey AS entvaluekey,
    a.accountkey AS acctKey,
    a.name AS accName,
    'Provision Access' AS Default_Action_For_Analytics
FROM 
    users u
JOIN 
    user_accounts ua ON u.userkey = ua.userkey
JOIN 
    accounts a ON ua.accountkey = a.accountkey
JOIN 
    entitlement_values ev ON (
      
        ev.entitlement_value = CASE 
            WHEN u.employeeclass IN ('123') THEN 'xxxxxxxxxxxxxxxxxxx'
            WHEN u.employeeclass IN ('321') THEN 'xxxxxxxxxxxxxxxxx'
            
        END
        OR
        
        (ev.customProperty8 = u.CUSTOMPROPERTY4 AND ev.customProperty9 = u.DEPARTMENTNUMBER)
    );

but what about removing the entitelments when the employeeclass changes or the other condition?

also would the actionable report be triggered automatically when a new account is created for an exsiting user? or how do we make it automatically triggered when the employeeclass is updated or a new account is created, also does it create an add access task that would be excuted using the provisioning job ? 

[rushikeshvartak]

• also would the actionable report be triggered automatically when a new account is created for an exsiting user? or how do we make it automatically triggered when the employeeclass is updated or a new account is created, also does it create an add access task that would be excuted using the provisioning job ?  - Run Job in triggerchain
• Deprovisioning - Create similar analytics report for deprovision or add union in existing query - read latest value from userhistory table

[Roua]

@rushikeshvartak 
you mean to put the analytics job and the provisioning job in one chain job? there is no way for it to trigger a task creation automatically like user update rules do ? 

- can you please provide me with a sample of the UNION query that would be used here ? thank you so much 

[rushikeshvartak]

you mean to put the analytics job and the provisioning job in one chain job? there is no way for it to trigger a task creation automatically like user update rules do ?  - No

• Sample query

SELECT 
    u.userKey AS userKey,
    ev.entitlement_valuekey AS entvaluekey,
    a.accountkey AS acctKey,
    a.name AS accName,
    'Provision Access' AS Default_Action_For_Analytics
FROM 
    users u
JOIN 
    user_accounts ua ON u.userkey = ua.userkey
JOIN 
    accounts a ON ua.accountkey = a.accountkey
JOIN 
    entitlement_values ev ON (
      
        ev.entitlement_value = CASE 
            WHEN u.employeeclass IN ('123') THEN 'xxxxxxxxxxxxxxxxxxx'
            WHEN u.employeeclass IN ('321') THEN 'xxxxxxxxxxxxxxxxx'
            
        END
        OR
        
        (ev.customProperty8 = u.CUSTOMPROPERTY4 AND ev.customProperty9 = u.DEPARTMENTNUMBER)
    )
	UNION
	SELECT 
    u.userKey AS userKey,
    ev.entitlement_valuekey AS entvaluekey,
    a.accountkey AS acctKey,
    a.name AS accName,
    'Deprovision Access' AS Default_Action_For_Analytics
FROM 
    users u
JOIN 
    user_accounts ua ON u.userkey = ua.userkey
JOIN 
    accounts a ON ua.accountkey = a.accountkey
JOIN 
    entitlement_values ev ON (
      
        ev.entitlement_value = CASE 
            WHEN u.employeeclass IN ('123') THEN 'xxxxxxxxxxxxxxxxxxx'
            WHEN u.employeeclass IN ('321') THEN 'xxxxxxxxxxxxxxxxx'
            
        END
        OR
        
        (ev.customProperty8 = u.CUSTOMPROPERTY4 AND ev.customProperty9 = u.DEPARTMENTNUMBER)
    )

[Roua]

Hello @rushikeshvartak ,
This worked for me and to test it i assigned a specifiy user,

SELECT 
    u.userKey,
    u.employeeclass,
    ev.entitlement_valuekey AS entvaluekey,
    a.accountkey AS acctKey,
    a.name AS accName,
ev.entitlement_value AS entitlementValue,
    'Provision Access' AS Default_Action_For_Analytics
FROM 
    users u
JOIN 
    user_accounts ua ON u.userkey = ua.userkey
JOIN 
    accounts a ON ua.accountkey = a.accountkey
JOIN 
    entitlement_values ev ON (
        ev.entitlement_value = CASE 
            WHEN u.employeeclass IN ('xx') THEN 'xxxxxxxxxx'
        END
        OR (ev.customProperty8 = u.CUSTOMPROPERTY4 AND ev.customProperty9 = u.DEPARTMENTNUMBER)
    )
WHERE 
    u.username = 'xxxxxxxx'  
    AND ev.entitlement_value NOT IN (

        SELECT ev.entitlement_value 
        FROM account_entitlements1 ae 
        JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
        WHERE ae.accountkey = a.accountkey
    )

UNION


SELECT 
    u.userKey,
    u.employeeclass,
    ev.entitlement_valuekey AS entvaluekey,
    a.accountkey AS acctKey,
    a.name AS accName,
ev.entitlement_value AS entitlementValue,
    'Deprovision Access' AS Default_Action_For_Analytics
FROM 
    users u
JOIN 
    user_accounts ua ON u.userkey = ua.userkey
JOIN 
    accounts a ON ua.accountkey = a.accountkey
JOIN 
    account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN 
    entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
WHERE 
    u.username = 'xxxxx'
    AND ev.entitlement_value NOT IN (
       
        SELECT entitlement_value

        FROM entitlement_values ev_new
        WHERE ev_new.entitlement_value = CASE 
            WHEN u.employeeclass IN ('xxxxx') THEN 'xxxxxxx'
        END
        OR (ev_new.customProperty8 = u.CUSTOMPROPERTY4 AND ev_new.customProperty9 = u.DEPARTMENTNUMBER)
    );

 i tried it in data analyzer and i did in analytics preview and was correct

but when i run the job the analytics is not getting executed: 

in applications i have assigned AD and LDAP already.

analytics:

do you have an idea why it didn't work or if i skipped a point ? 

[rushikeshvartak]

• Delete all history from run history
• keep base count =1