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.
To make any changes to a query, refer to the KB article below.
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 ADSelfService Plus?
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 ...Query that shows Service catalog SLA (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select slad.slaname "SLA NAME", sdo.name "SITE", slad.duebydays "Resolved within Days", slad.duebyhours "Resolved within Hours", slad.duebyminutes "Resolved within Mins", slad.fr_duebydays "Responded ...