Dashboards & Visualizations

How can I create a Multi-series chart with different sized bins

stepheneardley
Explorer

I've a request from the business to produce a chart with multiple series which usually isn't a problem but in this case they've added some complexity by asking for weekly averages on top of daily totals so I'm trying to work with multiple bins with different sizes.

Series1 - Columns showing the number of daily transactions for the last 30 days
Series2 - A line showing the average daily response time for those daily transactions
Series3 - A weekly average line showing the average response time Mon-Fri

The first 2 are easy but I'm having trouble trying to figure out the last bit. The Statistics tab SHOULD look as follows assuming the 1st and 8th are Mondays;

01/08/2016 - 150 - 2.5 - 2.8
02/08/2016 - 124 - 3.1 - 2.8
03/08/2016 - 167 - 2.1 - 2.8
04/08/2016 - 167 - 4.1 - 2.8
05/08/2016 - 167 - 1.3 - 2.8
06/08/2016 - 167 - 2.1 - 2.8
07/08/2016 - 167 - 4.4 - 2.8
08/08/2016 - 150 - 2.5 - 2.7
09/08/2016 - 124 - 3.1 - 2.7 
10/08/2016 - 167 - 1.9 - 2.7
11/08/2016 - 167 - 3.2 - 2.7
12/08/2016 - 167 - 2.9 - 2.7
13/08/2016 - 167 - 2.1 - 2.7
14/08/2016 - 167 - 3.2 - 2.7

I tried building up the search as follows using appendcols to add the weekly averages data.

index=myIndex sourcetype=sometype source=somesource
| bin _time as TIME span=1d
| convert ctime(TIME)
| stats avg(responseTime) as AvgDailyResponse, count as CallsPerDay by TIME
| appendcols [search index=myIndex sourcetype=sometype source=somesource | bin _time AS TIMEW span=1w | convert ctime(TIMEW) | stats avg(responseTime) as AvgWeeklyResponse]

I'm probably close but just can't finish it off. When I run this search I get a new column called AvgWeeklyResponse that only has a value in the first row rather than weekly averages across the days of each week.

0 Karma

sundareshr
Legend

Try this

index=myIndex sourcetype=sometype source=somesource
 | timechart span=1d avg(responseTime) as AvgDailyResponse, count as CallsPerDay
 | eval dow=if(strftime(_time, "%a")="Sat" OR strftime(_time, "%a")="Sun", "Weekend", "Weekday")
 | eval weeknum=strftime(_time, "%W")
 | eventstats avg(eval(if(dow="Weekday", AvgDailyResponse, null()))) as WeeklyAverage by weeknum 
0 Karma

davebrooking
Contributor

Stephen

I don't think your appendcols subsearch is matching the requirement you listed, I don't think it handles the Mon-Fri requirement in item 3. You may have intentionally omitted it to illustrate your thinking.

If you do need a Mon-Fri average, you may also need to use something to identify the week number of the events and in association with the associated day number of an event and ignore those events from the average weekly calculation. This answer may help calculate the week number.

You may also need to use streamstats and not appendcols and a subsearch to calculate the weekly average for the the table.

I'm sorry I'm not able to try either of my ideas at the moment, but I thought I'd post an alternative method as food for thought.

Dave

0 Karma

stepheneardley
Explorer

It's helpful to get knocked off a single train of thought sometimes. I'll look into streamstats and check that link regarding calculating the week number. Appreciated Dave.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...