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
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • 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 ...

                      • Automatic Problem Creation for Request Clustering

                        Requirement: Automatically create a problem when there are threshold number of requests created with the same category/subcategory/item within a specified time frame. Steps to configure: Download the attached scripts. Goto Admin > Developer Space > ...

                      • 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 Notes

                        SELECT prob.PROBLEMID "Problem ID", prob.TITLE "Title", ownaaa.FIRST_NAME "Technician", longtodate(prob.REPORTEDTIME) "Reported Date", sdno.description "Notes" FROM Problem prob LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ...

                        Related Products