Subtract resulting averages from two separate searches

[poplajt1]

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 

[lguinn2]

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