Create a moving baseline week over week for firewall traffic

[JohnEGones]

Hi Team,

I have been trying to work on a query I found on a blog that was trying to calculate and tag a week over week average of traffic by volume that I was modifying to chart inbound and outbound firewall traffic, but something I cannot see is wrong here.

Specifically, it is doing the calculation, but it seems that the daily_bytes sum and the w_o_w value stay the same, so the table will only ever show LOW.

index=firewall sourcetype=firewall_traffic earliest=-30@d
| bucket _time span=1d
| stats sum(bytes_out) as daily_bytes by _time
| streamstats avg(daily_bytes) as week_avg window=7
| eval w_o_w_change=((daily_bytes - week_avg)/week_avg)*100
| eval state=case(
     w_o_w_change > 50, "CRITICAL - 50%+ increase",
     w_o_w_change > 30, "HIGH - 30%+ increase",
     w_o_w_change > 20, "MED - 20%+ increase",
     1=1, "LOW",)
| table _time, daily_bytes, week_avg, w_o_w_changem state
| sort -w_o_w_change

[tscroggins]

Hi @JohnEGones,

An analysis of week-over-week change over the last 30 days probably requires 44 days of data: 30 days of recent data, 7 days of additional historical data for week-over-week coverage, and another 7 days of historical data for moving averages.

The trendline and autoregress commands are sometimes easier to manage than streamstats. Try:

index=firewall sourcetype=firewall_traffic earliest=-44d@d latest=@d
| timechart span=1d sum(bytes_out) as daily_bytes 
| trendline sma7(daily_bytes) as week_avg 
| autoregress week_avg p=7 
| where _time>=relative_time(now(), "-30d@d") AND _time<relative_time(now(), "@d") 
| eval w_o_w_change=100*(week_avg-week_avg_p7)/week_avg_p7 
| eval state=case(
    w_o_w_change > 50, "CRITICAL - 50%+ increase",
    w_o_w_change > 30, "HIGH - 30%+ increase",
    w_o_w_change > 20, "MED - 20%+ increase",
    1=1, "LOW")
| table _time daily_bytes week_avg w_o_w_change state
| sort - w_o_w_change

[JohnEGones]

Hi ITW,

Thanks for the reply.

The only result I get is one line, and the values in the fields: daily_bytes, week_avg, w_o_w_change, all have the same value. While I do not expect that the weekly average should differ too (low standard deviation) much from a simple mean, I do not think they should be the same value. So, I am trying to understand where the query is wrong.

[ITWhisperer]

Obviously, I don't have access to your data but I ran a similar search over my _internal logs and I get a line for each day in the time period.

index=_internal
| bucket _time span=1d
| stats sum(bytes) as daily_bytes by _time
| streamstats avg(daily_bytes) as week_avg list(daily_bytes) as seven_days window=7
| eval w_o_w_change=((daily_bytes - week_avg)/week_avg)*100
| eval state=case(
     w_o_w_change > 50, "CRITICAL - 50%+ increase",
     w_o_w_change > 30, "HIGH - 30%+ increase",
     w_o_w_change > 20, "MED - 20%+ increase",
     1=1, "LOW")
| table _time, daily_bytes, week_avg, w_o_w_change state
| sort -w_o_w_change

[ITWhisperer]

Your SPL looks fine - are you sure the results aren't correct?

You could try adding a list to see if the values make sense

| streamstats avg(daily_bytes) as week_avg list(daily_bytes) as seven_days window=7