SLA violation in next 3 days

SLA violation in next 3 days

When we get a list of SLA violation in next 2 days, it is important to alert the team involved in resolving the incident, to take part in SLA restoration.


PGSQL

SELECT wo.WORKORDERID "Request ID",
       aau.FIRST_NAME "Requester",
       qd.QUEUENAME "Group",
       cd.CATEGORYNAME "Category",
       wo.TITLE "Subject",
       ti.FIRST_NAME "Technician",
       longtodate(wo.DUEBYTIME) "DueBy Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN statusdefinition std ON wos.statusid=std.statusid
WHERE (wo.ISPARENT='1')
  AND std.ispending = '1'
  AND wos.isoverdue='0'
  AND (extract(epoch
               FROM(to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 >=1
       AND extract(epoch
                   FROM(to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 <=3)

MSSQL

SELECT wo.WORKORDERID "Request ID",
       aau.FIRST_NAME "Requester",
       qd.QUEUENAME "Group",
       cd.CATEGORYNAME "Category",
       wo.TITLE "Subject",
       ti.FIRST_NAME "Technician",
       longtodate(wo.DUEBYTIME) "DueBy Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN statusdefinition std ON wos.statusid=std.statusid
WHERE (wo.ISPARENT='1')
  AND std.ispending = '1'
  AND wos.isoverdue='0'
  AND (((wo.DUEBYTIME/1000)-DATEDIFF(s, '19700101', GETDATE()))/60/60/24 >=1
       AND ((wo.DUEBYTIME/1000)-DATEDIFF(s, '19700101', GETDATE()))/60/60/24 <=3)

 

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 



      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • SLA details

                      This report is used to get the complete list of SLA  configured in the application.  ​ PGSQL SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...

                    • Query to get complete SLA details. (PGSQL)

                      Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...

                    • Query to show overdue tickets with delay by days

                      Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...

                    • SLA not working

                      There are a few configurations that need to be checked 1. Account/Site Check the request Account & Site, Now, go to Admin>Service Level Agreements>Filter the Account & Site and make sure the SLA is created for the same  2. Operational Hours Go to ...

                    • Send escalation email to the Technician "Reporting to" if SLA is violated (Deluge).

                      From Builds 10600 and above Follow the below steps and based on the update we need to create a custom trigger based on the action set under the SLA escalation notification and then with the help of that we can execute the custom trigger and the ...

                      Related Products