Best way to overlay two searches on a graph that share similar X-axis intervals?

[cmeyers]

I would like to have a panel that shows total logs per hour over 24 hours in a column graph, and show the average log per hour over the previous 7 days as a line graph. I have working searches for both, just not sure how to overlay them on top of each other. I am seeing people suggesting appendcols, join, append, etc. I can't quite get them to work for me. I think I am missing the concept. Both searches are have a 24 hour x-axis, so I would imagine it wouldn't be too hard to overlay them? My two searches are:

Column Graph:

device_type=firewall earliest=-1d@d latest=0d@d | timechart span=1h count

Line Graph:

device_type=firewall | timechart span=1h count | eval Hour=strftime(_time,"%H:%M") | stats avg(count) as Avg_count by Hour

Any guidance would be greatly appreciated! Thank you!

[chimell · Accepted solution]

Hi
I'm using splunk 6.3.2
Follow the instructions below

Here is the search to create this chart:

 device_type=firewall earliest=-1d@d latest=0d@d | timechart span=1h useother=f  count as count1 | appendcols [ search  device_type=firewall | timechart span=1h useother=f count as count2 | eval Hour=strftime(_time,"%H:%M") | stats avg(count) as Avg_count by Hour]

You can create the overlay using the Visualization Editor.

1-From the dashboard, clickEdit > Edit Panels.

2-Add a panel specifying the following:
Content Title: Chart Overlay (Dual Axis)
Search String: The search string listed above.
Time Range: .... days.

3-For the chart overlay panel, click the Edit Properties icon. Click Chart
Overlay.

4-Click in the Overlay field. Select Avg_count from the fields available for
selection as an overlay.

5-For View as Axis, click Onto specify a second Y-axis.

6-For Title, click Custom. Type Avg_count in the adjacent text field to specify a title for the second axis..

7-For Scale, click Inherit to inherit the selection for the scale from the first
Y-axis.

8-Click Apply. Click Done.

[cmeyers]

Hi!
So when I get to your step 4, the only option I have is count. Do I need to label something within the appendCols Web Access? Not sure where that come from.

[chimell]

Hi
i rectified just re-try

[Phil219]

I also do not see "Avg_count" option in step 4. What is the fix?

[chimell]

good thanks
don't forget to vote

[somesoni2]

Try something like this

device_type=firewall earliest=-8d@d latest=@d | eval Type=if(_time>=relative_time(now(),"-1d@d"),"Yesterday","LastWeek") | timechart span=1h count by Type | eval Hour=strftime(_time,"%H:%M")  | stats max(Yesterday) as count avg(LastWeek) as Avg_count by Hour