How to overlay/combine line charts with two different time spans?

[josephinemho]

I have two line charts I'd like to display in one view, but I'm having trouble combining them because they're using different time spans.

The first chart is
index=os
| search sourcetype=cpu cpu=all host=$server$
| eval Percent_CPU_Load = 100 - pctIdle
| timechart avg(Percent_CPU_Load) as "Avg CPU"

Which gives me this:

The second chart is
index=os
| search sourcetype=cpu cpu=all host=$server$
| eval Percent_CPU_Load = 100 - pctIdle
| timechart values(Percent_CPU_Load) as "Actual CPU" span=5min

Which gives me this:

I'd like to combine the two, so that my users can see the actual CPU activity for this server, but also see the trend when it is averaged out. Any help would be much appreciated!!

[niketn]

@josephinemho what do you mean by two separate time span. Does it imply that they have same time filter but only spans are different i.e. 5 min and default span based on time selected? Or are they running for two different time period and also with two different span?

What is the time selected for both searches? For us to assist you better with your requirement kindly provide more details.

Before we suggested you solution for your problem, two try out following couple of query optimization tips:

1) Add search filter to base search rather than second pipe (With search optimization enabled by default Splunk should correct this for your however, it is better if you wrote the better query upfront).

index=os | search sourcetype=cpu cpu=all host=$server$ 

Should be replaced with

index="os" sourcetype="cpu" cpu="all" host="$server$" 

2) eval can be performed after timechart on reduced number of aggregated rows:

<yourBaseSearch>
| timechart span=5min max(pctIdle) as "pctIdle"
| eval "Actual CPU" = 100 - pctIdle
| fields - pctIdle

PS: Changed from values() to max(), because in case there are more than one event in 5 min window then the query will not work as expected as it will result in multi-value field for values() function.

[josephinemho]

Hi @niketnilay, I'd like to have the same time filter and search query, but only the time spans are different. The time selected for both searches will change depending on the filter selected.

Also, I used values() because I'm looking at CPU data, and oftentimes CPU jumps from low to high within a time span, so I wanted to capture it all. That's why one of the chart is using the average and the other is using all its values. I wanted to overlay them so we can easily see all the CPU activity of a server, but also use the average line chart to see any overall trends.

Hi @gpradeepkumarreddy I believe chart overlay doesn't work if the charts are using different time spans (at least I have not gotten it to work with different spans).

Thanks!

[pradeepkumarg]

You can use chart overlay to create a secondary y-axis
Details here - http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchTutorial/Chartoverlays