Splunk Search

Calculate past stats and use them for comparison with current values

Explorer

I have to generate a time chart wherein I have to compare the field named util and check if it is in the range between [avg(util) -
stdev(util), avg(util) + stdev(util)]
. The avg and stdev values must be taken for the past 15 days only. I have extracted the fields avg and stdev but am unable to compare the current values with that. Had tried using subsearches

index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2" | convert num("cpu.kernelSpace") as knum | convert num("cpu.userSpace") as unum| eval util=unum+knum | eval average = if(average > 0, average, 0)[search index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2" | convert num("cpu.kernelSpace") as knum | convert num("cpu.userSpace") as unum| eval util=unum+knum | stats avg(util) as average]
0 Karma
1 Solution

SplunkTrust
SplunkTrust

If I understand correctly, you just want to ensure your field util is within one standard deviation of your average.
Now, you are saying you just want to average the past 15 days, but you need to discretise your data first.

How often is this data being generated?
If more than once a day, which value do you want to take as the most representative for the the day? Do you want the daily max, daily min, daily average?

Also, do you want the moving average for the last 15 days or static average.

if moving average:

index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2"
| convert num("cpu.kernelSpace") as knum 
| convert num("cpu.userSpace") as unum
| eval util=unum+knum
| timechart span=1d avg(util) as daily_util
| streamstats window=15 avg(daily_util) as avg_daily_util, stdev(daily_util) as stdev_daily_util
| eval upper_bound= avg_daily_util + stdev_daily_util
| eval lower_bound=  avg_daily_util - stdev_daily_util
| eval outlier = if(daily_util < lower_bound OR daily_util > upper_bound, 1, 0)
| table _time upper_bound lower_bound daily_util outlier

If you want static average for the past 15 days:

index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2"
| convert num("cpu.kernelSpace") as knum 
| convert num("cpu.userSpace") as unum
| eval util=unum+knum
| timechart span=1d avg(util) as daily_util
| where _time >= relative_time(now(), "-15d@d")
| eventstats avg(daily_util) as avg_daily_util, stdev(daily_util) as stdev_daily_util
| eval upper_bound= avg_daily_util + stdev_daily_util
| eval lower_bound=  avg_daily_util - stdev_daily_util
| eval outlier = if(daily_util < lower_bound OR daily_util > upper_bound, 1, 0)
| table _time upper_bound lower_bound daily_util outlier

In both cases what you do is to calculate the average and standard dev using streamstats (moving) or eventstats (static) first and then use those values to compare with your daily values for util.

timechart is simply grouping your values for field util on a daily basis using average. You can also use other functions such as median (which is more representative against extreme values), max, min, etc. You could also group hourly, every minute, second. Whatever you want, but then you need to ensure the average your are comparing against is also representative too.

Hope that helps.

Thanks,
J

REMEMBER: I am unable to test your exact query in my lab as I don't have access to your data

View solution in original post

SplunkTrust
SplunkTrust

If I understand correctly, you just want to ensure your field util is within one standard deviation of your average.
Now, you are saying you just want to average the past 15 days, but you need to discretise your data first.

How often is this data being generated?
If more than once a day, which value do you want to take as the most representative for the the day? Do you want the daily max, daily min, daily average?

Also, do you want the moving average for the last 15 days or static average.

if moving average:

index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2"
| convert num("cpu.kernelSpace") as knum 
| convert num("cpu.userSpace") as unum
| eval util=unum+knum
| timechart span=1d avg(util) as daily_util
| streamstats window=15 avg(daily_util) as avg_daily_util, stdev(daily_util) as stdev_daily_util
| eval upper_bound= avg_daily_util + stdev_daily_util
| eval lower_bound=  avg_daily_util - stdev_daily_util
| eval outlier = if(daily_util < lower_bound OR daily_util > upper_bound, 1, 0)
| table _time upper_bound lower_bound daily_util outlier

If you want static average for the past 15 days:

index=campaign_prod sourcetype=witness_linux_log host="enviedefraise-mkt-prod1-2"
| convert num("cpu.kernelSpace") as knum 
| convert num("cpu.userSpace") as unum
| eval util=unum+knum
| timechart span=1d avg(util) as daily_util
| where _time >= relative_time(now(), "-15d@d")
| eventstats avg(daily_util) as avg_daily_util, stdev(daily_util) as stdev_daily_util
| eval upper_bound= avg_daily_util + stdev_daily_util
| eval lower_bound=  avg_daily_util - stdev_daily_util
| eval outlier = if(daily_util < lower_bound OR daily_util > upper_bound, 1, 0)
| table _time upper_bound lower_bound daily_util outlier

In both cases what you do is to calculate the average and standard dev using streamstats (moving) or eventstats (static) first and then use those values to compare with your daily values for util.

timechart is simply grouping your values for field util on a daily basis using average. You can also use other functions such as median (which is more representative against extreme values), max, min, etc. You could also group hourly, every minute, second. Whatever you want, but then you need to ensure the average your are comparing against is also representative too.

Hope that helps.

Thanks,
J

REMEMBER: I am unable to test your exact query in my lab as I don't have access to your data

View solution in original post

Explorer

In this if I wish to include some dynamic element such that it creates the bounds as such and now for new samples coming in it checks against these bounds then how do I add the new samples to the above query??

0 Karma

SplunkTrust
SplunkTrust

Hi @AshimaE, sorry but I'm not too sure what you are asking for here.

What is it exactly that you are trying to achieve?
Can you give me an example?

0 Karma