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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...