4 Replies Latest reply on Oct 10, 2017 12:00 PM by Patrick Marshall

    Report on status changes in Analytics

    Patrick Marshall
      Share This:

      I'm trying to track back status changes for Incidents in Analytics. I've been digging through the ITSM universe and the various tables behind the scenes and I can't find where the status change history is recorded - just the Last_Resolved_Date in HPD_Help_Desk. I thought it might be populated in HPD_Help_Desk_Audit_Log, but this table doesn't appear to be populated.

       

      Can anyone guide me to where I might be able to find this information? We are using Remedy OnDemand 8.1.

       

      Thanks!

        • 1. Re: Report on status changes in Analytics
          James Halligan

          Hi Patrick,

           

          you may have to add SH_HPD_Help_Desk to your universe if it is not there OOTB.

           

          Required join is : HPD_HELP_DESK.Entry_ID=SH_HPD_Help_Desk.Entry_ID

           

          This table gives the last date that a Status was updated

           

          The audit log table is HPD_Help_Desk_Audit_LogSystem and this give you all updates to the incident.

          This is trickier to pull data from as it is all held in a long string. I've done it, but it's a bit more complex to incorporate into the universe

           

          regards

           

          James

           

          http://www.jemstar.ie/

          • 2. Re: Report on status changes in Analytics

            Hi Patrick,

             

            We have Change Status History Class in ITSM universe, where you can track Status value and updated time.

             

            Before that, you need to Updating the CHG_STATUS_HISTORY table ( Refer page no. 76). Doubt updating table was implemented by RoD guys.


            Verify once, hope it can be helpful to you.

             

            Regardes,

            Bhushan

            • 3. Re: Report on status changes in Analytics

              Hi Patrick,

              If you want to build the report to track all status change dates (current and previous changes) Status history table will not be any help, since it lists only the latest date the particular status has acquired by overwriting the previous date value for that column. You will have to use HPD_HELPDESK_AUDITLOGSYSTEM table for that purpose. I am listing the query below which will track all the dates for the incident when it moved to Assigned Status. You can add derived table in the universe and modify the query as per your requirement.

               

              Query:

               

              SELECT   HPD_HELPDESK_AUDITLOGSYSTEM.ORIGINAL_REQUEST_ID,

                       HPD_HELPDESK_AUDITLOGSYSTEM.MODIFIED_DATE AS INCIDENT_ASSIGNED_DATE

              FROM     HPD_HELPDESK_AUDITLOGSYSTEM,

                       HPD_HELP_DESK

              WHERE    HPD_HELPDESK_AUDITLOGSYSTEM.ORIGINAL_REQUEST_ID = HPD_HELP_DESK.ENTRY_ID

                       AND HPD_HELPDESK_AUDITLOGSYSTEM.FIELDS_CHANGED LIKE '%;Status;%'

                       AND HPD_HELPDESK_AUDITLOGSYSTEM.LOG LIKE '%Status: Assigned%'

               

              Hope that helps!

              Regards,

              Shweta

              • 4. Re: Report on status changes in Analytics
                Patrick Marshall

                Thanks, everyone!  Shwetambari pointed me in the right direction. Unfortunately, as it is RoD, we can't modify the universe without a customization.

                 

                For those interested, here's a query I wrote for this to pull the status by ticket, and the date it changed:

                 

                SELECT
                  HPD_HELP_DESK.Incident_ID,
                  dbo.fn_adjusted_date(HPD_HelpDesk_AuditLogSystem.Modified_Date),
                  case
                    when (Fields_Changed LIKE '%;Status;%')
                      then left(substring(Log, charindex('Status: ',Log) + 8, 30),
                                charindex(' (',substring(Log, charindex('Status: ',Log) + 8, 30)))
                    else null
                  end as Status
                FROM
                   HPD_HELP_DESK left outer join HPD_HelpDesk_AuditLogSystem
                ON
                  (HPD_HELP_DESK.Entry_ID = HPD_HelpDesk_AuditLogSystem.Original_Request_ID)
                WHERE
                   Fields_Changed LIKE '%;Status;%'

                 

                Note that this will report the Status in text format (Assigned, Pending, Resolved, etc). The numerical status is also presented in the Log field for the Status: line, placed between parenthesis. Some tweaking with the statement should be able to pull that if required.

                 

                *modified to show how it links back to incidents