Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/25/2024 03:42 AM
existing query , unable to run the query pls help on this
select distinct ar.TASKKEY as 'TASK ID', CASE WHEN ar.TASKTYPE='1' THEN 'ADD ACCESS' WHEN ar.TASKTYPE='2' THEN 'REMOVE ACCESS' WHEN ar.TASKTYPE THEN 'NEWACCOUNT' WHEN ar.TASKTYPE='4' THEN 'ROLE REQUEST' WHEN ar.TASKTYPE='5' THEN 'CHANGEPASSWORD' WHEN ar.TASKTYPE='6' THEN 'ENABLE ACCOUNT' WHEN ar.TASKTYPE='7' THEN 'PROPOSED ACCOUNT OWNERS' WHEN ar.TASKTYPE='8' THEN 'DELETE ACCOUNT' WHEN ar.TASKTYPE='9' THEN 'UPDATE USER' WHEN ar.TASKTYPE='12' THEN 'UPDATE ACCOUNT' WHEN ar.TASKTYPE='13' THEN 'PROPOSED Entitlement OWNERS' WHEN ar.TASKTYPE='14' THEN 'DISABLE ACCOUNT' WHEN ar.TASKTYPE='23' THEN 'MODIFY PRIVILEGE' WHEN ar.TASKTYPE='24' THEN 'CREATE ENTITLEMENT' WHEN ar.TASKTYPE='27' THEN 'UPDATE ENTITLEMENT' WHEN ar.TASKTYPE='28' THEN 'DELETE ENTITLEMENT' WHEN ar.TASKTYPE='25' THEN 'UPDATE ENTITLEMENT ACCESS ADD' WHEN ar.TASKTYPE='26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE' WHEN ar.TASKTYPE='29' THEN 'FIREFIGHTER ID GRANT ACCESS' WHEN ar.TASKTYPE='30' THEN 'FIREFIGHTER ID REVOKE ACCESS' WHEN ar.TASKTYPE='31' THEN 'UPDATE ACCESS END DATE' WHEN ar.TASKTYPE='32' THEN 'LOCK ACCOUNT' WHEN ar.TASKTYPE='33' THEN 'UNLOCK ACCOUNT' else ar.tasktype end as 'TASK TYPE', ar.PARENTTASK as 'PARENT TASK',(Select DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1)) AS 'REQUEST ID',(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE RA.USERKEY=USERKEY) AS 'REQUESTED FOR',(Select u.username,ar.tasktype,ar.STATUS,ar.PARENTTASK,U.email,u.firstname,u.lastname,Case when ar.ENDPOINT=123 then 'Q2C and I2R' When ar.endpoint=123 then 'S2P and R2R' ELSE 0 END AS 'APPLICATION NAME',ar.ENTITLEMENT_VALUEKEY,ev.ENTITLEMENT_VALUE,ar.ACCOUNTNAME,ar.REQUESTKEY,ar.TASKDATE,ar.USERKEY,ar.PROVISIONINGCOMMENTS, ar.PROVISIONINGTRIES,ar.status as 'ERROROUT_TASK_8' from arstasks ar ,users u,entitlement_values ev where ar.userkey=u.userkey and ev.ENTITLEMENT_VALUEKEY = ar.ENTITLEMENT_VALUEKEY and ENDPOINT in (mnv,yyz) and ar.Status=8 and TASKDATE>CURDATE()
10/25/2024 05:45 AM
anyone pls help on this
10/25/2024 05:49 AM
existing query :
Select u.username,ar.tasktype,ar.STATUS,ar.PARENTTASK,U.email,u.firstname,u.lastname,Case when ar.ENDPOINT=926 then 'Q2C and I2R' When ar.endpoint=925 then 'S2P and R2R' ELSE 0 END AS 'APPLICATION NAME',ar.ENTITLEMENT_VALUEKEY,ev.ENTITLEMENT_VALUE,ar.ACCOUNTNAME,ar.REQUESTKEY,ar.TASKDATE,ar.USERKEY,ar.PROVISIONINGCOMMENTS, ar.PROVISIONINGTRIES,ar.TASKKEY,ar.status as 'ERROROUT_TASK_8' from arstasks ar ,users u,entitlement_values ev where ar.userkey=u.userkey and ev.ENTITLEMENT_VALUEKEY = ar.ENTITLEMENT_VALUEKEY and ENDPOINT in (925,926) and ar.Status=8 and TASKDATE>CURDATE()
Need Request ID and Task type(Add/Remove/other requests) attributes need to be added in this query , i have added but query giving error
10/25/2024 05:55 AM
SELECT DISTINCT A.TASKKEY AS
'TASK ID',
CASE
WHEN A.TASKTYPE = '1' THEN 'ADD ACCESS'
WHEN A.TASKTYPE = '2' THEN 'REMOVE ACCESS'
WHEN A.TASKTYPE = '3' THEN 'NEW ACCOUNT'
WHEN A.TASKTYPE = '4' THEN 'ROLE REQUEST'
WHEN A.TASKTYPE = '5' THEN 'CHANGE PASSWORD'
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',
A.PARENTTASK AS
'PARENT TASK',
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS
'REQUEST ID',
CONCAT(U.FIRSTNAME, ' ', U.LASTNAME, ' (', U.USERNAME, ')') AS
'REQUESTED FOR',
U.USERNAME,
A.TASKTYPE,
A.STATUS,
A.PARENTTASK,
U.EMAIL,
U.FIRSTNAME,
U.LASTNAME,
CASE
WHEN A.ENDPOINT = 123 THEN 'Q2C and I2R'
WHEN A.ENDPOINT = 456 THEN 'S2P and R2R'
ELSE 'Other'
END AS
'APPLICATION NAME',
A.ENTITLEMENT_VALUEKEY,
EV.ENTITLEMENT_VALUE,
A.ACCOUNTNAME,
A.REQUESTKEY,
A.TASKDATE,
A.USERKEY,
A.PROVISIONINGCOMMENTS,
A.PROVISIONINGTRIES,
A.STATUS AS
'ERROROUT_TASK_8'
FROM ARSTASKS A
JOIN ARS_REQUESTS AR
ON AR.REQUESTKEY = A.REQUESTKEY
JOIN USERS U
ON A.USERKEY = U.USERKEY
JOIN ENTITLEMENT_VALUES EV
ON EV.ENTITLEMENT_VALUEKEY = A.ENTITLEMENT_VALUEKEY
WHERE A.ENDPOINT IN ( 1 )
AND A.STATUS = 8
AND A.TASKDATE > CURDATE();