We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Query for Capturing Tasks which are pending more than 12 hours

gagan94
Regular Contributor
Regular Contributor

Hi Team,

 

I tried to create one query for monitoring purpose but here I am not getting any such tasks with pending more than 12 hours, Below is the query:

select distinct a.taskkey as 'TaskID',a.taskdate as 'TaskDate',a.updatedate as 'Task_Complete_Date',u.Username as 'Username',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',a.requestkey as 'Request Id',
(select displayname from users where userkey=a.UPADTEUSER) as 'UPADTEUSER',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status',
a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME',
CASE
WHEN a.TASKTYPE='1' THEN 'ADD ACCESS'
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 'TaskType'
FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s
where a.status=2 and
a.endpoint = e.endpointkey and
u.userkey = a.userkey and
e.securitysystemkey = s.systemkey and
ev.entitlement_valuekey = a.entitlement_valuekey and a.taskdate > DATE_SUB(NOW(), INTERVAL 12 HOUR);

I am not able to save it even, I tried to get results from data analyser, Can anybody help me out whether something is wrong with logic or what.

 

Regards,

Gagan

 

 

7 REPLIES 7

rushikeshvartak
All-Star
All-Star

select distinct a.taskkey as 'TaskID',a.taskdate as 'TaskDate',a.updatedate as 'Task_Complete_Date',u.Username as 'Username',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',a.requestkey as 'Request Id',
(select displayname from users where userkey=a.UPADTEUSER) as 'UPADTEUSER',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status',
a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME',
CASE
WHEN a.TASKTYPE='1' THEN 'ADD ACCESS'
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 'TaskType'
FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s
where a.status in (1,2) and
a.endpoint = e.endpointkey and
u.userkey = a.userkey and
e.securitysystemkey = s.systemkey and
ev.entitlement_valuekey = a.entitlement_valuekey and a.taskdate > DATE_SUB(NOW(), INTERVAL 12 HOUR);


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

gagan94
Regular Contributor
Regular Contributor

Tried to get the result in data Analyser and in Analytical module, This query is not creating, it took lot of time to save and after that it get blank.

try limiting data

rushikeshvartak_0-1667227439468.png

 


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

gagan94
Regular Contributor
Regular Contributor

Hey, 

Now the challenge is , it is not showing exact request id which it has supposed to be Plus it is showing those tasks also which are not pending more than 12 hours Its showing those tasks which are just created in last 3-4 hours and are in pending state, I think there is some logical issues here:

a.taskdate > DATE_SUB(NOW(), INTERVAL 12 HOUR);

 

 

select distinct
TIMESTAMPDIFF(HOUR,a.taskdate,DATE_SUB(NOW(), INTERVAL 12 HOUR) )hours, a.taskkey as 'TaskID',a.taskdate as 'TaskDate',a.updatedate as 'Task_Complete_Date',u.Username as 'Username',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID',
(select displayname from users where userkey=a.UPADTEUSER) as 'UPADTEUSER',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status',
a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME',
CASE
WHEN a.TASKTYPE='1' THEN 'ADD ACCESS'
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 'TaskType'
FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s,ars_requests ar
where a.status in (1,2) and
a.endpoint = e.endpointkey and
u.userkey = a.userkey and
e.securitysystemkey = s.systemkey and
ar.requestkey=a.requestkey and
ev.entitlement_valuekey = a.entitlement_valuekey and a.taskdate <= DATE_SUB(NOW(), INTERVAL 12 HOUR);


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

gagan94
Regular Contributor
Regular Contributor

I re-framed the query, now only challenge is it is picking recently based tasks also with below query.

According to this query it is picking 5 hour, 6 hours pending tasks also but it should captured only pending which are more than 12 hours,  If you have any idea on this what is wrong with this query that would be really helpful.

select distinct a.taskkey as 'TaskID',a.taskdate as 'TaskDate',u.Username as 'Username',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',(select distinct SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) from ars_requests ar where ar.REQUESTKEY=a.REQUESTKEY) as 'Request ID',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status',a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME',CASE WHEN (a.TASKTYPE = 1 or a.TASKTYPE = 3) THEN 'Grant Access' WHEN a.TASKTYPE = 2 THEN 'Revoke Access' END AS 'Task TYPE' FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s where a.status in (1,2) and a.endpoint = e.endpointkey and u.userkey = a.userkey and e.securitysystemkey = s.systemkey and ev.entitlement_valuekey = a.entitlement_valuekey and a.taskdate > DATE_SUB(NOW(), INTERVAL 12 HOUR)

select distinct a.taskkey as 'TaskID',a.taskdate as 'TaskDate',u.Username as 'Username',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',(select distinct SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) from ars_requests ar where ar.REQUESTKEY=a.REQUESTKEY) as 'Request ID',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status',a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME',CASE WHEN (a.TASKTYPE = 1 or a.TASKTYPE = 3) THEN 'Grant Access' WHEN a.TASKTYPE = 2 THEN 'Revoke Access' END AS 'Task TYPE' FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s where a.status in (1,2) and a.endpoint = e.endpointkey and u.userkey = a.userkey and e.securitysystemkey = s.systemkey and ev.entitlement_valuekey = a.entitlement_valuekey and a.taskdate <= DATE_SUB(NOW(), INTERVAL 12 HOUR)


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