Changes that were caused by Incidents

Changes that were caused by Incidents

SELECT chdt.changeid                  "Change ID", 
       chdt.title                     "Change Title", 
       Longtodate(chdt.createdtime)   "Change Created Time", 
       Longtodate(chdt.completedtime) "Change Completed Time", 
       orgaaa.first_name              "Change Requested by", 
       ownaaa.first_name              "Change Technician", 
       stagedef.NAME                  "Change Status", 
       wo.workorderid                 "Request ID", 
       aau.first_name                 "Requester", 
       qd.queuename                   "Group", 
       ti.first_name                  "Request Technician", 
       Longtodate(wo.createdtime)     "Request Created Time", 
       Longtodate(wo.completedtime)   "Request Completed Time", 
       std.statusname                 "Request Status" FROM   changedetails chdt 
       LEFT JOIN sduser orgsd 
              ON chdt.initiatorid = orgsd.userid 
       LEFT JOIN aaauser orgaaa 
              ON orgsd.userid = orgaaa.user_id 
       LEFT JOIN sduser ownsd 
              ON chdt.technicianid = ownsd.userid 
       LEFT JOIN aaauser ownaaa 
              ON ownsd.userid = ownaaa.user_id 
       LEFT JOIN stagedefinition stagedef 
              ON chdt.stageid = stagedef.stageid 
       INNER JOIN incidenttochangemapping icm 
               ON chdt.changeid = icm.changeid 
       LEFT JOIN workorder wo 
              ON icm.workorderid = wo.workorderid 
       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 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 

          • Related Articles

          • Incidents caused by Change

            SELECT chdt.changeid                  "Change ID",         chdt.title                     "Change Title",         Longtodate(chdt.createdtime)   "Change Created Time",         Longtodate(chdt.completedtime) "Change Completed Time",  ...
          • Problem and Associated incidents with Request id

            MSSQL SELECT "prob"."PROBLEMID" AS "Problem ID", max("prob"."TITLE") AS "Title", longtodate(max("prob"."REPORTEDTIME")) AS "Reported Date", max("prob"."DUEBYTIME") AS "DueBy Date", max("orgaaa"."FIRST_NAME") AS "Reported by", max("probf"."UDF_CHAR1") ...
          • How to auto-create change request(s) for unplanned changes on Assets

            This post describes the use of a python script to auto-create change requests for unplanned modifications on Assets using Custom Schedules. This script is applicable only for builds prior to 11138. UseCase: Multiple assets are inventoried in ...
          • Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent)

            This report contains the time analysis for each status/group/technician changes in a request. For builds lower than 11122. Status Changes: SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Changed From",sinsd.STATUSNAME as "Changed ...
          • Request change association

            This Report is used to find the request, problem and change association with the status of the association. To make any changes to a query, refer to the KB article below. ...