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.