Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
07/10/2024 01:52 PM
Hi,
We have the following scenario:
John Doe has two active directory accounts JD1 (Main Account on the main AD connection and endpoint) and JD2 (Secondary Account on a different AD connection and endpoint).
Both of the accounts should have access to the same entitlement ENT1, else we need to deprovision account JD2.
Is there a way to build a query to use in actionable analytic for this case?
Thanks!
Solved! Go to Solution.
07/10/2024 02:21 PM
You can prepare analytics and create disable account task
07/10/2024 02:36 PM
SELECT
JD1.account_name AS JD1_account_name,
JD1.entitlement_value AS JD1_entitlement_value,
JD1.accountkey AS JD1_accountkey,
JD2.account_name AS JD2_account_name,
JD2.entitlement_value AS JD2_entitlement_value,
JD2.accountkey AS JD2_accountkey,
JD2.endpointkey,
'disableAccount' AS Default_Action_For_Analytics
FROM (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value
FROM
accounts a
JOIN
account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD1' -- Replace with actual account name JD1
AND a.endpointkey = 1 -- Replace with actual endpoint key for JD1
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD1
LEFT JOIN (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value,
a.endpointkey
FROM
accounts a
JOIN
account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD2' -- Replace with actual account name JD2
AND a.endpointkey = 2 -- Replace with actual endpoint key for JD2
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD2 ON JD1.entitlement_value = JD2.entitlement_value
UNION
SELECT
JD1.account_name AS JD1_account_name,
JD1.entitlement_value AS JD1_entitlement_value,
JD1.accountkey AS JD1_accountkey,
JD2.account_name AS JD2_account_name,
JD2.entitlement_value AS JD2_entitlement_value,
JD2.accountkey AS JD2_accountkey,
JD2.endpointkey,
'disableAccount' AS Default_Action_For_Analytics
FROM (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value
FROM
accounts a
JOIN
account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD1' -- Replace with actual account name JD1
AND a.endpointkey = 1 -- Replace with actual endpoint key for JD1
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD1
RIGHT JOIN (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value,
a.endpointkey
FROM
accounts a
JOIN
account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD2' -- Replace with actual account name JD2
AND a.endpointkey = 2 -- Replace with actual endpoint key for JD2
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD2 ON JD1.entitlement_value = JD2.entitlement_value
WHERE
JD1.entitlement_value IS NULL OR JD2.entitlement_value IS NULL;
07/22/2024 05:54 AM
Hi,
I'm sorry I didn't explain myself correctly. This needs to be done for all accounts on JD2, not for one specific account. Do you have any thoughts?
Or maybe if easier, is it possible to disable all users accounts on JD2, if their account on JD1 does not have access to an specific entitlement?
07/22/2024 09:05 AM
SELECT
JD2.account_name AS JD2_account_name,
JD2.entitlement_value AS JD2_entitlement_value,
JD2.accountkey AS JD2_accountkey,
JD2.endpointkey,
'disableAccount' AS Default_Action_For_Analytics
FROM (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value,
a.endpointkey
FROM
accounts a
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD2'
AND a.endpointkey = 2
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD2
LEFT JOIN (
SELECT DISTINCT
a.name AS account_name,
a.accountkey,
ev.entitlement_value AS entitlement_value
FROM
accounts a
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE
a.name = 'JD1'
AND a.endpointkey = 1
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
) AS JD1 ON JD2.entitlement_value = JD1.entitlement_value
WHERE
JD1.entitlement_value IS NULL;
07/22/2024 09:44 AM
Thank you, Rushikesh! This was very helpful! I used parts of both of your suggested queries and built this one:
SELECT JD2.account_name,JD2.account_key as acctKey, JD2.endpointkey, JD2.UsernameEP2
FROM (SELECT distinct a.name AS account_name,a.accountkey as account_key, e.endpointkey, u.username as UsernameEP2
FROM accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae, entitlement_types et, endpoints e, users u
WHERE a.accountkey = ua.accountkey
AND ua.accountkey = ae.accountkey
AND u.userkey=ua.userkey
AND ae.entitlement_valuekey = ev.entitlement_valuekey
AND ev.entitlementtypekey=et.entitlementtypekey
AND et.endpointkey=e.endpointkey
AND e.endpointname='INSERT ENDPOINTNAME'
) AS JD2
LEFT JOIN (
SELECT a.name AS account_name,a.accountkey as account_key, e.endpointkey, U.USERNAME AS UsernameEP1
FROM users u,user_accounts ua,accounts a,endpoints e,account_entitlements1 ae1,entitlement_values ev
WHERE EV.ENTITLEMENT_VALUE = "INSERT ENTITLEMENT VALUE"
AND u.userkey=ua.userkey
AND ua.accountkey=a.accountkey
AND a.endpointkey=e.endpointkey
AND ua.accountkey=ae1.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey
AND e.endpointname = "AD_Dummy") AS JD1
ON JD2.UsernameEP2 = JD1.UsernameEP1
WHERE JD1.account_name is NULL
07/19/2024 11:07 AM
Thank you so much, Rushikesh! I'll test this in DEV and let you know the result!