Graph weekly average but only show graph for last 24 hours

[AlexMcDuffMille]

I currently have a graph that shows the number of events over the last 24 hours by host. I've also included streamstats to create a running average over the last 24 hours. There are two things that I'm wondering:

1. Is it possible to also put a Weekly_Running_Average line on the same graph as well? This way I can view the daily average and the weekly average simultaneously.

2. Is this a true running average over the last 24 hours, or do I need to extend my graph to go back 48 hours so that I'm getting a full 24 hours worth of data? Right now my two average lines are identical so I'm assuming I'm only getting a full 24 hour average point for the most recent data point.

My code looks like this:

(search for number of events) | timechart span=30m count by host | addtotals | streamstats window=48 avg(Total) as "Total Daily Running Average" | streamstats window=336 avg(Total) as "Total Weekly Running Average"

Thanks!

[emiller42 · Accepted solution]

keep in mind that streamstats does not override the timeframe of the search. If you're only getting 24 hours of data in your results, streamstats will not change that.

To bring in data from a different timeframe, you're going to need to use a subsearch. For example:

index=_internal | join [search index=_internal earliest=-7d | bucket _time span=1h | stats count by _time | stats avg(count) as weekly_avg ] | timechart span=1h count avg(weekly_avg) as weekly_avg

In the above, the subsearch runs over a 7 day timeframe, regardless of what the containing search is set for. So it will gather hourly counts for the past week, average that into a single metric, and then join that with every result of the containing search. Then when you do the final timechart, you end up with your moving count overlaid with the weekly metric.

It is worth noting that the above example may not necessarily be performant, as it's potentially pulling a lot of data. But it does illustrate the concept. In practice, cases like this are prime candidates for summary indexing. You would collect the longer term metrics you care about into a summary index on an interval, and then you could simply reference the summary data in your subsearch, as opposed to having to re-pull the raw events. It's much faster that way.

[splunkuseradmin]

could you help me with this same loggic as you explained but cannot find a fix to this query it is basically add all total_calls by name. when you select in time picker for one day 2 days or last 7 days so on....
I want to get total_calls per day and show in a graph for 30 days.

cdr_events
(callingPartySubgroup="$selectgroup$" OR originalCalledPartySubgroup="$selectgroup$" OR finalCalledPartySubgroup="$selectgroup$") dest_device_type="hardphone" OR orig_device_type="hardphone" duration>0
| eval number=mvappend(if(callingPartySubgroup="$selectgroup$",callingPartyNumber,null()), if(originalCalledPartySubgroup="$selectgroup$", originalCalledPartyNumber,null()), if (finalCalledPartySubgroup="$selectgroup$", finalCalledPartyNumber, null()))
| mvexpand number
| search number=*
| fillnull callMediaType value="unknown"
| eventstats dc(callId) as calls sum(duration) as seconds by number callMediaType
| eval number_media_calls_seconds=number + "::" + callMediaType + "::" + calls + "::" + seconds
| get_call_concurrency(number_media_calls_seconds)
| timechart_for_concurrency(number_media_calls_seconds,50000)
| eval day_of_week =strftime(_time,"%a")
| eval hour_of_day=strftime(_time,"%H")
| eval is_business_hours=case((day_of_week=="Sat" OR day_of_week=="Sun"),0,(hour_of_day>7 AND hour_of_day<17),1,true(),0)
| search is_business_hours=1
| fields - day_of_week hour_of_day is_business_hours
| untable _time number_media_calls_seconds active
| eval number_media_calls_seconds=split(number_media_calls_seconds,"::")
| eval number=mvindex(number_media_calls_seconds,0)
| eval media=mvindex(number_media_calls_seconds,1)
| eval calls=mvindex(number_media_calls_seconds,2)
| eval seconds=mvindex(number_media_calls_seconds,3)
| eval minutes=round(seconds/60,2)
| eval {media}_minutes=minutes
| eval {media}_calls = calls
| eval active=if(active>0,1,active)
| stats values(video_calls) as video_calls values(audio_calls) as audio_calls values(audio_minutes) as audio_minutes values(video_minutes) as video_minutes count(eval(active=1)) as active count(eval(active=0)) as inactive by number
|fillnull audio_minutes, video_minutes, audio_calls, video_calls value="0" | eval total_calls=audio_calls+video_calls
| eval total_minutes=audio_minutes+video_minutes
| eval utilization=round(100*active/(active+inactive),2)
| lookup groups number OUTPUT name group subgroup |search number!=OTHER | fields name number group subgroup audio_minutes video_minutes total_minutes audio_calls video_calls total_calls utilization
|fields - number group subgroup audio_minutes video_minutes audio_calls video_calls