STRAP IN FOR LONG REPLY
Here's how I'd build it if it were me. I'd create a new Metric Definition for Resolved By.
If you've never used Metric Definitions before... its a mechanism you can use to store records any time a field value changes. You can also get it to run scripts, but that's not what we're doing in this case. Any time a field targeted by a Metric Definition changes, it creates a Metric Instance record.
EXPLAINING METRIC INSTANCES
Here's an example of three Metric Instance records created from my Resolved By Metric Definition.
The bottom record tells us that at 5:40 AM, Resolved By was set to Robert Fedoruk. Now since then (middle record) the Resolved By was blanked out. That ENDED the bottom record since resolved By no longer Robert Fedoruk... so the bottom record metric instance marked Calculation Complete of true.
So at 5:41 the Resolved By was blanked out. But at 5:42 (top record) it was re-resolved by Robert Fedoruk. When that happened, the middle record ended because the value stopped being null. Calculation complete marked as true.
So RIGHT NOW, the Resolved By is still Robert Fedoruk (top record). Calculation Complete is false, because there hasn't been a new change.
BUT I DONT" WANT TO HAVE TO INTERPRET THE HISTORY FOR EACH INC
Build a report on incident_metric table (an OOB join between Incidents and Metric Instances for incidents).
- You want ONLY incident_metric records for the Metric Definition of Resolved By. Remember, metrics for ALL incident metric definitions are stored in metric_instance.
- Created on Today, but plot twist: There's 3 Created fields! Why? Because its a join of three tables: incident, metric_instance, and metric_definition. We want to isolate metric instances created date (mi_sys_created_on). There will be one of these for every change to Resolved By.
- Calculation Complete must be true. If calculation complete was false, this would mean the Resolved By field currently has a value... which means the incident is NOT re-opened.
- Value must be "is not empty". Because we don't care about times when Resolved By was updated to null.
Hi @Garvit1
Hope, the solution was helpful for your main issue, but we can add more following condition for todays resolved incidents.
Hello @Garvit1
You can add filter conditions as per your requirement. Refer the screenshot below:
Unfortunately we don't have OOTB field that captures who resolved the incident initially. To see who resolved the incident initially is something that needs to be stored somewhere. OOTB updated by field gets updated every time the form is updated.
"If you found my answer helpful, please like and mark it as an "accepted solution". It helps others find the solution more easily and supports the community!"
Thank You
Juhi Poddar
