How can I report on the date an Incident was reopened?

[scottatah]

We currently make use of the incident.reopened_count field and the Reopen Count business rule.   They essentially increment the reopen_count when the state is changed from Resolved to any other state excluding closed.

I've recently received a request to be able to report on how many incidents were reopened in a particular month.   The problem is, although I can see when a ticket was reopened via the activity log, there's no date tied to the reopening of a ticket and i'm not even sure where that would store.   How would I go about configuring the system to track and thus be able to report on this??

Thanks,
Scott

[iPrashantG]

Hi Scott,

I just checked in Eureka instance as well and this "Reopen Count" field is available OOB on incident table. (please check incident table of your instance as well)

And below is the screenshot of same report created in Eureka instance.

Build name: HEAD (Eureka) Patch 11

Also refer this link of demo instance: ServiceNow

-

Thanks

Prashant

[Paul Porter]

Prashant, it turns out this particular field would only be placed in the Incident table if you were on a particular version when it was made available in an upgrade.   Apparently, the necessary variables were not in line on our side to make the field available.   I'm currently working with support to get the situation remedied.   Thanks again for engaging with me on this.

~Paul

[Subhajit1]

If the requirement is such as you have to provide a report now, then using the approach that Mike have provided can be taken, but please keep in mind that reporting on the Audit tables can have major impact on system performance as these tables tends to contain humongous amounts of data.

Going ahead, it will be pretty helpful for you to create a Time stamp field on your Incident table and populate that field with the Time stamp when the Incident was reopened.

This should be easily configurable by a Before-Update BR:-

current.u_your_field = gs.nowDateTime();

[scottatah]

Thanks Subhajit, this is what I was leaning towards.   My only debate is how would you handle the same incident being reopened multiple times?   This would store the time stamp of presumably the most recent, but the others would be lost.

[Uncle Rob]

I should have read this before replying.   You basically need to create a reporting record each time the event you're trying to track happens.   Luckily SN already has a mechanism for this:   Metric Definitions.

The trick there is that once your Metric Definition is made and creating Metric Instances, you'll have to report on the Incident Metrics table (which is a database view built on a join between Incident, Metric Instance, and Metric Definition).