Analytics : Control execution is taking longer than expected

[Joon]

I am trying to retrieve data but Analytics throws the error below and never works.

 Alert: Control execution is taking longer than expected. You will be notified when the result is ready

I've been looking some in forum but nothing works for me.

can you please take a look ?

Here's my query in v23.12

select
u.username,
ar.requestdate,
Substring_index(ar.jbpmprocessinstanceid, '.', - 1) AS 'Request ID',
ar.endpointascsv AS 'Endpoint',
ev.displayname Role,
u.customproperty17 as name,
u.email AS 'User Email', u.jobdescription,
DATE_ADD((aa.submitdate), INTERVAL 9 HOUR) AS 'start of Owner Approval',
DATE_ADD((aa.approvedate), INTERVAL 9 HOUR) AS 'date of OwnerApproval',
CASE
WHEN ar.requesttype = 1 THEN 'Add Access'
WHEN ar.requesttype = 2 THEN 'Remove Access'
WHEN ar.requesttype = 3 THEN 'New Account'
WHEN ar.requesttype = 4 THEN 'Enterprise Role Request'
WHEN ar.requesttype = 12 THEN 'Update Account'
WHEN ar.requesttype = 19 THEN 'Update User'
ELSE ar.requesttype
END AS 'Request Type',
CASE
WHEN aa.STATUS = 1 THEN 'Pending Approval'
WHEN aa.STATUS = 2 THEN 'Approved'
WHEN aa.STATUS = 3 THEN 'Rejected'
WHEN aa.STATUS = 4 THEN 'Escalated'
WHEN aa.STATUS = 6 THEN 'Discontinued'
ELSE aa.STATUS
END AS 'REQUEST APPROVAL STATE',
u1.customproperty17 AS 'Approver',
aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
ev.customproperty20 AS 'Applications',
ar.comments as requestcomment,
ra.comments as approvalcomment
FROM
ars_requests ar
INNER JOIN request_access ra ON ar.requestkey = ra.requestkey
INNER JOIN access_approvers aa ON aa.request_access_key = ra.request_accesskey
INNER JOIN entitlement_values ev ON ev.entitlement_valuekey = ra.accesskey
INNER JOIN users u ON ra.userkey = u.userkey
INNER JOIN users u1 ON aa.approverkey = u1.userkey AND aa.approveby = u1.userkey
INNER JOIN user_accounts ua ON u.userkey = ua.userkey
INNER JOIN accounts a ON a.accountkey = ua.accountkey
WHERE
u.statuskey = 1
AND ev.customproperty20='TMS'
AND a.accountkey in (
SELECT
ae1.accountkey
FROM
ACCOUNTS A
INNER JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
INNER JOIN account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
INNER JOIN Entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
WHERE
e.displayname = 'authweb'
AND ev.STATUS = 1
AND ev.customproperty20 = 'TMS'
AND a.STATUS IN ('1', 'manually provisioned')
)

[CR]

can you try below

select
u.username,
ar.requestdate as 'requetdata',
Substring_index(ar.jbpmprocessinstanceid, '.', - 1) AS 'Request ID',
ar.endpointascsv AS 'Endpoint',
ev.displayname Role,
u.customproperty17 as name,
u.email AS 'User Email', u.jobdescription,
DATE_ADD((aa.submitdate), INTERVAL 9 HOUR) AS 'start of Owner Approval',
DATE_ADD((aa.approvedate), INTERVAL 9 HOUR) AS 'datajd of OwnerApproval',
CASE
WHEN ar.requesttype = 1 THEN 'Add Access'
WHEN ar.requesttype = 2 THEN 'Remove Access'
WHEN ar.requesttype = 3 THEN 'New Account'
WHEN ar.requesttype = 4 THEN 'Enterprise Role Request'
WHEN ar.requesttype = 12 THEN 'Update Account'
WHEN ar.requesttype = 19 THEN 'Update User'
ELSE ar.requesttype
END AS 'Request Type',
CASE
WHEN aa.STATUS = 1 THEN 'Pending Approval'
WHEN aa.STATUS = 2 THEN 'Approved'
WHEN aa.STATUS = 3 THEN 'Rejected'
WHEN aa.STATUS = 4 THEN 'Escalated'
WHEN aa.STATUS = 6 THEN 'Discontinued'
ELSE aa.STATUS
END AS 'REQUEST APPROVAL STATE',
u1.customproperty17 AS 'Approver',
aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
ev.customproperty20 AS 'Applications',
ar.comments as requestcomment,
ra.comments as approvalcomment
FROM
ars_requests ar
INNER JOIN request_access ra ON ar.requestkey = ra.requestkey
INNER JOIN access_approvers aa ON aa.request_access_key = ra.request_accesskey
INNER JOIN entitlement_values ev ON ev.entitlement_valuekey = ra.accesskey
INNER JOIN users u ON ra.userkey = u.userkey
INNER JOIN users u1 ON aa.approverkey = u1.userkey AND aa.approveby = u1.userkey
INNER JOIN user_accounts ua ON u.userkey = ua.userkey
INNER JOIN accounts a ON a.accountkey = ua.accountkey
WHERE
u.statuskey = 1
AND ev.customproperty20='TMS'
AND a.accountkey in (
SELECT
ae1.accountkey
FROM
ACCOUNTS A
INNER JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
INNER JOIN account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
INNER JOIN Entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
WHERE
e.displayname = 'authweb'
AND ev.STATUS = 1
AND ev.customproperty20 = 'TMS'
AND a.STATUS IN ('1', 'manually provisioned')
)

[Joon]

Hello Raghu,

No luck, I tried that one already 😞 

[CR]

i tried my system it working fine @Joon .

[CR]

Delete and create again try run

[Joon]

Hello @CR ,

doesn't work for me.. i guess we have too many records to retrieve..

[CR]

yes if have more records it will take to complete and you get after some tym report. don’t have solution

[rushikeshvartak]

SELECT u.username,
       ar.requestdate,
       Substring_index(ar.jbpmprocessinstanceid, '.', -1) AS 'Request ID',
       ar.endpointascsv                                   AS 'Endpoint',
       ev.entitlement_value                               ROLE,
       u.customproperty17                                 AS name,
       u.email                                            AS 'User Email',
       u.jobdescription,
       Date_add(( aa.submitdate ), interval 9 hour)       AS
       'start of Owner Approval',
       Date_add(( aa.approvedate ), interval 9 hour)      AS
       'date of OwnerApproval',
       CASE
         WHEN ar.requesttype = 1 THEN 'Add Access'
         WHEN ar.requesttype = 2 THEN 'Remove Access'
         WHEN ar.requesttype = 3 THEN 'New Account'
         WHEN ar.requesttype = 4 THEN 'Enterprise Role Request'
         WHEN ar.requesttype = 12 THEN 'Update Account'
         WHEN ar.requesttype = 19 THEN 'Update User'
         ELSE ar.requesttype
       END                                                AS 'Request Type',
       CASE
         WHEN aa.status = 1 THEN 'Pending Approval'
         WHEN aa.status = 2 THEN 'Approved'
         WHEN aa.status = 3 THEN 'Rejected'
         WHEN aa.status = 4 THEN 'Escalated'
         WHEN aa.status = 6 THEN 'Discontinued'
         ELSE aa.status
       END                                                AS
       'REQUEST APPROVAL STATE',
       u1.customproperty17                                AS 'Approver',
       aa.jbpm_activity_name                              AS 'APPROVAL TYPE',
       ev.customproperty20                                AS 'Applications',
       ar.comments                                        AS requestcomment,
       ra.comments                                        AS approvalcomment
FROM   ars_requests ar
       inner join request_access ra
               ON ar.requestkey = ra.requestkey
       inner join access_approvers aa
               ON aa.request_access_key = ra.request_accesskey
       inner join entitlement_values ev
               ON ev.entitlement_valuekey = ra.accesskey
       inner join users u
               ON ra.userkey = u.userkey
       inner join users u1
               ON aa.approverkey = u1.userkey
                  AND aa.approveby = u1.userkey
       inner join user_accounts ua
               ON u.userkey = ua.userkey
       inner join accounts a
               ON a.accountkey = ua.accountkey
WHERE  u.statuskey = 1
       AND ev.customproperty20 = 'TMS'
       AND a.accountkey IN (SELECT ae1.accountkey
                            FROM   accounts A
                                   inner join endpoints E
                                           ON A.endpointkey = E.endpointkey
                                   inner join user_accounts ua
                                           ON ua.accountkey = a.accountkey
                                   inner join users u
                                           ON u.userkey = ua.userkey
                                   inner join account_entitlements1 ae1
                                           ON ae1.accountkey = a.accountkey
                                   inner join entitlement_values ev
                                           ON ae1.entitlement_valuekey =
                                              ev.entitlement_valuekey
                            WHERE  e.displayname = 'authweb'
                                   AND ev.status = 1
                                   AND ev.customproperty20 = 'TMS'
                                   AND a.status IN ( '1', 'manually provisioned'
                                                   )) 

[indrahema95]

@Joon Can you please try to create a job like this according to your analytics?

And run it couple of times and check the analytics history if it shows up or not?

Regards,

Indranil