Hi @Splunk3, If you're using Splunk Add-on for ServiceNow, new copies of table records are indexed by Splunk whenever the inputs sees a newer timestamp value in e.g. sys_updated_on. You most likely want to calculate durations from the latest instance of the record indexed by Splunk: index=servicenow sourcetype=snow:change_request short_description IN ("abc", "xyz", "123")
| stats latest(short_description) as short_description latest(eval(strptime(dv_closed_at, "%F %T")-strptime(dv_opened_at, "%F %T"))) as duration_secs by sys_id
| stats avg(eval(duration_secs/3600)) as avg_duration_hours by short_description I'm using stats aggregation eval shortcuts, but at a high level, the search finds the most recent short_description and calculates the most recent duration by sys_id, and then calculates the average duration by short_description. If your changes are opened for long periods of time before approval and implementation, you may prefer to use the work_start and work_end fields or their equivalents, depending on your use case. Most orgs customize the change workflow and schemas to fit their service model.
... View more