Splunk Search

How to edit my search to create a moving average based on 3 parameters?

cameo_cameo
New Member

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.

0 Karma

HeinzWaescher
Motivator

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

cameo_cameo
New Member

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!