topic Re: moving average query in Splunk Search

moving average query

vw5qb73 — Mon, 29 May 2017 09:15:30 GMT

Hello - I m collecting some user metrics in below format. customer's trVol ( transactionvolume)

2017-05-29 04:50:01,customer=ABC,trVol=2009,elapsedtimeAvg=175.7988
2017-05-29 04:50:01,customer=DEF,trVol=500,elapsedtimeAvg=50.2

Now, i need to identify those customers who is sending 50% or more transactions in last 1 hour compared to the moving average of transaction volume for last 3 weeks

Can you help me with the SPL?

sourcetype= customermetric customer!= Unknown
| streamstats window=6 global=f avg(trVol) as rollingavg by customer

Customer metric is gathered every 10 minutes.

Re: moving average query

Ravan — Thu, 01 Jun 2017 18:28:12 GMT

trendline command along with timechart might work here.

Ex:

sourcetype= customermetric customer!= Unknown   earliest=-3w |bin span=1h
_time | stats sum(trVol) AS volume_hour by user,_time| trendline sma504(volume_hour) AS sma_avg|your logic to caluculate 50 % diff btw hour count vs avg |... search filters

Re: moving average query

DalJeanis — Fri, 02 Jun 2017 03:25:25 GMT

Okay, so you have to calculate two pieces of information -

(1) what is the customer's moving average transaction volume per unit of time for the last 3 weeks
(2) what is the customer's transaction volume for the most recent unit of time.

Your best bet for efficiency will probably be to build a summary index, but let's ignore that for a moment.

You apparently have the calculation for hourly average, so we drop in the 3-week average calculation (I think time_window works better than window for this matter) and then compare...

sourcetype= customermetric customer!= Unknown 
| streamstats time_window=3w avg(trVol) as rollingavg3w by customer
| streamstats window=6 global=f avg(trVol) as rollingavg1h by customer
| where rollingavg1h>1.5*rollingavg3w

Re: moving average query

DalJeanis — Fri, 02 Jun 2017 03:32:50 GMT

What I haven't commented on is the "50% more" part of your query. Unless your customers have VERY steady volumes, you would probably be better off calculating the 95th percentile (or something like that). Run a few runs and compare the p95 to 1.5*avg and see what the shape of your data really is.

 sourcetype= customermetric customer!= Unknown 
 | streamstats time_window=3w avg(trVol) as W3avg  p95(trVol) as W3p95  by customer
 | streamstats window=6 global=f avg(trVol) as rollingavg1h by customer
 | where rollingavg1h>W3p95