I have a table of end times and a table of start times. I want to get the change in average response time from day to day (or from week to week, month to month, etc.) Response time being the difference between start_time and end_time. I want to use this search to create Single Value Trend dashboards.
Some of the entries will have the same md5. I think I’m close. I can get the average response time from beginning until now and from beginning until a set latest=-nd, in two separate searches. I can display those results in separate events or in separate fields.
I cannot seem to display the answer for subtracting the two resulting averages. That is Difference=Today-Yesterday. What am I missing? I’ve looked at a bunch of examples that try to do similar things with COUNT but I can’t get mine to work.
Search with resulting averages in their own field:
index="end_time" OR index="start_time" latest=-7d
|spath md5sum
|stats range(_time) by md5sum
|stats avg(range(_time)) AS Yesterday
|append
[search index="end_time" OR index="start_time"
|spath md5sum
|stats range(_time) by md5sum
|stats avg(range(_time)) AS Today
]
|eval change = (Yesterday-Today)
Search with resulting averages in their own event:
index="end_time" OR index="start_time" latest=-7d
|spath md5sum
|stats range(_time) by md5sum
|stats avg(range(_time))
|eval ReportKey="Yesterday"
|append
[search index="end_time" OR index="start_time"
|spath md5sum
|stats range(_time) by md5sum
|stats avg(range(_time))
|eval ReportKey="Today"]
|eval change = (Yesterday-Today)
index="end_time" OR index="start_time" , Sample Data:
12/9/2014 end_time
50:57.0
12/9/2014 start_time
50:53.0
12/9/2014 end_time
50:29.0
12/9/2014 start_time
50:26.0
12/9/2014 end_time
50:22.0
12/9/2014 start_time
50:19.0
12/9/2014 end_time
50:16.0
12/9/2014 start_time
50:13.0
12/9/2014 end_time
49:55.0
12/9/2014 start_time
49:52.0
12/9/2014 end_time
10:55.0
12/9/2014 start_time
10:47.0
12/9/2014 end_time
31:42.0
12/9/2014 start_time
23:24.0
12/5/2014 end_time
01:01.0
12/5/2014 start_time
00:53.0
12/5/2014 end_time
00:42.0
12/5/2014 start_time
00:34.0
12/5/2014 end_time
48:31.0
12/5/2014 start_time
48:23.0
12/5/2014 end_time
48:09.0
12/5/2014 start_time
48:01.0
12/5/2014 end_time
30:50.0
12/5/2014 start_time
30:45.0
12/5/2014 end_time
30:29.0
12/5/2014 start_time
30:24.0
12/5/2014 end_time
29:35.0
12/5/2014 start_time
29:30.0
Try this
index="end_time" OR index="start_time" earliest=-7d
|spath md5sum
|stats range(_time) as timerange by md5sum
|stats avg(timerange) AS ThisWeek
|append
[search index="end_time" OR index="start_time"
|spath md5sum
|stats range(_time) as timerange by md5sum
|stats avg(timerange) AS Today
]
| stats first(Today) as Today first(ThisWeek) as ThisWeek
|eval change = (ThisWeek-Today)
But this is even faster, and actually compares yesterday and today. (Your search above compares last week.) Also, you should be using earliest=
in your search, not latest=
index="end_time" OR index="start_time" earliest=-2d
| spath md5sum
| eval Timespan = if (_time>(now()-86400),"Today","Yesterday")
| stats range(_time) as timerange by md5sum Timespan
| stats avg(timerange) as AvgTime by Timespan
| transpose 2
| where column="AvgTime"
| rename "row 1" as "Today" "row 2" as "Yesterday"
| eval Change = Yesterday - Today