Adding a Entitlement with Analytics report by using a condition

[navneetv]

Hi Everyone,
need your help as we have a use case, where we want to assign "ABC" role which is in okta endpoint if user is part of Xyz role which is in AD endpoint 

i have tried to built the query but not working 

select 
  a.name as accName, 
  u.userkey as userKey, 
  u.username as Username, 
  a.accountkey as acctKey, 
  'APP-OKTA-Planit' as 'Entitlement Value', 
  (
    select 
      entitlement_valuekey 
    from 
      entitlement_values 
    where 
      entitlement_value = 'APP-ABC' 
      and entitlementtypekey = 8 
      and status = 1
  ) as entvaluekey, 
  'Provision Access' as Default_Action_For_Analytics 
from 
  accounts a, 
  users u, 
  user_accounts ua 
where 
  u.userkey = ua.userkey 
  and ua.accountkey = a.accountkey 
  and u.statuskey = 1 
  and a.endpointkey = 11 
  and a.status = 1 
  and a.accountkey not in (
    select 
      ae1.accountkey 
    from 
      account_entitlements1 ae1, 
      entitlement_values ev 
    where 
      ae1.entitlement_valuekey = ev.entitlement_valuekey 
      and ev.entitlementtypekey = 8 
      and ev.entitlement_value = 'APP-ABC'
  ) 
  and u.userkey in (
    select 
      distinct(ua1.userkey) 
    from 
      user_accounts ua1, 
      accounts a1, 
    where 
      ua1.accountkey = a1.accountkey 
      and a1.endpointkey = 12 
      and a1.status in (1, 'Manually Provisioned') 
      and a1.accountkey in (
        select 
          ae1.accountkey 
        from 
          account_entitlements1 ae1, 
          entitlement_values ev 
        where 
          ae1.entitlement_valuekey = ev.entitlement_valuekey 
          and ev.entitlementtypekey = 13 
          and ev.entitlement_value = 'CN=XYZ,OU=TEST OU,OU=Group,OU=ITLab,DC=office,TesTITlab,DC=com'
      )
  )

[rushikeshvartak · Accepted solution]

SELECT a.NAME                  AS accName,
       u.userkey               AS userKey,
       u.username              AS Username,
       a.accountkey            AS acctKey,
       'APP-OKTA-Planit'       AS Entitlement_Value,
       (SELECT entitlement_valuekey
        FROM   entitlement_values
        WHERE  entitlement_value = 'APP-ABC'
               AND entitlementtypekey = 8
               AND status = 1) AS entvaluekey,
       'Provision Access'      AS Default_Action_For_Analytics
FROM   accounts a
       JOIN user_accounts ua
         ON ua.accountkey = a.accountkey
       JOIN users u
         ON u.userkey = ua.userkey
WHERE  u.statuskey = 1
       AND a.endpointkey = 11
       AND a.status = 1
       AND a.accountkey NOT IN (SELECT ae1.accountkey
                                FROM   account_entitlements1 ae1
                                       JOIN entitlement_values ev
                                         ON
               ae1.entitlement_valuekey = ev.entitlement_valuekey
                                WHERE  ev.entitlementtypekey = 8
                                       AND ev.entitlement_value = 'APP-ABC')
       AND u.userkey IN
               (SELECT DISTINCT ua1.userkey
                         FROM   user_accounts ua1
                                JOIN accounts a1
                                  ON ua1.accountkey = a1.accountkey
                         WHERE  a1.endpointkey = 12
                                AND a1.status IN ( 1, 'Manually Provisioned' )
                                AND a1.accountkey IN
                                    (SELECT ae1.accountkey
                                     FROM   account_entitlements1 ae1
                                            JOIN entitlement_values ev
                                              ON ae1.entitlement_valuekey
                                                 =
                                                 ev.entitlement_valuekey
                                     WHERE  ev.entitlementtypekey = 13
                                            AND ev.entitlement_value =
               'CN=XYZ,OU=TEST OU,OU=Group,OU=ITLab,DC=office,TesTITlab,DC=com'
                            )) 

[stalluri]

@navneetv 
Instead of passing entitlement value, try passing entitlement key.

  'XXX' as 'entvaluekey', 

[NM]

@navneetv you can use entitlement entitlement map feature so once user get assigned to xyz role it will automatically be added to abc role of okta endpoint.