I have highlighted for two new columns which we needed in report and I used a filter in where clause to list only the tasks startdate as current date(assuming startdate is where the conditionally certified future date is present). when I use this filter I am getting no data found even when date is present.
select
YEAR(a.taskdate) AS YEAR ,
a.TASKKEY,
CASE
WHEN a.status = '1' THEN 'NEW'
WHEN a.status = '2' THEN 'IN PROGRESS'
WHEN a.status = '3' THEN 'COMPLETE'
WHEN a.status = '4' THEN 'DISCONTINUED'
WHEN a.status = '5' THEN 'PENDING CREATE'
WHEN a.status = '6' THEN 'PENDING Provisioning'
WHEN a.status = '7' THEN 'Provisioning FAILED'
WHEN a.status = '8' THEN 'ERROR'
WHEN a.status = '9' THEN 'NO ACTION REQUIRED'
ELSE a.status
END AS 'TASK_STATUS',
CASE WHEN a.status IN (1,2) THEN format(5 * (DATEDIFF(NOW() , a.taskdate) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(a.taskdate) + WEEKDAY(NOW() ) + 1, 1) ,0) ELSE '' END as 'PENDING_DAYS',
CASE
WHEN camp.campaign_type = 1 THEN 'ENTITLEMENT OWNER (AR)'
WHEN camp.campaign_type = 2 THEN 'USER MANAGER (DT)'
WHEN camp.campaign_type = 5 THEN 'ROLE OWNER'
WHEN camp.campaign_type = 6 THEN 'SERVICE ACCOUNT'
END AS 'CAMPAIGN SOURCE TYPE',
CASE
WHEN month(cert.STARTDATE) < 6 AND Camp.campaign_name LIKE '%PRIVILEGED%' THEN 'FIRST BIANNUAL PRIVILEGED (AM-F2B)'
WHEN month(cert.STARTDATE) > 6 AND Camp.campaign_name LIKE '%PRIVILEGED%' THEN 'SECOND BIANNUAL PRIVILEGED (AM-F2B)'
WHEN Camp.campaign_name LIKE '%REGULAR%' THEN 'ANNUAL REGULAR (AM-F2A)'
WHEN Camp.campaign_name LIKE '%NON%SOX%' THEN 'ANNUAL NON-SOX (AMNS_F2A)'
WHEN Camp.campaign_name LIKE '%LAN FOLDER%' THEN 'ANNUAL NON-SOX (AMNS_FC2)'
ELSE Camp.campaign_name END AS 'CERTIFICATION SCOPE',
CASE
WHEN Camp.campaign_name LIKE '%ENTERPRISE%' OR Camp.campaign_name LIKE '%ROLE%' or EV.ENTITLEMENT_GLOSSARY LIKE 'ENTERPRISE%' THEN CONCAT('ENTERPRISE ROLE REMEDIATION: Please make sure to remove all acesses granted by: [',EV.ENTITLEMENT_GLOSSARY, '] end updated the task comment with [ALL ENTITLEMENTS ARE REMOVED]')
WHEN a.status in(8) and a.TASKKEY in(
select DISTINCT
ars.TASKKEY as 'Task ID'
from account_entitlements1 ae
left join arstasks ars ON ars.accountkey = ae.accountkey and ars.entitlement_valuekey = ae.entitlement_valuekey
left join users u on ars.USERKEY = u.USERKEY
left join user_accounts ua ON u.userkey = ua.userkey
left join accounts a on ars.ACCOUNTKEY = a.ACCOUNTKEY and a.accountkey = ae.accountkey
where ars.tasktype =2
and ae.ENTITLEMENT_VALUEKEY is not Null
and a.name NOT LIKE '%-Deleted on-%'
and a.PROVISIONINGCOMMENTS like '%error%'
) THEN 'THE AUTOMATED DEPROVISIONING FAILED: Reopen the task and manually remove the access, then complete the task by adding a comment.Refer to LATESTERROR column'
WHEN a.status in(3) and a.TASKKEY in(
SELECT DISTINCT
ars.TASKKEY as 'Task ID'
from account_entitlements1 ae
INNER join arstasks ars ON ars.accountkey = ae.accountkey and ars.entitlement_valuekey = ae.entitlement_valuekey
INNER join users u on ars.USERKEY = u.USERKEY
INNER join user_accounts ua ON u.userkey = ua.userkey
INNER join accounts a on ars.ACCOUNTKEY = a.ACCOUNTKEY and a.accountkey = ae.accountkey
where
ars.tasktype =2
and ae.ENTITLEMENT_VALUEKEY is not Null
and a.name NOT LIKE '%-Deleted on-%'
and DATEDIFF(NOW()+1, ars.updatedate) >= 1
and ars.UPDATEDATE > CURDATE() - INTERVAL 6 DAY
) THEN CONCAT('REMOVAL FAILED: The task has been completed by ', taskupdated.displayname,' but the access still exisit. Reopen the task and manually remove the access, then complete the task by adding a comment')
ELSE ''
end as 'SPECIAL INSTRUCTION',
CASE
WHEN a.TASKTYPE='1' THEN 'ADD'
WHEN a.TASKTYPE='2' THEN 'REMOVE ACCESS'
WHEN a.TASKTYPE='3' THEN 'NEWACCOUNT'
WHEN a.TASKTYPE='4' THEN 'ROLE REQUEST'
WHEN a.TASKTYPE='5' THEN 'CHANGEPASSWORD '
WHEN a.TASKTYPE='6' THEN 'ENABLE ACCOUNT'
WHEN a.TASKTYPE='7' THEN 'PROPOSED ACCOUNT OWNERS'
WHEN a.TASKTYPE='8' THEN 'DELETE ACCOUNT'
WHEN a.TASKTYPE='9' THEN 'UPDATE USER'
WHEN a.TASKTYPE='12' THEN 'UPDATE ACCOUNT '
WHEN a.TASKTYPE='13' THEN 'PROPOSED Entitlement OWNERS '
WHEN a.TASKTYPE='14' THEN 'DISABLE ACCOUNT '
WHEN a.TASKTYPE='23' THEN 'MODIFY PRIVILEGE '
WHEN a.TASKTYPE='24' THEN 'CREATE ENTITLEMENT'
WHEN a.TASKTYPE='27' THEN 'UPDATE ENTITLEMENT'
WHEN a.TASKTYPE='28' THEN 'DELETE ENTITLEMENT '
WHEN a.TASKTYPE='25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
WHEN a.TASKTYPE='26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
WHEN a.TASKTYPE='29' THEN 'FIREFIGHTER ID GRANT ACCESS'
WHEN a.TASKTYPE='30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
WHEN a.TASKTYPE='31' THEN 'UPDATE ACCESS END DATE'
WHEN a.TASKTYPE='32' THEN 'LOCK ACCOUNT'
WHEN a.TASKTYPE='33' THEN 'UNLOCK ACCOUNT'
else a.tasktype end as 'TASK TYPE',
CONVERT_TZ(A.TASKDATE, 'UTC', 'America/New_York') as 'REVOKE REQUESTED',
CASE WHEN a.status IN (3,4) THEN CONVERT_TZ(a.updatedate, 'UTC', 'America/New_York') ELSE '' END as 'TASK COMPLETED',
CASE
WHEN a.startdate = CURDATE() AND a.COMMENTS LIKE '%Conditional Certified%' THEN a.startdate
ELSE NULL
END AS TEMPORARY_ACCESS_EXPIRATION_DATE,
CASE
WHEN a.COMMENTS LIKE '%Conditional Certified%' THEN 'YES'
ELSE 'NO'
END AS IMMEDIATE_REMOVAL,
CASE
WHEN taskupdated.displayname IS NULL AND a.TASKKEY IS NOT NULL THEN 'AUTOMATION'
WHEN taskupdated.displayname IS NOT NULL AND a.TASKKEY IS NOT NULL THEN taskupdated.displayname
END as 'TASK UPDATED BY NAME',
CASE
WHEN taskupdated.USERNAME IS NULL AND a.TASKKEY IS NOT NULL THEN 'SAVIYNT (Admin)'
WHEN taskupdated.USERNAME IS NOT NULL AND a.TASKKEY IS NOT NULL THEN taskupdated.USERNAME
END as 'TASK UPDATED BY PIN',
a.PROVISIONINGCOMMENTS AS 'TASK OWNER COMMENT',
a.comments as 'TASK STATUS_HISTORY',
u.username as 'USER',
U.DISPLAYNAME AS 'USER NAME',
CASE
WHEN u.STATUSKEY = '1' THEN 'ACTIVE'
WHEN u.STATUSKEY = '0' THEN 'INACTIVE'
ELSE '' END AS 'USER_STATUS',
a.ACCOUNTNAME as 'ORIGINAL ACCOUNT',
acc.name as 'ACCOUNT',
ss.DISPLAYNAME as 'SYSTEM',
e.endpointname as 'ENDPOINT',
ev.entitlement_value as 'ENTITLEMENT VALUE',
CASE WHEN EV.ENTITLEMENT_GLOSSARY LIKE 'ENTERPRISE%' THEN EV.ENTITLEMENT_GLOSSARY ELSE EV.displayname END AS 'ENTITLEMENT NAME',
CASE
WHEN Acc.STATUS = '1' THEN 'ACTIVE'
WHEN Acc.STATUS = '2' THEN 'INACTIVE'
ELSE '' END AS 'ACCOUNT_STATUS',
case when ev.CUSTOMPROPERTY21 ='NO' then 'This SAP access should be automatically removed. If part of this list, please contact Dhathri and Francois.' else '' end as 'AUTOMATION ISSUE',
ev.CUSTOMPROPERTY21 AS 'Excluded from Remediation Automation (CP21)',
ev.CUSTOMPROPERTY22 AS 'Automation Comment (CP22)',
CERTIFIER.DISPLAYNAME AS 'CERITIFER NAME',
CERTIFIER.USERNAME AS 'CERTIFIER PIN',
camp.campaign_name as 'Campaign',
ev.Entitlement_Glossary as 'TECHNICAL COMMENT'
FROM arstasks a
left join accounts acc on acc.accountkey = a.accountkey
left join user_accounts ua on a.accountkey = ua.accountkey
left join users u on ua.userkey = u.userkey
left join entitlement_values ev on a.ENTITLEMENT_VALUEKEY= ev.ENTITLEMENT_VALUEKEY
left join endpoints e on a.ENDPOINT = e.ENDPOINTKEY
left join securitysystems ss on a.SECURITYSYSTEM = ss.SYSTEMKEY
left join certification cert on a.SOURCEID = cert.CERTKEY
left join campaign camp on cert.CAMPAIGNKEY = camp.id
left join users certifier on cert.CERTIFIER = CERTIFIER.USERKEY
left join users taskupdated on taskupdated.userkey= A.UPADTEUSER
where
a.TASKTYPE='2' and
camp.campaign_name not like '%SELF%' AND
DATE(a.STARTDATE) = CURDATE() and
camp.campaign_type in (2) and
(
(
(a.status in(1,2,5,6,7) and
YEAR(a.taskdate) =year(curdate()) and
(e.endpointname in ('SAP BW BP1','SAP ECC PRD','SAP EAM AP1','SAP Gateway GP1','SAP Gateway GP0','SAP Gateway GP2','SAP Solution Manager OP2','SAP LMS TP9','SAP TM LP0','SAP EM VP0') OR
ss.SYSTEMNAME like 'SS_SAP_S4_S4P%' OR
ss.SYSTEMNAME like 'SS_SAP_SLT_LTP%' OR
ss.SYSTEMNAME like 'SS_SAP_MDG_D4P%' OR
ss.SYSTEMNAME LIKE 'SS_SAP_Enable_Now_%' OR
ss.SYSTEMNAME not like '%SAP%')
AND ev.entitlement_value IS NOT NULL )
)
OR
(
a.status in(1,2,5,6,7,9) and
EV.ENTITLEMENT_GLOSSARY LIKE 'ENTERPRISE%' and
a.comments not like '%ALL ENTITLEMENTS ARE REMOVED%'
)
OR
(
a.TASKKEY in(
select DISTINCT
ars.TASKKEY as 'Task ID'
from account_entitlements1 ae
INNER join arstasks ars ON ars.accountkey = ae.accountkey and ars.entitlement_valuekey = ae.entitlement_valuekey
INNER join users u on ars.USERKEY = u.USERKEY
INNER join user_accounts ua ON u.userkey = ua.userkey
INNER join accounts a on ars.ACCOUNTKEY = a.ACCOUNTKEY and a.accountkey = ae.accountkey
where ars.tasktype =2
and ars.status =8
AND DATEDIFF(NOW() , a.taskdate) < 60
AND YEAR(a.taskdate) =year(curdate())
and ae.ENTITLEMENT_VALUEKEY is not Null
and a.name NOT LIKE '%-Deleted on-%')
)
OR
(
a.TASKKEY in(
SELECT DISTINCT
ars.TASKKEY as 'Task ID'
from account_entitlements1 ae
INNER join arstasks ars ON ars.accountkey = ae.accountkey and ars.entitlement_valuekey = ae.entitlement_valuekey
INNER join users u on ars.USERKEY = u.USERKEY
INNER join user_accounts ua ON u.userkey = ua.userkey
INNER join accounts a on ars.ACCOUNTKEY = a.ACCOUNTKEY and a.accountkey = ae.accountkey
where
ars.tasktype =2
AND ars.status = 3
and ae.ENTITLEMENT_VALUEKEY is not Null
and a.name NOT LIKE '%-Deleted on-%'
and DATEDIFF(NOW()+1, ars.updatedate) >= 1
and ars.UPDATEDATE > CURDATE() - INTERVAL 1 DAY)
AND DATEDIFF(NOW() , a.taskdate) < 60
)
)
