Splunk Search

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

slipinski
Path Finder

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
Ultra Champion
| 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
Path Finder

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
Path Finder

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

slipinski
Path Finder

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...