not sure if I articulated my problem well in the title but let me elaborate here. I need to find where IPs have a daily average count from the past 3 days that is at least 150% larger than a daily average count from the past 7 days. I am looking for spikes in activity based on those two averages. With the way I phrased it, that may sound confusing, but let me show you what I have and why I'm having issues calculating the averages.
| index=blah_blah
| earliest=-7d
| bucket _time span=1d
| stats count by ip _time
| sort ip
| trendline sma3(count) as 3_Day_Average
| trendline sma7(count) as 7_Day_Average
| where 3_Day_Average > 7_Day_Average * 1.5
This provides incorrect averages because if an IP doesn't have a count on a particular day, it won't include that day in the statistics table and it won't be calculated into the average. Instead, it will use a different IP's count to fill in. So if one IP doesn't have a count for 2 of the 7 days for example, then it will take 2 counts from the next IP and calculate that into the average for the original IP that was missing 2 days... I'm hoping that all makes sense. I need the days that don't have counts to still show so that they can be calculated into these averages. If this doesn't make sense to you, feel free to ask questions. I appreciate the help
Instead of stats, try timechart. The timechart command will fill in zeros for spans that have no data.
| index=blah_blah earliest=-7d
| timechart span=1d count by ip
| untable _time ip count
| sort ip
| trendline sma3(count) as 3_Day_Average
| trendline sma7(count) as 7_Day_Average
| where 3_Day_Average > 7_Day_Average * 1.5