14 Replies Latest reply on Jan 3, 2013 4:14 AM by Guillermo NameToUpdate

    Incident Status Duration Reports



      I'm trying to locate information, either in a log file, table, etc., that provides how long an incident ticket was in the "Pending" status.  We're trying to use this information to be included as an addition to our metrics within Crystal Reports we create.  I'm not sure about how to go about this but I believe that Remedy already keeps track of the date/time an incident tickets enters AND exits the "Pending" status, or any status for that matter, and therefore I would be able to calculate the time the ticket spent in that status before moving forward in the process.  Any help would be grateful and let me know if you need additional information.





        • 1. Re: Incident Status Duration Reports
          Hitesh Thanki

          On Incident Interface form you have Status History - Time column with various Status values, you may want to apply some math around and get it !

          • 2. Re: Incident Status Duration Reports
            Ashwanth Padmanabhan

            if i think of achieving this then i would create a field called total time in Pending Status...when ever Ticket moves from 'DB.Status'!="Pending" AND 'TR.Status'="Pending" I will capture that time in one field and when Ticket moves out of Pending Status that is 'DB.Status="Pending" AND 'TR.Status'!="Pending" then I will capture that finish time in a Field and calculate the difference and append it in the Total Time in Pending Status Date Time Field...


            so when ever ticket moves on to pending it will capture that time and when it moves out it wil capture that time as well...find the difference and append it in the Total Time in Pending Status Date Time Field...


            and I will include this field in any of the reports...if needed....


            not sure is there is any place readily we can capture the total time a ticket was in Pending Status....however you can view the Incident Audit Log but that shows who has moved the ticket to pending and when they have moved and from which status...

            • 3. Re: Incident Status Duration Reports
              LJ LongWing

              wouldn't turning on auditing on the status field, and then calculating the in-out of that status do it effectively, without the need for additional workflow?

              • 4. Re: Incident Status Duration Reports
                Ashwanth Padmanabhan

                if in case we have Audit Enabled in the type of Log..


                what info we get is Status(Audit Enabled) whenever someone changes the Status Field value there will an entry created in the Audit Form saying Field Changed is Status and in the Log  it says that Status:Pending...


                you can pick in at what all the point the Status of the Ticket was put on Pending...having Create Date of the Audit Form Record whenever Status is there in the Fields Changed and in the Log when Status is 'Pending'....but with that we wont be able to calculate how long the ticket was remaining in Pending...because it doesnt capture from which Status the record has been moved from to the Current Status..for example if i move the ticket from In Progress to Pending...there will be a record in the audit that just says Status Changed and Status is Pending moved by which user,,,and when i move the ticket from Pending to In Progress again it just says Fields Changed is Status and the value of the Status is In Progress but it doesnt tell that the earlier status was Pending...so this makes impossible to track down the Start and End time of a Pending Status...

                • 5. Re: Incident Status Duration Reports
                  LJ LongWing


                  What you do in that situation is create a left outer self join on the audit form and join them on the same source record.  The join is defined so that sequential records are joined together.  What you have with this is the ability to look at the 'from and 'to' values, along with the times in which each one went from and to.  the first record in the join for a particular record will not have a 'from' because it's the initial...and the final record doesn't have a 'to' because that's the current status....but with this type of join you essentially have a log of all transitions and with it you can search for all records 'from pending' and calculate the numerical difference between the 'to' time of going into pending, and the 'to' time of going into any other status, then sum the results (in case it happens more than once) and this gives you total duration in any status other than the 'last' status, but that is a running total anyway so the total would only be accurate for the current second that the report was run anyway....but I have also taken care of that scenario as well and basically populated 'now' into the to time to give running totals of any tickets in that status

                  • 6. Re: Incident Status Duration Reports
                    Ashwanth Padmanabhan

                    excellent--having Join Structure with a qual of Original ID of the audit form and if that gives us a structure which would let us know the time(Create Time of Audit Form can be used) when the record enters in to a particular Status and the time when the record leaves from that Status to anyother Status....then we can have the summation of all entry point time subtracted with the summation of all exit point time would yield the total time of a State....


                    this would be more generic...

                    • 7. Re: Incident Status Duration Reports

                      Hi Ashwanth, I have a similar kind of requirement while I do manual reconciliation (for Quality Check) of an Incident. Could you elaborate and advise me on how do I calculate the Pending time or Duration an Incident was there in each status please.


                      Thank You



                      • 8. Re: Incident Status Duration Reports
                        Ashwanth Padmanabhan

                        For Pending Time what I was proposing was you would have to have '3' Fields(a bit of Customization involved) ...in the HPD:Help Desk...


                        Pending Startime:

                        Pending Endtime:


                        Pending Total Time:



                        Here when ever the Ticket moves from some Status to Pending..we will capture the Pending Starttime in the Pending Starttime Field having a simple Filter in place which would fire when 'DB.Status'!="Pending" AND 'TR.Status'="Pending" that would set the Current Date Time ...in the Pending Startime Field to capture when the Ticket moved in to Pending...


                        and another Filter should be there which will fire when the Ticket Status moves from Pending to anyother Status so this Filter would fire when 'DB.Status'="Pending" AND 'TR.Status'!="Pending"..that time it will capture that Current Date Time in to Pending EndTime ....


                        now the difference of the Pending Start and Pending Endtime would give you the total time the ticket was in Pending..and you can append that to the Pending Total Time Field with another Filter or in the Same Filter which sets the Pending End Time...


                        this is more specific to a Status...but if you go with what lj is talking about that would be more generic wherein you would have to build a join structure and some workflows which allows you to calc duration of each status....may be lj can throw more lights on these implementation details exactly at this point...

                        • 9. Re: Incident Status Duration Reports

                          Can I carry out these exercies being a Remedy User or do I need to have Administrator access to it. please advise.


                          Also, where would I put the filter 'DB.Status'!="Pending" AND 'TR.Status'="Pending" etc., I am unsure to be honest. Please guide me.


                          Thank You,



                          • 10. Re: Incident Status Duration Reports
                            Ashwanth Padmanabhan

                            Remedy Admin should be doing this...as it involves creation of field and workflows ....as an end user you can put in this request to your remedy administrator...he should be able to perform this for u...

                            • 11. Re: Incident Status Duration Reports

                              Hi Ashwanth,

                              I have got Administrator access on UAT Server to do this and test it on my own. Need your help now on how do I start. It will be great if you can explain it to me here or can share ay useful document I can refer to to perform this on my own. Finding out the time spent spent on each status (for an Incident ticket) will help me calculate/audit/reconcile a ticket accuartely and it saved me a lot of time. Appreciate your help on this. Many thanks.



                              • 12. Re: Incident Status Duration Reports

                                Hi lj,


                                I have tried out the idea you posted above through Crsytal Reports. I have added the view HPDHelpDesk_AuditLogSystem from OLE DB twice by linking left outer self join on ORIGINAL_REQUEST_ID field. Could you now advise on how do I look "FROM" and "TO" values please!


                                Many thanks,


                                • 13. Re: Incident Status Duration Reports
                                  LJ LongWing


                                  I don't have a system available to me to to do the testing...but your self join will need to be on something like this


                                  left outer join form on min(request_id) > request_ID and original_request_id = original_request_id


                                  what this will do is make 'this' record join with the 'next' record in the table for the same request.  What you have at that point is the fields from this record (start), and the records from the next record (end)...and you can calculate the time between them because you have both sets of timestamps


                                  Does that help with the concept?

                                  • 14. Re: Incident Status Duration Reports

                                    Hi Keshavamurthy,


                                    I have your same problem, could you paste here the join that you have used to link the two views? Thank you.


                                    I have some issues with the name of the view.