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 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", ...

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

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

                    • Automated Problem Ticket Creation Based on Request Criteria

                      Requirement: Create a problem automatically when a minimum threshold number of requests created with the same category, subcategory, and Item within the specified time frame is met. Configuration Steps : 1. Download the attached scripts. 2. Go to ...

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

                      Related Products