Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners Click HERE | EMEA/APJ Click HERE |
12/18/2023 05:30 AM
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'
)
Solved! Go to Solution.
12/18/2023 06:54 AM
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')
12/18/2023 07:06 AM - edited 12/18/2023 07:10 AM
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
12/18/2023 07:29 AM
@navneetv , is it working fine in data analyzer? Also, can you save the analytics and try to trigger it through job RunAllAnalyticsJob
12/18/2023 08:00 AM - edited 12/18/2023 08:01 AM
@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
12/18/2023 09:00 AM
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.
12/18/2023 09:12 AM
12/18/2023 07:17 PM
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>'
)
12/18/2023 07:58 PM
'Provision Access' as Default_Action_For_Analytics --> Remove this line and try
12/18/2023 09:45 PM
thankyou @rushikeshvartak @pmahalle for working on it
12/18/2023 09:48 PM
Thanks kudos it