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. 


          • 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" , ...
          • Contract expire in next 30 days

            MSSQL SELECT mcdt.CONTRACTNAME "Contract Name", r.resourcename "Asset", longtodate(mcdt.todate) "Date" FROM MaintenanceContract mcdt LEFT JOIN contractdetails cd ON cd.contractid = mcdt.contractid LEFT JOIN resources r ON r.resourceid = cd.resourceid ...
          • Dynamically set SLA based on field values

            Requirement: SLA for service requests needs to be set depending on the category, subcategory and priority of the request. Usecase: The possibility to modify/set SLA depending on a request Field is not available in Service Request. Also, since SLA is ...
          • Set SLA automatically for a service request using FAFR

            Requirement: Set SLA automatically for a service request depending on the requester site. Usecase: Let us consider a scenario where SLA has to be set depending on the requester's associated site. To do this,  we need to create the required SLA list ...
          • Request based on SLA name

            This report is used to get the name of the sla applied in the request.  To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT ...