Add access actionable Analytics

[navneetv]

Hi Team,

could you please help or guide us with the blow analytics query? the use case is that. there is one attribute in Netsuite is CP23. If CP23 is true and NetSuite Account status is true then add okta group to user's OKTA account

NetSuite Endpoint 22

OKTA ENDPOINT:  11

entitlementtypekey = 8 (OKTA)

here is a query which we have created 

SELECT
a.NAME AS accName,
u.userkey AS userKey,
u.username AS Username,
a.accountkey AS acctKey,
'APP-OKTAPreview-Test01' AS 'Entitlement Value',
(
SELECT
entitlement_valuekey
FROM
entitlement_values
WHERE
entitlement_value = 'APP-OKTAPreview-Test01'
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.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 a.endpointkey = 11
AND ev.entitlement_value = 'APP-OKTAPreview-Test01'
)
AND a.accountkey IN (
SELECT
DISTINCT us.accountkey
FROM
user_accounts us,
accounts ac
WHERE
us.accountkey = ac.accountkey
AND ac.endpointkey = 22
AND (
ac.status = '1'
OR ac.status = 'Active'
)
AND ac.customproperty23 = 'true'
)

[pmahalle · Accepted solution]

Hi @navneetv ,

Can you try below query:

select
distinct u.username,
a.accountkey as acctKey,
'Provision Access' AS Default_Action_For_Analytics,
a.name as accName,
u.userkey as userKey,
'<Replace OKTA entitlement key here>' as entvaluekey
from
users u,
user_accounts ua,
accounts a
where
u.userkey = ua.userkey and
ua.accountkey = a.accountkey and
a.endpointkey=11 and
a.status in (1, 'Manually Provisioned') and
u.userkey in (select distinct(ua1.userkey) from user_accounts ua1, accounts a1 where ua1.accountkey = a1.accountkey and a1.endpointkey= 22 and a1.status in (1, 'Manually Provisioned') and a1.customproperty23='true')

[navneetv]

Hi @pmahalle  we added the below query because the task should generate for those users who are not part of a group, can we add to the above query so that it can check whether the user is a member of the group or not , if the user has group then add the task should not generate?

 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-OKTAPreview-Test01'
)

I have tested the above query that you have shared. we got message that it will take long time 

[pmahalle]

@navneetv , is it working fine in data analyzer? Also,  can you save the  analytics and try to trigger it through job RunAllAnalyticsJob

[navneetv]

@pmahalle thankyou for working on it.

not sure, what I am doing wrong. generally with this way task generates. but still, no add task was generated for Okta. could you please suggest what I did wrong here? why task not being genreated 

[pmahalle]

Hi @navneetv ,

Users for which you are expecting the Add Access task for OKTA group getting generated, can you check user have OKTA account with active state.

[rushikeshvartak]

• Please validate below
  • Account is active 
  • Entitlement is active.
  • Entitlement is not already assigned to account
  • task is already not in open/in progress state
  • Run report once manually action taken 
  • Did you added provision access action in allowed action in report config

[navneetv]

Hi @pmahalle @rushikeshvartak I tested with the new OKTA group however when marked "Provision Access" in a report, the task is still not generated.

Enttielment is active

User is active and not the part of group 

Active endpoint 

we have another OKTA add access report which is working and the task is being generated 

select
a.name as accName,
u.userkey as userKey,
u.username as Username,
a.accountkey as acctKey,
'<OKTA GROUPNAME>' as 'Entitlement Value',
(
select
entitlement_valuekey
from
entitlement_values
where
entitlement_value = '<OKTA GROUPNAME>'
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 u.email like '%@XYZ.com%'
and a.endpointkey = 11
and a.status = 1
and u.employeetype in (
'Contractor', 'Employee', 'Intern'
)
and a.customproperty53 = 'ACTIVE_DIRECTORY'
and a.customproperty54 = 'ZYX.com'
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 = '<OKTA GROUPNAME>'
)

[rushikeshvartak]

'Provision Access' as Default_Action_For_Analytics --> Remove this line and try 

[navneetv]

thankyou @rushikeshvartak @pmahalle   for working on it 

[rushikeshvartak]

Thanks kudos it