7 Replies Latest reply on Mar 20, 2019 4:49 AM by Lisa Singh

    Capturing changes in priority in Smart Reporting

    Melissa Kray
      Share This:

      I have a customer that would like to a build a report within Smart Reporting to track priority changes, specifically ones that are opened as Critical priority and then downgraded to a lower priority.  The options available in our current incident management view only capture the incident's priority at that particular point in time.


      Any guidance would be appreciated!


      Thank you,

      Melissa Kray

        • 2. Re: Capturing changes in priority in Smart Reporting
          Abhijeet Teli

          Hello Melissa,

          Essentially the data required for your Report is available in Audit Forms, but only problem with audit data is it has excess amount of unstructured information, so we need extract required information from the large piece of chunk using String extraction functions. We have added few advanced function in recent releases So in theory it should have been achievable, But i need try it in lab env.

          Will try revert back on this



          Which version of Smart Reporting your customer is using ?





          1 of 1 people found this helpful
          • 3. Re: Capturing changes in priority in Smart Reporting
            Melissa Kray

            Hi Abhijeet,


            Thank you for your response!  We are using Smart Reporting 18.05.


            Thank you,

            Melissa Kray

            • 4. Re: Capturing changes in priority in Smart Reporting
              Krunal Thanki

              Hello Melissa,


              Were you able to get this report? We have same requirement from customer.





              • 5. Re: Capturing changes in priority in Smart Reporting
                Abhijeet Teli

                Hi Melissa/Krunal,


                Sorry for the delayed response,

                I tried few combinations and this can be achieved with tricky Query qualifications and Subquery, I have tested on few Records and it works as expected I have tested for Change management but similar process can be followed for any ITSM module.

                Please follow below steps -


                You need to add CHG:ChangeRequest_AuditLogSystem to OOTB view to get the audit data.

                Edit the change management view and add Audit form and join it with Request Id from CHG:Infrastructure Change and Original Request ID from CHG:ChangeRequest_AuditLogSystem


                We need four fields from the Audit form please select them and add it in respective field category-

                     - Audit Date

                     - Fields Changes

                     - Log

                     - Submitter


                Now our view is ready with required fields. We need to create report with subquery

                Create Master query as Change ID, Audit Date and Submitter and add Fields Changed and Log fields into filter section. Defining a filter values correctly is the important part of this mechanism. Define filters values for master query as -


                Fields Changed Like %Priority% AND Log Like %Priority: Critical%

                Now the master report will pull all change requests from the system which has set Critical priority atleast once in its life cycle. as we are traversing through Audit data it will also consider historical changes, You can additionally add Status filter to pull only open Requests.

                Now add an Append Subquery on same view make join type as inner join and join it with Change ID field. Also add Fields Changed and Log into Subquery Filters



                In a child Report add only two fields Audit Date and Submitter, Since we have columns with similar names in both queries. change column names to something relevant. In master query change audit date as Priority Set to Critical on , in Child query change it to Priority set to high on.

                Now define filter values in child report as

                Fields Changed Like %Priority% AND Log Like %Priority: High%


                Here is the final output of Report -

                Technically report will fetch only relevant records because different qualification for both queries and inner join in Subquery, Also this report will only track Requests which has lowered from Critical to High, If you need it to be from Critical to any Priority then change subquery filter as Log Not Like '%Priority: Critical%' .


                Hope this helps to achieve your requirement, Feel free to revert in case you need any further information.






                3 of 3 people found this helpful
                • 6. Re: Capturing changes in priority in Smart Reporting
                  Peter Adams

                  Thanks, Abhijeet, for taking the time to write this up.

                  • 7. Re: Capturing changes in priority in Smart Reporting
                    Lisa Singh

                    Is it a necessity to use Smart? If you use the Remedy Reporting Console and AR System reports you can use the status history fields to show the status changes.