Actionable Analytic to disable secondary account

[DanielGamboa]

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!

[rushikeshvartak]

You can prepare analytics and create disable account task

[rushikeshvartak]

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;

[DanielGamboa]

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?

[rushikeshvartak · Accepted solution]

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;

[DanielGamboa · Accepted solution]

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

[DanielGamboa]

Thank you so much, Rushikesh! I'll test this in DEV and let you know the result!