Request violated by Group
This report used to find the group who violated the request. If the request/incident already has a violation and is reassigned to another group the new group assumes the violation instead of the group that the violation occurred.
To make any changes to a query, refer to the KB article below.
SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(qd.QUEUENAME) AS "Current Group", max(std.STATUSNAME) "Request Status", CASE WHEN max(g.queuename) IS NOT NULL THEN max(g.queuename) ELSE max(qd.QUEUENAME) END "Due by Group" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN (SELECT woh2.workorderid, qd2.queuename FROM workorderhistory woh2 LEFT JOIN workorderhistorydiff wohd2 ON woh2.historyid=wohd2.historyid LEFT JOIN queuedefinition qd2 ON cast(cast(wohd2.prev_value AS varchar) AS int)=qd2.queueid LEFT JOIN workorder wo2 ON wo2.workorderid=woh2.workorderid WHERE (wohd2.COLUMNNAME) IN ('QUEUEID') AND woh2.operationtime > wo2.duebytime) g ON wo.workorderid=g.workorderid WHERE wos.isoverdue='1' AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> GROUP BY wo.WORKORDERID
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
Request violated by technician
This report used to find the technician who violated the request. If the request/incident already has a violation and is reassigned to another technician the new technician assumes the violation instead of the technician that the violation occurred. ...Query to show SLA violated and not violated request count based on technician (MSSQL)
Tested in Build MSSQL (14306) SELECT ti.FIRST_NAME "Technician", count(wo.WORKORDERID) "Total Request", count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" , count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA ...Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT pd.PRIORITYNAME AS "Priority" , count(wo.WORKORDERID) "Total Requests", count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed", count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE ...Shared Request - Group
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", std.STATUSNAME AS "Request Status", qa.queuename "Shared to group" FROM WorkOrder wo LEFT JOIN SDUser ...Query to show who actually violated the SLA (PGSQL & MSSQL )
Working on Builds 14500 and above. Tested in build PGSQL (14300) and MSSQL (14306) Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(ti.FIRST_NAME) "Current Technician", max(std.STATUSNAME) "Request Status", ...