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" , ...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 ...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 ...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 ...