topic Re: How to express multiple average windows in a table form? in Splunk Search

How to express multiple average windows in a table form?

peiffer — Thu, 12 Jan 2023 20:38:02 GMT

I am having trouble expressing multiple average windows in a table form. My table shows the same values for myval, five_min_val, fifteen_min_val for each host. I can get some of what I want from timechart and trellis layout on each of the aggregations for a single host, but I really would like to look at the data across hundreds of hosts, where the value is above some threshold over 15 minutes. I tried trendline and sma5, sma15 to represent the 5 min and 15 min simple moving averages with similar effect.

Please enlighten me?

<base search> | fields _time host myval | bins span=1m _time | streamstats window=5 avg(myval) as five_min_val by host | streamstats window=15 avg(myval) as fifteen_min_val by host | stats latest(myval) as myval latest(five_min_val) as five_min_val latest(fifteen_min_val) as fifteen_min_val by host | table host myval five_min_val fifteen_min_val

Re: How to express multiple average windows in a table form?

yuanliu — Fri, 13 Jan 2023 10:09:43 GMT

First, I think you mean

| streamstats time_window=5m avg(myval) as five_min_val by host | streamstats time_window=15m avg(myval) as fifteen_min_val by host

Secondly, you need to illustrate what the actual output from your code and illustrate the desired output, explain any difference that is not obvious to volunteers who are unfamiliar with your original data.

Re: How to express multiple average windows in a table form?

peiffer — Mon, 16 Jan 2023 23:41:24 GMT

You were absolutely right on the first point. I did drop the minute modifier on the time_window after contorting my example multiple times and not making any progress and as a result made typos.

On the second point, below are the example searches and the outputs. Note, I did change the stats to use aggregator avg() instead of latest() from the streamstats. Now changing the aggregator was actually what was needed, solving my own problem, I think. I believe the issue was my use of latest load, 5min load, 15min load was the cause of the problem. Please validate my thinking.

How would getting the below example searches and output to compare or contrast help you enlighten me?

<mysearch>
| bin span=1m _time
| eval load = round(PercentUserTime + PercentSystemTime, 1)
| eval host="foo"
| streamstats time_window=5m avg(load) as five_min_load by host
| streamstats time_window=15m avg(load) as fifteen_min_load by host
| timechart span=1m partial=false avg(load) as load avg(five_min_load) as five_min_load avg(fifteen_min_load) as fifteen_min_load by host

_time  fifteen_min_load: foo  five_min_load: foo load: foo
2023-01-16 16:46:00    5.3    5.1    5.5
2023-01-16 16:47:00    5.3    5.5    17.2

<mysearch>
| bin span=1m _time
| eval load = round(PercentUserTime + PercentSystemTime, 1)
| eval host="foo"
| streamstats time_window=5m avg(load) as five_min_load by host
| streamstats time_window=15m avg(load) as fifteen_min_load by host
| stats latest(_time) as _time avg(load) as load avg(five_min_load) as five_min_load avg(fifteen_min_load) as fifteen_min_load by host
| table _time host load five_min_load fifteen_min_load

_time  host   load   five_min_load  fifteen_min_load
2023-01-16 16:55:00    foo    4.9    5.5    5.4

Re: How to express multiple average windows in a table form?

yuanliu — Tue, 17 Jan 2023 06:40:49 GMT

Semantically, if you use latest in streamstats, Splunk should return the latest value of that field in the past 5 minutes, 15 minutes, 1 hour, or 15 hours, whichever the time_window is. You would not expect the latest value to be different just because time_window is different. I think this concludes the original question about why you saw identical values with very different time_window.

I am not sure what the other question is. Timechart and stats perform different operations, therefore they will give different results. This is not related to how you use them. (The documents include some useful examples.) If you are wondering why the groupby field (host) got represented in column names in timechart while they appear as a separate column in stats, you can try to redesign timechart (whose purpose is to plot values on time scale) and see if you can come up with a better representation.

Re: How to express multiple average windows in a table form?

bowesmana — Tue, 17 Jan 2023 06:51:07 GMT

Looks like you misunderstand the use of bin.

Using bin with _time+stats is only useful if you stats by _time also

In your second search if you want 1 minute latest intervals by time you must also split by time, otherwise the bin command is redundant

| stats latest(_time) as _time avg(load) as load avg(five_min_load) as five_min_load avg(fifteen_min_load) as fifteen_min_load by _time host