Average Time KPI for request

 SELECT   'Level 1 - Avg Time spent in the first Approval (QAD Mexico SoD Validation Approval) in hours'           AS category ,

                  Concat(Round((Sum(Timestampdiff(hour, submitdate, approvedate)) / Count(username)),0) ,' hours') AS 'Data'

FROM     ars_requests ar,

         access_approvers aa,

         users u,

         request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='QAD Mexico SoD Validation Approval'

AND      en.displayname ='RUSHI'

AND      ar.requestdate BETWEEN Now() - interval 30 day AND      now()

GROUP BY en.displayname

UNION

SELECT   'Level 1 - Count of requests exceeding 5 days in rolling 30 day period(QAD Mexico SoD Validation Approval)' AS category ,

                  concat(round(count(DISTINCT jbpmprocessinstanceid),0) ,' requests')                                     data

FROM     ars_requests ar,

         access_approvers aa,

         users u,

         request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='QAD Mexico SoD Validation Approval'

AND      en.displayname = 'RUSHI'

AND      ar.requestdate BETWEEN now() - interval 30 day AND      now()

AND      timestampdiff(day, aa.submitdate, aa.approvedate) > 5

GROUP BY en.displayname

UNION

SELECT   'Level 2 - Avg Time spent in the first Approval (Secuirty Groups Owner) in hours'           AS category ,

                  concat(round((sum(timestampdiff(hour, submitdate, approvedate)) / count(username)),0) ,' hours') AS 'Data'

FROM     ars_requests ar,

         access_approvers aa,

         users u,

          request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='Secuirty Groups Owner'

AND      en.displayname ='RUSHI'

AND      ar.requestdate BETWEEN now() - interval 30 day AND      now()

GROUP BY en.displayname

UNION

SELECT   'Level 2 - Count of requests exceeding 5 days in rolling 30 day period by (Secuirty Groups Owner)' AS category ,

                  concat(round(count(DISTINCT jbpmprocessinstanceid),0) ,' requests')                                     data

FROM     ars_requests ar,

         access_approvers aa,

         users u,

         request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='Secuirty Groups Owner'

AND      en.displayname = 'RUSHI'

AND      ar.requestdate BETWEEN now() - interval 30 day AND      now()

AND      timestampdiff(day, aa.submitdate, aa.approvedate) > 5

GROUP BY en.displayname

UNION

SELECT   'Level 2 - Avg Time spent in the first Approval (Default Security Groups Owner) in hours'           AS category ,

                  concat(round((sum(timestampdiff(hour, submitdate, approvedate)) / count(username)),0) ,' hours') AS 'Data'

FROM     ars_requests ar,

         access_approvers aa,

         users u,

         request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='Default Security Groups Oowner'

AND      en.displayname ='RUSHI'

AND      ar.requestdate BETWEEN now() - interval 30 day AND      now()

GROUP BY en.displayname

UNION

SELECT   'Level 2 - Count of requests exceeding 5 days in rolling 30 day period by (Secuirty Groups Owner)' AS category ,

                  concat(round(count(DISTINCT jbpmprocessinstanceid),0) ,' requests')                                     data

FROM     ars_requests ar,

         access_approvers aa,

         users u,

         request_access ra,

         request_access_attrs raa,

         endpoints en

WHERE    ar.requestkey = ra.requestkey

AND      raa.request_access_key = ra.parentrequest

AND      raa.attribute_value = en.endpointkey

AND      raa.attribute_name = 'ENDPOINT'

AND      ra.request_accesskey = aa.request_access_key

AND      1 = 1

AND      ar.status NOT IN (4,

                           6)

AND      u.userkey = aa.approveby

AND      jbpm_activity_name='Default Security Groups Oowner'

AND      en.displayname = 'RUSHI'

AND      ar.requestdate BETWEEN now() - interval 30 day AND      now()

AND      timestampdiff(day, aa.submitdate, aa.approvedate) > 5

GROUP BY en.displayname

UNION

select

'Provisioning - Avg Time spent provisioning between 2nd approval and request competition' AS category ,

concat(ROUND(AVG(TIMESTAMPDIFF(HOUR, ar.taskdate, ar.updatedate)),0),' hours') Data

 from arstasks ar, endpoints e 

where 

e.endpointkey=ar.endpoint

and e.displayname='RUSHI'

and ar.status=3

AND      ar.updatedate BETWEEN now() - interval 30 day AND      now()

UNION

select

'Provisioning - Count of requests exceeding 5 days rolling 30 day period' AS category ,

concat(round(count(DISTINCT ar.requestkey),0) ,' requests')Data

 from arstasks ar, endpoints e 

where 

e.endpointkey=ar.endpoint

and e.displayname='RUSHI'

and ar.status=3

AND      ar.updatedate BETWEEN now() - interval 30 day AND      now()

Comments

Post a Comment

Popular posts from this blog

Enhanced Query - To Update Account Status and Name

select accountkey as accounts__primarykey, 'SUSPENDED FROM IMPORT SERVICE' as accounts__status, CONCAT(name,'-Deleted on-', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))as accounts__name from accounts where endpointkey=527 and name='CN=INRVCH,OU=User Accounts Vista,OU=User Accounts,OU=User Directory,DC=asia-pac,DC=xx,DC=com'  and status not in ('SUSPENDED FROM IMPORT SERVICE') limit 1;
Read more

Connection Config - STATUS_THRESHOLD_CONFIG

 {   "statusAndThresholdConfig": {    "inactivateAccountsNotInFile": true    "statusColumn":"userlock",    "activeStatus":["0","128","192"],     "accountThresholdValue": 500,     "deleteLinks": false,     "correlateInactiveAccounts": true,        } } ----------------------------------------------------------------------------- When account import run above STATUS_THRESHOLD_CONFIG is used to check. ----------------------------------- inactivateAccountsNotInFile  ==> True/false true ---> Account status 1/2 ( Active/Inactive) Day 1 --> 100 Accounts import Day 2 --> 90 Diff= 10  ( 90= Active , 10 Inactive)  ------------------------------------------------------------------------------ ----------------------------------- inactivateAccountsNotInFile  ==> True/false false ---> Account status 1/SFIS ( Active/SUSPENDED FROM IMPORT SERVICE.) Day 1 --> 100 Ac...
Read more

Email variables _ Register User

ServiceAccountOwnerMap ------- [:] entitlements ------- [] accessapprovers ------- com.saviynt.ecm.workflow.Access_Approvers : 812 jbpm_activity_name ------- Third Party Registration Approvers allApproversComments ------- null fullrowhtmltablerows ------- listofallrolesinrequest ------- [] approvedEntitlementsOwners ------- [] entitlementshtmltablerowsv2 ------- entitlementshtmltablerowsv3 ------- ServiceAccountType ------- requestlink ------- null/jbpmworkflowmanagement/showrequestdetails/TP Registration Approvals.553050?reqid=433 allRejectorsComments ------- null rolesApprovedOrRejected ------- [] rolename ------- request_access ------- com.saviynt.ecm.workflow.Request_Access : 456 users ------- XXXX allRejectors ------- [] requestid ------- 553050 rolemap ------- [:] baseUrlForEmail ------- https://dev.saviyntcloud.com/ECM assignee ------- XXXX activityname ------- Third Party Registration Approvers ServiceAccountFlag ------- false request ------- com.saviynt.ecm.workflow.ARS_Reques...
Read more