Hi,
I am trying to create a RUNTIME ANLAYTICS Report to pull all the tasks related to campaigns without having AD connection. I can pull all the data, however when I specify the TASKTYPE in report and while running it, we are getting alert as
[Alert: Control execution is taking longer than expected. You will be notified when the result is ready]
but we are not getting the report.
PFB for both working and Alert queries
************************************************************************************************************************
WORKING QUERY:
select
Campaign_Name,
Certification_Name,
Certifier_Username,
Certifier_FirstName,
Certifier_LastName,
Certifier_Email,
USER_ACCOUNT_NAME,
USER_NAME,
USER_Account_status,
User_Owner,
Entitlement_Value,
Entitlement_Description,
Security_System,
Task_Update_User,
Task_Creation_Date,
CONNECTIONTYPE,
connectionname,
TASKTYPE,
Certification_Type,
EntitlementType,
ENDPOINTNAME,
Certification_Status,
TASKID,
TASK_STATUS
FROM
(
SELECT
cm.campaign_name AS 'Campaign_Name',
k.CERT_NAME AS 'Certification_Name',
u.USERNAME AS 'Certifier_Username',
u.firstname as 'Certifier_FirstName',
u.lastname as 'Certifier_LastName',
u.email as 'Certifier_Email',
a.ACCOUNTID AS 'USER_ACCOUNT_NAME',
a.NAME AS 'USER_NAME',
a.status AS 'USER_Account_status',
u.Owner AS 'User_Owner',
ev.entitlement_value AS 'Entitlement_Value',
ev.DESCRIPTION AS 'Entitlement_Description',
sc.Displayname AS 'Security_System',
at.UPADTEUSER AS 'Task_Update_User',
at.UPDATEDATE AS 'Task_Update_Date',
at.STARTDATE AS 'Task_Creation_Date',
ect.CONNECTIONTYPE,
ec.connectionname,
CASE WHEN cm.campaign_type = 1 THEN 'Entitlement Owner' WHEN cm.campaign_type = 2 THEN 'User Manager' WHEN cm.campaign_type = 5 THEN 'Role Owner' WHEN cm.campaign_type = 6 THEN 'Service Account' WHEN cm.campaign_type = 8 THEN 'Application Owner' END AS 'Certification_Type',
case WHEN ET.DISPLAYNAME is null THEN ET.ENTITLEMENTNAME WHEN ET.DISPLAYNAME = '' THEN ET.ENTITLEMENTNAME ELSE ET.DISPLAYNAME END AS 'EntitlementType',
case when EP.DISPLAYNAME is null THEN EP.ENDPOINTNAME ELSE EP.DISPLAYNAME END AS 'ENDPOINTNAME',
CASE WHEN k.status = 4 THEN 'Expired' WHEN k.status IN (3, 😎 THEN 'Locked' WHEN k.status IN (0, 1) THEN 'Pending' WHEN k.status = 7 THEN 'Discontinued' WHEN k.status = 2 THEN 'Ready to Submit' WHEN k.status = 6 THEN 'Preview' WHEN k.status = 10 THEN 'Fully Executed' END AS 'Certification_Status',
caes.TASKID,
CASE WHEN at.STATUS = 1 THEN 'Open/New' WHEN at.STATUS = 2 THEN 'In Progress' WHEN at.STATUS = 3 THEN 'Complete' WHEN at.STATUS = 4 THEN 'Discontinued' WHEN at.STATUS = 8 THEN 'Error' WHEN at.STATUS = 9 THEN 'No Action Required' END AS 'TASK_STATUS' ,
CASE WHEN at.TASKTYPE = 1 THEN 'Add Access'
WHEN at.TASKTYPE = 2 THEN 'Remove Access'
WHEN at.TASKTYPE = 3 THEN 'New Account'
WHEN at.TASKTYPE = 6 THEN 'Enable Account'
WHEN at.TASKTYPE = 8 THEN 'Delete Account'
WHEN at.TASKTYPE = 12 THEN 'Update Account'
WHEN at.TASKTYPE = 14 THEN 'Disable Account'
END AS 'TASKTYPE'
FROM
campaign cm
inner join certification k on cm.id = k.CAMPAIGNKEY
inner join users u on k.certifier = u.USERKEY
left outer join (
select
certkey,
taskid,
certified,
taskstatus,
CERT_ACCOUNTKEY,
CERT_ENTITLEMENT_VALUEKEY
from
certification_account_entitlement1_status
) caes on k.certkey = caes.certkey
left outer join arstasks at on caes.taskid = at.taskkey
left outer join certification_account ca on caes.CERT_ACCOUNTKEY = ca.CERT_ACCOUNTKEY
left outer join accounts a on ca.ACCOUNTKEY = a.ACCOUNTKEY
left outer join certification_entitlement_value cev on caes.CERT_ENTITLEMENT_VALUEKEY = cev.CERT_ENTITLEMENT_VALUEKEY
left outer join entitlement_values ev on cev.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
left outer join entitlement_types ET on EV.entitlementtypekey = ET.entitlementtypekey
left outer join endpoints EP on ET.ENDPOINTKEY = EP.ENDPOINTKEY
left outer join securitysystems sc on ep.securitysystemkey = sc.systemkey
left outer join externalconnection ec on sc.EXTERNALCONNECTION = ec.externalconnectionkey
left outer join externalconnectiontype ect ON ec.EXTERNALCONNECTIONTYPE = ect.EXTERNALCONNECTIONTYPEKEY
) Cert
WHere
IFNULL(Cert.CONNECTIONTYPE, 'NA') != 'AD'
*****************************************************************************************************************
ALERT QUERY
Select
Campaign_Name,
Certification_Name,
Certifier_Username,
Certifier_FirstName,
Certifier_LastName,
Certifier_Email,
USER_ACCOUNT_NAME,
USER_NAME,
USER_Account_status,
User_Owner,
Entitlement_Value,
Entitlement_Description,
Security_System,
Task_Update_User,
Task_Creation_Date,
CONNECTIONTYPE,
connectionname,
TASKTYPE,
Certification_Type,
EntitlementType,
ENDPOINTNAME,
Certification_Status,
TASKID,
TASK_STATUS
FROM
(
SELECT
cm.campaign_name AS 'Campaign_Name',
k.CERT_NAME AS 'Certification_Name',
u.USERNAME AS 'Certifier_Username',
u.firstname as 'Certifier_FirstName',
u.lastname as 'Certifier_LastName',
u.email as 'Certifier_Email',
a.ACCOUNTID AS 'USER_ACCOUNT_NAME',
a.NAME AS 'USER_NAME',
a.status AS 'USER_Account_status',
u.Owner AS 'User_Owner',
ev.entitlement_value AS 'Entitlement_Value',
ev.DESCRIPTION AS 'Entitlement_Description',
sc.Displayname AS 'Security_System',
at.UPADTEUSER AS 'Task_Update_User',
at.UPDATEDATE AS 'Task_Update_Date',
at.STARTDATE AS 'Task_Creation_Date',
ect.CONNECTIONTYPE,
ec.connectionname,
CASE WHEN cm.campaign_type = 1 THEN 'Entitlement Owner' WHEN cm.campaign_type = 2 THEN 'User Manager' WHEN cm.campaign_type = 5 THEN 'Role Owner' WHEN cm.campaign_type = 6 THEN 'Service Account' WHEN cm.campaign_type = 8 THEN 'Application Owner' END AS 'Certification_Type',
case WHEN ET.DISPLAYNAME is null THEN ET.ENTITLEMENTNAME WHEN ET.DISPLAYNAME = '' THEN ET.ENTITLEMENTNAME ELSE ET.DISPLAYNAME END AS 'EntitlementType',
case when EP.DISPLAYNAME is null THEN EP.ENDPOINTNAME ELSE EP.DISPLAYNAME END AS 'ENDPOINTNAME',
CASE WHEN k.status = 4 THEN 'Expired' WHEN k.status IN (3, 😎 THEN 'Locked' WHEN k.status IN (0, 1) THEN 'Pending' WHEN k.status = 7 THEN 'Discontinued' WHEN k.status = 2 THEN 'Ready to Submit' WHEN k.status = 6 THEN 'Preview' WHEN k.status = 10 THEN 'Fully Executed' END AS 'Certification_Status',
caes.TASKID,
CASE WHEN at.STATUS = 1 THEN 'Open/New' WHEN at.STATUS = 2 THEN 'In Progress' WHEN at.STATUS = 3 THEN 'Complete' WHEN at.STATUS = 4 THEN 'Discontinued' WHEN at.STATUS = 8 THEN 'Error' WHEN at.STATUS = 9 THEN 'No Action Required' END AS 'TASK_STATUS' ,
CASE WHEN at.TASKTYPE = 1 THEN 'Add Access'
WHEN at.TASKTYPE = 2 THEN 'Remove Access'
WHEN at.TASKTYPE = 3 THEN 'New Account'
WHEN at.TASKTYPE = 6 THEN 'Enable Account'
WHEN at.TASKTYPE = 8 THEN 'Delete Account'
WHEN at.TASKTYPE = 12 THEN 'Update Account'
WHEN at.TASKTYPE = 14 THEN 'Disable Account'
END AS 'TASKTYPE'
FROM
campaign cm
inner join certification k on cm.id = k.CAMPAIGNKEY
inner join users u on k.certifier = u.USERKEY
left outer join (
select
certkey,
taskid,
certified,
taskstatus,
CERT_ACCOUNTKEY,
CERT_ENTITLEMENT_VALUEKEY
from
certification_account_entitlement1_status
) caes on k.certkey = caes.certkey
left outer join arstasks at on caes.taskid = at.taskkey
left outer join certification_account ca on caes.CERT_ACCOUNTKEY = ca.CERT_ACCOUNTKEY
left outer join accounts a on ca.ACCOUNTKEY = a.ACCOUNTKEY
left outer join certification_entitlement_value cev on caes.CERT_ENTITLEMENT_VALUEKEY = cev.CERT_ENTITLEMENT_VALUEKEY
left outer join entitlement_values ev on cev.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
left outer join entitlement_types ET on EV.entitlementtypekey = ET.entitlementtypekey
left outer join endpoints EP on ET.ENDPOINTKEY = EP.ENDPOINTKEY
left outer join securitysystems sc on ep.securitysystemkey = sc.systemkey
left outer join externalconnection ec on sc.EXTERNALCONNECTION = ec.externalconnectionkey
left outer join externalconnectiontype ect ON ec.EXTERNALCONNECTIONTYPE = ect.EXTERNALCONNECTIONTYPEKEY
) Cert
WHere
IFNULL(Cert.CONNECTIONTYPE, 'NA') != 'AD'
