Splunk Search

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

cmeyers
Explorer

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!

0 Karma
1 Solution

chimell
Motivator

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.

View solution in original post

0 Karma

chimell
Motivator

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.

0 Karma

cmeyers
Explorer

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
Motivator

Hi
i rectified just re-try

0 Karma

Phil219
Path Finder

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

0 Karma

chimell
Motivator

good thanks
don't forget to vote

0 Karma

somesoni2
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...