streamstats multiple moving averages

[philallen1]

My current Splunk search looks like this:

sourcetype="ContributionWebApiUat" DbResponseTime=* | chart values(DbResponseTime) by _time, DbQuery 

This produces a bar chart with 3 types of DbQuery and their associated DbResponseTime plotted over time. Of course, in the future, there may be more than 3 DbQuerys

I now want to plot a line chart which shows a moving average of each DbQuerys response time. This is so that we can see if any queries are getting slower over time, because of the increase in the app's traffic.

I have written a search that compiles the ResponseTimes for all the queries together and gives me a moving average. This is my search to achieve that:

sourcetype="ContributionWebApiUat" DbResponseTime=* | chart values(DbResponseTime) by _time | streamstats avg(values(DbResponseTime)) 

But I want to see a moving average for each individual DbQuery, rather than them all in one result. How do I go about this?

[jonuwz · Accepted solution]

Do you want the moving average of the last X queries by type ? Or the moving average of each query by time ?

If you take the average time for each query every minute, you'll end up with some minutes with 0. If you take the moving average over time, thats going to artificially lower your measurements.

If you take the moving average of the last X queries by type, then the average at 6am might take into account queries that occured hours ago.

Statistically - what are you trying to achieve ?

Here's an example using the average time per query per minute as the starting point :

This : bin _time span=1m chunks the data to the nearest minute.

This : streamstats window=5 global=f avg(avg) as rolling by root takes the average (of the averages) for the last 5 minutes by the 'query' ( in this case root ).

index=_internal sourcetype=*_access
| bin _time span=1m 
| stats avg(spent) as avg by root,_time 
| streamstats window=5 global=f avg(avg) as rolling by root

Now if you dont care about timechart re-averaging your values you can simply do :

| timechart avg(avg) as avg avg(rolling) as rolling by root

If you do care about timechart re-averaging your values :

| eval s="avg rolling"
| makemv s
| mvexpand s
| eval yval=case(s=="avg",avg,s=="rolling",rolling)
| eval s=root.": ".s
| xyseries _time, s, yval
| makecontinuous _time

You'll get far more meaningful readings by using something other than avg though. median or perc95