topic Re: How to display count from last week and avg from last month on one timechart in Splunk Search

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

slipinski — Wed, 30 Sep 2020 05:30:02 GMT

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

regards,
Szymon

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

to4kawa — Mon, 18 May 2020 10:11:27 GMT

join needs field-name

reference: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

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

slipinski — Wed, 30 Sep 2020 05:30:09 GMT

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

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

skoelpin — Mon, 18 May 2020 14:40:52 GMT

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

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

slipinski — Wed, 30 Sep 2020 05:28:09 GMT

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

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

skoelpin — Tue, 19 May 2020 15:51:11 GMT

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

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

to4kawa — Tue, 19 May 2020 20:08:51 GMT

| 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

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

slipinski — Wed, 30 Sep 2020 05:30:57 GMT

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