I need to create a webi report which shows the re-opened tickets with re-open timestamp and corresponding close timestamp.
I can fetch the data from activity log. If a ticket is re-opened multiple times, I can show all the re-open timestamp,but the only thing is, I need to show the corresponding close date as well. Right now, I can only show the final close date, not all the intermediate close dates.
How can I achieve that from activity log?
Any help for the same?
I assume you're using the close_date directly on the ticket and that's why you only get the latest close date. If you insist on doing this with the activity log you'll have to look for the proper activities and depending on your configuration it might be the status update (ST), resolved (RE) or close (CL). As you already dig up the reopens then just add the suitable activity to your filter to get the intermediate close dates.
A more flexible solution would be to sift through the audit log entries and look for the status changes, that way it is easier to get only the data you want (filter on attr_name = 'status' and then look for the fitting attr_before_val and attr_after_val).
Whichever way you choose, good luck!
Can anyone please share some idea how this can be achieved?