Splunk Search

How to display count from last week and avg from last month on one timechart

slipinski
Loves-to-Learn Lots

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

Tags (2)
0 Karma

to4kawa
SplunkTrust
SplunkTrust
| 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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma

slipinski
Loves-to-Learn Lots

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,""))).

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma

slipinski
Loves-to-Learn Lots

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

0 Karma

to4kawa
SplunkTrust
SplunkTrust
0 Karma

slipinski
Loves-to-Learn Lots

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") ]

0 Karma

Tune In & Win!

Don't miss out on your
chance to take home free
prizes by helping our players
save the Splunk Cloudom!

Dungeons & Data
Monsters: Splunk O11y
Day Editions Games
stream live:
5/4 at 6:30pm PST
5/5 at 7:00pm PST
on