Trigger AD task when new account pending task is detected for a specific endpoint

[Kundan960]

Hi All,

I'm trying to achieve one use case where i need to trigger task for Active directory upon detecting the pending task for a specific endpoint XYZ. I created a actionable analytics query to be able to find those tasks and upon Job run, it should create a task for a specific entitlement mentioned in he SQL query as shown below:

SELECT DISTINCT ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctKey,
a.name AS accName,
u.userKey,
'Provision Access' AS "Default_Action_For_Analytics",
NOW() AS "Date_Ran"
FROM
entitlement_values ev
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
INNER JOIN accounts a ON a.accountkey IN (
SELECT ua.accountkey
FROM user_accounts ua
INNER JOIN arstasks at ON at.userkey = ua.userkey
WHERE at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'endpointname' LIMIT 1)
AND at.status = 1
AND at.tasktype = 3
)
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN Users u ON u.userKey = ua.userkey
INNER JOIN arstasks at ON at.userkey = u.userKey AND at.accountname = a.name
WHERE
et.endpointkey = (SELECT endpointkey FROM endpoints WHERE ENDPOINTNAME = 'Active Directory' LIMIT 1)
AND ev.ENTITLEMENT_VALUE = 'CN=xxxxx,OU=xxxxx test OU,DC=xxxx,DC=xxxx,DC=net'
AND at.status = 1
AND at.tasktype = 3
AND at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'endpointname' LIMIT 1)
GROUP BY
ev.entitlement_valuekey,
ev.entitlement_value,
a.accountkey,
a.name,
u.userKey;

Expected outcome: The query should be able to detect new account tasks for the given endpoint in the above query and should be able to create task for AD upon job run.

Current issue: The query is unable to fetch those tasks and is unable to create task for AD for the given entitlement for AD.

Could you please provide your insights if the query needs to be refactored or is there anything needs to be changed in it.

Much thanks,

Kundan Pathak

[rushikeshvartak]

SELECT DISTINCT 
    ev.entitlement_valuekey AS entvaluekey,
    ev.entitlement_value,
    a.accountkey AS acctKey,
    a.name AS accName,
    u.userKey,
    'Provision Access' AS "Default_Action_For_Analytics",
    NOW() AS "Date_Ran"
FROM
    entitlement_values ev
    INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
    INNER JOIN accounts a ON a.accountkey = ua.accountkey
    INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
    INNER JOIN Users u ON u.userKey = ua.userkey
    INNER JOIN arstasks at ON at.userkey = u.userKey 
       AND at.accountname = a.name 
       AND at.status = 1 
       AND at.tasktype = 3
       AND at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Docusign - BPPR' LIMIT 1)
WHERE 
    et.endpointkey = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Active Directory' LIMIT 1)
    AND ev.ENTITLEMENT_VALUE = 'CN=xxxxx,OU=xxxxx test OU,DC=xxxx,DC=xxxx,DC=net'
GROUP BY 
    ev.entitlement_valuekey,
    ev.entitlement_value,
    a.accountkey,
    a.name,
    u.userKey;

[Kundan960]

Hi @rushikeshvartak ,

i will check this out and get back shortly.

Thanks,

Kundan Pathak

[NM]

Hi @Kundan960, you can use entitlement with new account feature instead of using analytics.

When they request for an account in endpoint xyz after approval task will be created for specific entitlement.

[Kundan960]

Hi @NM ,

We have some different requirement to trigger this  only for specific type of users hence we are proceeding with actionable analytics and not via the option of Enntitltment with new account feature.

[Kundan960]

hi @rushikeshvartak ,

This query throws error with the message: 

Error : Unknown column 'ua.accountkey' in 'on clause' 

i tried to debug the condition by replacing the query below: 

SELECT DISTINCT
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctKey,
a.name AS accName,
u.userkey AS userKey,
'Provision Access' AS "Default_Action_For_Analytics",
NOW() AS "Date_Ran"
FROM
entitlement_values ev
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
INNER JOIN user_accounts ua ON ua.entitlement_valuekey = ev.entitlement_valuekey -- Corrected JOIN condition
INNER JOIN accounts a ON a.accountkey = ua.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
INNER JOIN arstasks at ON at.userkey = u.userkey
AND at.accountname = a.name
AND at.status = 1
AND at.tasktype = 3
AND at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Docusign - BPPR' LIMIT 1)
WHERE
et.endpointkey = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Active Directory' LIMIT 1)
AND ev.entitlement_value = 'CN=xxxxx,OU=xxxxx test OU,DC=xxxx,DC=xxxx,DC=net'
GROUP BY
ev.entitlement_valuekey,
ev.entitlement_value,
a.accountkey,
a.name,
u.userkey;

this query also doesn't detect the new account tasks and fails with message: Error : Unknown column 'ua.entitlement_valuekey' in 'on clause'

Could you please help on this.

Regards,

Kundan Pathak

[NM]

Hi @Kundan960 user accounts table doesn't contain entitlement value key column .. if you want to check entitlement use account_entitlement1 table.

[Kundan960]

Hi @NM / @rushikeshvartak , i ran separate queries to detect the tasks and recognize entitlemtn including the account_entitlement1 table:

Query to detect tasks :

SELECT
at.userkey AS userKey,
at.accountname AS accountName,
at.status AS taskStatus,
at.tasktype AS taskType,
at.endpoint AS taskEndpoint
FROM
arstasks at
WHERE
at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'endpointname' LIMIT 1);

Query to fetch entitlement value:

SELECT
ev.entitlement_valuekey,
ev.entitlement_value,
et.endpointkey
FROM
entitlement_values ev
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
WHERE
et.endpointkey = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Active Directory' LIMIT 1)
AND ev.entitlement_value = 'CN=xxx,xxx,xxx,xxx,DC=net'
LIMIT 10; -- Limit results to 10 for quick verification

Combined query:

SELECT DISTINCT
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctKey,
a.name AS accName,
u.userkey AS userKey,
'Provision Access' AS "Default_Action_For_Analytics",
NOW() AS "Date_Ran"
FROM
entitlement_values ev
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
INNER JOIN account_entitlement1 ae ON ae.entitlement_valuekey = ev.entitlement_valuekey
INNER JOIN accounts a ON a.accountkey = ae.accountkey
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
INNER JOIN arstasks at ON at.userkey = u.userkey
AND at.accountname = a.name
AND at.status = 1
AND at.tasktype = 3
AND at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'endpointname' LIMIT 1)
WHERE
et.endpointkey = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Active Directory' LIMIT 1)
AND ev.entitlement_value = 'CN=xxx,xxx,xxx,xxx,DC=net'
GROUP BY
ev.entitlement_valuekey,
ev.entitlement_value,
a.accountkey,
a.name,
u.userkey;

Combined query doesn't return any results even though few task are present in the Pending Tasks page.

Please advice.

Much thanks,

Kundan Pathak

[rushikeshvartak]

SELECT DISTINCT ev.entitlement_valuekey AS entvaluekey,
                ev.entitlement_value,
                a.accountkey            AS acctKey,
                a.name                  AS accName,
                u.userkey               AS userKey,
                'Provision Access'      AS "Default_Action_For_Analytics",
                Now()                   AS "Date_Ran"
FROM   entitlement_values ev
       INNER JOIN entitlement_types et
               ON ev.entitlementtypekey = et.entitlementtypekey
       INNER JOIN account_entitlements1 ae
               ON ae.entitlement_valuekey = ev.entitlement_valuekey
       INNER JOIN accounts a
               ON a.accountkey = ae.accountkey
       INNER JOIN user_accounts ua
               ON ua.accountkey = ae.accountkey
       INNER JOIN users u
               ON u.userkey = ua.userkey
       INNER JOIN arstasks at
               ON at.userkey = u.userkey
                  AND at.accountname = a.name
                  AND at.status = 1
                  AND at.tasktype = 3
                  AND at.endpoint = (SELECT endpointkey
                                     FROM   endpoints
                                     WHERE  endpointname = 'Docusign - BPPR'
                                     LIMIT  1)
WHERE  et.endpointkey = (SELECT endpointkey
                         FROM   endpoints
                         WHERE  endpointname = 'Active Directory'
                         LIMIT  1)
       AND ev.entitlement_value =
           'CN=xxxxx,OU=xxxxx test OU,DC=xxxx,DC=xxxx,DC=net'
GROUP  BY ev.entitlement_valuekey,
          ev.entitlement_value,
          a.accountkey,
          a.name,
          u.userkey; 

[Kundan960]

Hi @rushikeshvartak ,

Thanks for the query. It is still unable to detect tasks for the the same. Screenshot for your review:

Pending tasks for endpoint:

Query when run in the Analytics:

Looks like the query is not able to filter the tasks or perhaps not able to recognize the endpoint inspite of correct AD group and endpointname.

Thanks,

Kundan Pathak

[This message has been edited by moderator to mask sensitive information]

[rushikeshvartak]

You need to fix. Break query in smaller chunks and find issue

[rushikeshvartak · Accepted solution]

• Validate endpoint names
• Validate task type and status

SELECT DISTINCT
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctKey,
a.name AS accName,
u.userkey AS userKey,
'Provision Access' AS "Default_Action_For_Analytics",
NOW() AS "Date_Ran"
FROM
entitlement_values ev
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
INNER JOIN account_entitlement1 ae ON ae.entitlement_valuekey = ev.entitlement_valuekey
INNER JOIN accounts a ON a.accountkey = ae.accountkey
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
INNER JOIN arstasks at ON at.userkey = u.userkey
AND at.accountname = a.name
AND at.status = 1
AND at.tasktype = 3
AND at.endpoint = (SELECT endpointkey FROM endpoints WHERE endpointname = 'endpointname' LIMIT 1)
WHERE
et.endpointkey = (SELECT endpointkey FROM endpoints WHERE endpointname = 'Active Directory' LIMIT 1)
AND ev.entitlement_value = 'CN=xxx,xxx,xxx,xxx,DC=net'
GROUP BY
ev.entitlement_valuekey,
ev.entitlement_value,
a.accountkey,
a.name,
u.userkey;