Splunk Search

Graph weekly average but only show graph for last 24 hours

Communicator

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!

0 Karma
1 Solution

Motivator

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.

View solution in original post

Motivator

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.

View solution in original post

Path Finder

could you help me with this same loggic as you explained but cannot find a fix to this query it is basically add all totalcalls 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$") destdevicetype="hardphone" OR origdevicetype="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 numbermediacallsseconds=number + "::" + callMediaType + "::" + calls + "::" + seconds
| `get
callconcurrency(numbermediacallsseconds)
|
timechartforconcurrency(numbermediacallsseconds,50000)`
| eval day
ofweek =strftime(time,"%a")
| eval hourofday=strftime(time,"%H")
| eval is
businesshours=case((dayofweek=="Sat" OR dayofweek=="Sun"),0,(hourofday>7 AND hourofday<17),1,true(),0)
| search is
businesshours=1
| fields - day
ofweek hourofday isbusinesshours
| untable _time number
mediacallsseconds active
| eval numbermediacallsseconds=split(numbermediacallsseconds,"::")
| eval number=mvindex(numbermediacallsseconds,0)
| eval media=mvindex(number
mediacallsseconds,1)
| eval calls=mvindex(numbermediacallsseconds,2)
| eval seconds=mvindex(number
mediacallsseconds,3)
| eval minutes=round(seconds/60,2)
| eval {media}minutes=minutes
| eval {media}
calls = calls
| eval active=if(active>0,1,active)
| stats values(videocalls) as videocalls values(audiocalls) as audiocalls values(audiominutes) as audiominutes values(videominutes) as videominutes count(eval(active=1)) as active count(eval(active=0)) as inactive by number
|fillnull audiominutes, videominutes, audiocalls, videocalls value="0" | eval totalcalls=audiocalls+videocalls
| eval total
minutes=audiominutes+videominutes
| eval utilization=round(100*active/(active+inactive),2)
| lookup groups number OUTPUT name group subgroup |search number!=OTHER | fields name number group subgroup audiominutes videominutes totalminutes audiocalls videocalls totalcalls utilization
|fields - number group subgroup audiominutes videominutes audiocalls videocalls

0 Karma