I have the following data. The count field is calculated based on the method, status and date (I would also have the host name in the future). Can you please tell me how to calculate simple moving average based on method, time and status?
Am using the following search:
index=xxx stats count by method, _time, status | trendline sma5(method_CALLS_STATUS) as moving_avg_status
but the moving average is not grouped based on method, time and status.
HTTP Method Date Status Count
GET 1st July 200 10
GET 2nd July 200 20
GET 3rd July 200 15
GET 4th July 200 8
GET 5th July 200 50
GET 1st July 500 15
GET 2nd July 500 85
GET 3rd July 500 55
GET 4th July 500 10
GET 5th July 500 10
POST 1st July 301 15
POST 2nd July 301 15
POST 3rd July 301 85
POST 4th July 301 15
POST 5th July 301 15
Thanks in Advance,
Labora.
Havbe you tried out the streamstats command?
index=xxx stats count by method, _time, status
| streamstats avg(count) As moving_average BY method, _time, status
Hello HeinzWaescher, thanks for the help. Below are my requirements:
The simple moving average needs to be calculated for the method+date+status combination(Example: GET+HTTP status and date. So first these three needs to be grouped before calculating the average. If I apply the query that you pasted,
What is splunk giving now?
api status _time dayCount avgCount
GET 0004 2015-07-04 00:00:00 1 1.000000
GET 0004 2015-07-06 00:00:00 1 1.000000
GET 2061 2015-06-29 00:00:00 48 16.666667
GET 2061 2015-06-30 00:00:00 63 28.250000
GET 2061 2015-07-01 00:00:00 45 31.600000
If you see above, the average is calculated cumulatively. I want to apply moving average by grouping first two rows and then next 3 rows.
What am looking for is to implement a simple moving average first (sma of 30 or 40). Later I would want to move to exponential.
Thx,
Labora.