Snow ITSM dashboards

[Splunk3]

Hi , 

I have snow data for change requests in splunk, I want to create a dashboard which gives the average duration of change request ( from actual start date and actual end date ) for type of the change . 

type of change can derived from short_description field. 

On y-axis ( average duration ) and on x -axis ( type of change request( short_description) , I have written this query but this is not giving the average duration of change . The result which I am getting is too high , may be its calculating for all the events for same change number . Not sure .

index=servicenow short_description IN ("abc", "xyz", "123")
| eval start_date_epoch = strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S"), end_date_epoch = strptime(dv_closed_at, "%Y-%m-%d %H:%M:%S")
| eval duration_hours = (end_date_epoch - start_date_epoch ) /3600
| eval avg_duration = round (avg_duration_hours, 0)
| stats avg(duration_hours) as avg_duration by change_number, short_description
| eventstats avg(avg_duration) as overall_avg_duration by short_description | eval ocb = round (overall_avg_duration ,0)
| table short_description, ocb 

[tscroggins]

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.