Dashboards & Visualizations

Subtract resulting averages from two separate searches

poplajt1
Engager

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 
0 Karma

lguinn2
Legend

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
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...