Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Creating a RUNTIME ANLAYTICS Report to pull all the tasks related to campaigns without AD connection

ravitejainje
New Contributor
New Contributor

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'
 





4 REPLIES 4

Raghu
Valued Contributor III
Valued Contributor III

can you change below one like alias name and try it.

Task_Creation_Date as 'TaskCreationDatt'

You will get report after sometime also check bag-round.

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

ravitejainje
New Contributor
New Contributor

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'


Note : Above Posted Queries got a EMOJI's So Updated Again
 




( 3,8 ) Then locked

 

rushikeshvartak_0-1713239745888.png

Select
Campaign_Name,
Certification_Name,
Certifier_Username,
Certifier_FirstName,
Certifier_LastName,
Certifier_Email,
USER_ACCOUNT_NAME,
USER_NAME,
USER_Account_state,
User_Owner,
Entitlement_Value,
Entitlement_Description,
Security_System,
Task_Update_User,
Task_Creation_DaT,
CONNECTIONTYPE,
connectionname,
TASKTYPE,
Certification_Type,
EntitlementType,
ENDPOINTNAME,
Certification_State,
TASKID,
TASK_STATE

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_state',
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_DAT',
at.STARTDATE AS 'Task_Creation_DaT',
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_State',
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_STATE' ,
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'


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.