Request violated by Group

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.


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. 





          • 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.  ...
          • 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 ...
          • Group change from history

            This report is used to find who changed the group 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 wo.WORKORDERID "Request ID", ...
          • Automatically close request after successive approval reminders.

            This post describes the use of a python script to close requests after successive approval reminders using Custom Schedules. Use Case: We have an option under the self-service portal in order to send reminders mail for the approvals, what would be ...
          • Merged 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 MSSQL SELECT Workorderid "Parent Request", (SELECT STUFF( (SELECT cast(Child_woid AS ...