Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
No ratings

Arstasks Dashboard Reports Query based on task status

sudeshjaiswal
Saviynt Employee
Saviynt Employee

on ‎04/11/2023 06:50 AM

Use Case

 

This document will provide the consolidated list of some of the frequently used Dashboard Reports for ArsTasks Monitoring. This can be lifted and shifted at any customer environment.

This Document will cover the following scenarios:

  • Pending Tasks for more than 12 Hours
  • Error Tasks - Connection Failures 
  • List of Tasks discontinued in the last 10 days
  • Tasks in Error State 

 

Pre-requisites

 

N/A

 

Applicable Version(s)

 

All

 

Solution

 

Pending Tasks for more than 12 Hours:

This report displays the Tasks in pending state for more than 12 Hours

select a.taskkey as 'TaskID', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName',ev.entitlement_value as 'EntitlementValue', e.endpointname as 'Endpoint', a.comments as 'TaskComments',  a.source as 'Source', 

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 'TaskType',right(a.PROVISIONINGCOMMENTS,200) as 'LatestError',

'> 12 Hours'  AS SLA 

FROM arstasks a, users u, endpoints e, entitlement_values ev

where a.taskdate <= date_sub(now(),INTERVAL 12 HOUR)

and a.STATUS =1

and a.endpoint = e.endpointkey and u.userkey = a.userkey

and a.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY order by taskdate asc

 

Error Tasks - Connection Failures :

This report displays the list of tasks with errors due to connection failures. The task execution will fail if the tasks are giving error because of Connection issues. This Query will identify such connections and also provide you an option to Re-open the tasks from Analytics itself.

select a.taskkey as 'tasks' , u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.ACCOUNTKEY as acctKey,

 e.endpointname as 'Endpoint', ev.entitlement_value as 'EntitlementValue', a.comments as 'TaskComments',  a.source as 'Source', 

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 'TaskType',right(a.PROVISIONINGCOMMENTS,500) as 'LatestError', case when a.status = 8 then 'Reopen Tasks' end as Default_Action_For_Analytics

 from

 arstasks a

 inner join users u on u.userkey = a.userkey

 inner join endpoints e on a.endpoint = e.endpointkey

 inner join securitysystems ss on a.SECURITYSYSTEM = ss.systemkey

 inner join externalconnection ec on ss.EXTERNALCONNECTION = ec.externalconnectionkey

 left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey

  where a.status in (7,8)

  and ec.status = 0;

 

Analytics Config:

sudeshjaiswal_0-1681119300702.png

 

 

List of Tasks discontinued in the last 10 days :

This report displays the list of tasks discontinued in  the past 10 days. This Query will identify such tasks and also provide you an option to Re-open the tasks from Analytics itself. 

 

select a.taskkey as 'tasks', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey,

e.endpointname as 'Endpoint', ev.entitlement_value as 'EntitlementValue', a.comments as 'TaskComments',  a.source as 'Source',

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 'TaskType',right(a.PROVISIONINGCOMMENTS,200) as 'LatestError',

 case when a.status = 4 then 'Reopen Tasks' end as Default_Action_For_Analytics

from arstasks a

inner join users u on u.userkey = a.userkey

inner join endpoints e on a.endpoint = e.endpointkey

left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey

where  a.status = 4  and a.taskdate >= date_sub(curdate(),INTERVAL 10 DAY)

order by a.taskdate

 

Tasks in Error State :

This report displays the total tasks in error state to be monitored, owners and action items.

 

(select ABC.* from

(select a.taskkey as 'tasks' , u.SYSTEMUSERNAME as 'Username', 

 case when u.STATUSKEY = 1 then 'Active' 

 when u.STATUSKEY = 0 then 'Inactive' else u.STATUSKEY end as 'UserStatus' , a.ACCOUNTNAME as 'AccountName', a.ACCOUNTKEY as acctKey,date(a.taskdate) as taskdate,

 e.endpointname as 'Endpoint', ev.entitlement_value as 'EntitlementValue', a.comments as 'TaskComments',  a.source as 'Source',  

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 'TaskType',right(a.PROVISIONINGCOMMENTS,500) as 'LatestError', 

 case when a.status = 8 then 'Reopen Tasks' end as Default_Action_For_Analytics

 from

 arstasks a

 inner join users u on u.userkey = a.userkey

 inner join endpoints e on a.endpoint = e.endpointkey

 inner join securitysystems ss on a.SECURITYSYSTEM = ss.systemkey

 inner join externalconnection ec on ss.EXTERNALCONNECTION = ec.externalconnectionkey

 left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey

  where a.status in (7,8)

  and a.PROVISIONINGCOMMENTS is not null

  and ec.status = 1) as ABC)

    union

 (select ABC.*

 from

 (select a.taskkey as 'Tasks' , u.SYSTEMUSERNAME as 'Username', case when u.STATUSKEY = 1 then 'Active' 

 when u.STATUSKEY = 0 then 'Inactive' else u.STATUSKEY end as 'UserStatus', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey, date(a.taskdate) as taskdate,

 e.endpointname as 'Endpoint', ev.entitlement_value as 'EntitlementValue', a.comments as 'TaskComments',  a.source as 'Source', 

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 'TaskType',right(a.PROVISIONINGCOMMENTS,500) as 'LatestError', case when a.status = 8 then 'Reopen Tasks' end as Default_Action_For_Analytics

 from

 arstasks a

 inner join users u on u.userkey = a.userkey

 inner join endpoints e on a.endpoint = e.endpointkey

 inner join securitysystems ss on a.SECURITYSYSTEM = ss.systemkey

 inner join externalconnection ec on ss.EXTERNALCONNECTION = ec.externalconnectionkey

 left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey

  where a.status in (7,8)

  and a.PROVISIONINGCOMMENTS is null

  and ec.status = 1) as ABC);

 

References


https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter19-Dashboards/Dashboards.htm

Version history
Last update:
‎04/11/2023 06:50 AM
Updated by:
Saviynt Employee
Contributors
Copyright © 2024 Khoros, LLC