I'm trying to plot count of errors from last week per day and daily average value from month. The result from query below gives me only result from Monday (other dayweeks are missing). What did I wrong?
avg(count) DailyCount Dayweek
6903.6 3730 1 - Mon
index="abc" sourcetype=alarms_log earliest=-30d@d latest=@d
| bucket _time span=1day
| stats count by _time | stats avg(count)
| join
[search index="abc" sourcetype=alarms_log earliest=-7d@d latest=-1d@d
| timechart span=1d count as DailyCount
| eval Dayweek=strftime(_time,"%w - %a") ]
regards,
Szymon
| tstats count where index=_internal earliest=-30d@d latest=@d by _time span=1d
| timewrap 1w
| untable _time days count
| eventstats avg(count) as monthly_avg avg(eval(if(days="latest_week",count,NULL))) as weekly_avg by _time
| dedup monthly_avg weekly_avg
| table _time monthly_avg weekly_avg
It's easy to use timwrap
and untable
Don't use join, you will hit limits if the time window gets big enough and your granularity gets too small. Use some conditional logic instead
index="abc" sourcetype=alarms_log earliest=-30d@d latest=@d
| bucket _time span=1day
| eval one_week_ago=now()-604800
| eval one_mon_ago=now()-2592000
| eval last_week_flag=if(_time>one_week_ago,1,0)
| eval last_mon_flag=if(_time>one_mon_ago,1,0)
| stats count(eval(if(last_week_flag=1,_raw,""))) AS week_count avg(eval(if(last_mon_flag=1,_raw,""))) AS mon_avg_count
Thanks, but it doesn't work correctly. Displays only week_count result, but mon_avg_count is empty. If change function from avg to count for (eval(if(last_mon_flag=1,_raw,""))) I receive the same result as for count(eval(if(last_week_flag=1,_raw,""))).
Opps, we're aggregating on a text value of _raw. Try this
index="abc" sourcetype=alarms_log earliest=-30d@d latest=@d
| bucket _time span=1day
| eval one_week_ago=now()-604800
| eval one_mon_ago=now()-2592000
| eval last_week_flag=if(_time>one_week_ago,1,0)
| stats count(eval(if(last_week_flag=1,_raw,""))) AS week_count by _time
| eval last_mon_flag=if(_time>one_mon_ago,1,0)
| eventstats avg(eval(if(last_mon_flag=1,week_count,""))) AS mon_avg_count
Odd output 🙂 Displays last_mon_flag instead mon_avg_count.
_time week_count last_mon_flag
1 2020-04-25 3 0
2 2020-04-27 58 0
3 2020-05-01 1 0
4 2020-05-04 1 0
5 2020-05-06 1 0
6 2020-05-08 68 0
7 2020-05-13 1 0
8 2020-05-15 11 0
9 2020-05-16 3 0
10 2020-05-19 1 0
join
needs field-name
reference: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join
Thanks. This below gives me cycles per a weekday. Could you point me how to make 30 daily average as a reference point to daily data? I got now:
Dayweek AvgPerDayWeek DailyCount
1 - Mon 10402.75 3730
2 - Tue 9209.75 3237
3 - Wed 1073 3194
4 - Thu 3688.75 13892
and would like to have:
Format
Preview
Dayweek AvgPerDayWeek DailyCount
1 - Mon 10340.32 3730
2 - Tue 10340.32 3237
3 - Wed 10340.32 3194
4 - Thu 10340.32 13892
Maybe subsearch together with eventstats command will be more useful?
index="abc" sourcetype=alarms_log earliest=-30d@d latest=@d
| timechart count span=1d AS dailycount
| eval Dayweek=strftime(_time,"%w - %a")
| stats avg(dailycount) AS AvgPerDayWeek by Dayweek
| join Dayweek
[search index="abc" sourcetype=alarms_log earliest=-7d@d latest=-1d@d
| timechart span=1d count as DailyCount
| eval Dayweek=strftime(_time,"%w - %a") ]