Problem and Associated incidents with Request id

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") AS "Group",
       max("ownaaa"."FIRST_NAME") AS "Technician",
       max("priodef"."PRIORITYNAME") AS "Priority",
       max("statdef"."STATUSNAME") AS "Status",
       max("catadef"."CATEGORYNAME") AS "Category",
       max("subcatadef"."NAME") AS "Subcategory",
       max(ps.rootcause) "Root Cause",
       count(pti.WORKORDERID) "Count of Associated Incidents",

  (SELECT STUFF(
                  (SELECT cast(pt1.workorderid AS varchar) + char(10)
                   FROM ProblemToIncidentMapping pt1
                   WHERE pt1.PROBLEMID=prob.PROBLEMID
                     FOR XML PATH ('')), 1, 0, '')) "Associated Incidents" FROM "Problem" "prob"
LEFT JOIN "Problem_Fields" "probf" ON "prob"."PROBLEMID"="probf"."PROBLEMID"
LEFT JOIN "SDUser" "orgsd" ON "prob"."ORIGINATORID"="orgsd"."USERID"
LEFT JOIN "AaaUser" "orgaaa" ON "orgsd"."USERID"="orgaaa"."USER_ID"
LEFT JOIN "SDUser" "ownsd" ON "prob"."OWNERID"="ownsd"."USERID"
LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID"
LEFT JOIN "StatusDefinition" "statdef" ON "prob"."STATUSID"="statdef"."STATUSID"
LEFT JOIN "PriorityDefinition" "priodef" ON "prob"."PRIORITYID"="priodef"."PRIORITYID"
LEFT JOIN "CategoryDefinition" "catadef" ON "prob"."CATEGORYID"="catadef"."CATEGORYID"
LEFT JOIN "SubCategoryDefinition" "subcatadef" ON "prob"."SUBCATEGORYID"="subcatadef"."SUBCATEGORYID"
LEFT JOIN problemresolution ps ON ps. PROBLEMID=prob.problemid
LEFT JOIN ProblemToIncidentMapping pti ON prob.PROBLEMID=pti.PROBLEMID

          • Related Articles

          • How to close associated incident requests of change request automatically

            This post describes the use of a python script to close change that are in a specific stage using Custom Schedules. Use Case: There may be a scenario where number of Change Requests that have got closed and the associated incident is still in open ...
          • Request Problem association.

            This Report is used to find the request, problem association. 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 ...
          • Auto-create problem request based on certain criteria.

            This is a sample script that triggers the creation of a Problem Request in ServiceDesk Plus using the Problem API.  Use Case:  ​ All incident requests with Priority = High will create a problem ticket with the request's default field values. ...
          • How to reset Request ID sequence?

            Connect to the database and execute the below query to reset the RequestID sequence. Steps here to connect to the database. Query for PostgreSQL database To Reset the request id: insert into ...
          • 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",  ...