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?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Query to show Problem details, timespent and its associated request IDs (MSSQL)

                      Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...

                    • Query to show Problems, its associated incidents and change_ (MSSQL)

                      Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...

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

                    • Update request additional field with Custom Request ID

                      Requirement: Each Organization's request ID format may differ. In the current model, replacing the existing request id with these is not possible; however, the same can be replaced in an additional field. Custom Format : Example: 202305221125001 ...

                      Related Products