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 !
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...
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?
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...
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
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...
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.
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 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...
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.
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...
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.
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!
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?
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.