topic Re: Multiple stacked columns in timechart in Splunk Search

Multiple stacked columns in timechart

alexr — Tue, 16 Apr 2013 11:59:49 GMT

Hi guys,

I have data that reports page views per hour, per type of page (home page, search page, product page). I can draw a chart that displays stacked bars per hour with counts of page views for each of the page type.

Using this data, I can filter to get results only for a specific domain (host), so for example I can get the page view stats per hour, per page for host X. I can do the same then for host Y by changing the filtering.

#   _time                   Home    Search  Details
1   4/2/13 12:00:00.000 AM  8       15      27
2   4/2/13 1:00:00.000 AM   11      23      34
3   4/2/13 2:00:00.000 AM   15      31      43
4   4/2/13 3:00:00.000 AM   5       10      12

The issue I'm having is when I want to display the data from host X and host Y on the same chart to be able to see them side by side. This would imply that data from host X is on a stacked bar, while that from host Y is on a different one.

The tabular data is like this (prefixed with X and Y for the hosts):

#   _time                   X:Home  X:Search    X:Details   Y:Home  Y:Search    Y:Details
1   4/2/13 12:00:00.000 AM  8       15          27          6       13          20
2   4/2/13 1:00:00.000 AM   11      23          34          13      18          27
3   4/2/13 2:00:00.000 AM   15      31          43          17      42          61
4   4/2/13 3:00:00.000 AM   5       10          12          2       5           8

Is this possible? Do you have any suggestions on how it can be done?

Thank you.

Re: Multiple stacked columns in timechart

rechteklebe — Tue, 16 Apr 2013 13:39:26 GMT

Could you please provide your search?

Re: Multiple stacked columns in timechart

jonuwz — Tue, 16 Apr 2013 17:18:21 GMT

Yup. Example :

<?xml version="1.0" encoding="utf-8"?>
<dashboard>
  <label>SuperChart</label>
  <row>
    <chart>
      <searchString>* | timechart count as c | eval c2=c | eval c3=c | eval c4=c | eval c5=c | eval c6=c </searchString>
      <earliestTime>-24h@h</earliestTime>
      <latestTime>@h</latestTime>
      <title>SuperChart</title>
      <option name="charting.data1">view</option>
      <option name="charting.data1.table">@data</option>
      <option name="charting.data1.columns">[0,1,2,3]</option>
      <option name="charting.chart.data">@data1</option>
      <!-- set chart2 to use columns 0,4,5, where 0 is time, 4 + 5 are data -->
      <option name="charting.data2">view</option>
      <option name="charting.data2.table">@data</option>
      <option name="charting.data2.columns">[0,4,5,6]</option>
      <option name="charting.chart2.data">@data2</option>
      <!-- splunk doesnt suppory 2 Y axis, so we have to clone -->
      <option name="charting.axisY2">#axisY</option>
      <!-- clone the Yaxis into axisY2 -->
      <option name="charting.axisLabelsY2">#axisLabelsY</option>
      <!-- clone the Yaxis Labels -->
      <option name="charting.axisLabelsY2.axis">@axisY2</option>
      <!-- associate the new Y2 axis-labels with the Y2 axis -->
      <!--create the 1st chart -->
      <option name="charting.chart">column</option>
      <option name="charting.chart.stackMode">stacked</option>
      <option name="charting.chart.columnAlignment">0</option>
      <option name="charting.axisTitleY.text">CPU</option>
      <option name="charting.chart.columnSpacing">20</option>
      <option name="charting.chart.useAbsoluteSpacing">true</option>
      <option name="charting.axisLabelsY.placement">left</option>
      <!--create the 2nd chart -->
      <option name="charting.chart2">column</option>
      <option name="charting.chart2.stackMode">stacked</option>
      <option name="charting.chart2.columnSpacing">20</option>
      <option name="charting.chart2.useAbsoluteSpacing">true</option>
      <option name="charting.chart2.columnAlignment">0.5</option>
      <option name="charting.chart2.axisY">@axisY2</option>
      <!-- the Yaxis of chart 2 is a link to charting.axisY2 -->
      <option name="charting.axisTitleY2">axisTitle</option>
      <option name="charting.axisTitleY2.text">Memory</option>
      <option name="charting.axisLabelsY2.placement">right</option>
      <!-- throw the structure to layout -->
      <option name="charting.layout.charts">[@chart,@chart2]</option>
      <option name="charting.layout.axisLabels">[@axisLabelsX,@axisLabelsY,@axisLabelsY2]</option>
      <option name="charting.layout.axisTitles">[@axisTitleX,@axisTitleY,@axisTitleY2]</option>
    </chart>
  </row>
</dashboard>

You will need to play with "charting.chartX.columnSpacing" and possibly add "charting.chartX.columnStyle.width" to each of the charts to make it look really pretty though.

The timechart should output rows like this :

and will create a chart like this :

data1.columns and data2.columns define what columns in the search output get added to which stacked bar chart.

Both need column 0 - the time

Re: Multiple stacked columns in timechart

alexr — Wed, 17 Apr 2013 11:48:00 GMT

Perfect. I would like to be able to set this up as a saved report. Is that possible?

Re: Multiple stacked columns in timechart

jonuwz — Wed, 17 Apr 2013 14:43:26 GMT

how do you mean ? The above is a complete dashboard - which can be saved

Re: Multiple stacked columns in timechart

alexr — Thu, 18 Apr 2013 15:23:47 GMT

I meant to have this as a report, not a dashboard. But I will go ahead and mark your response as accepted because it's resolving the issue and it's also useful in terms of explanations. Thank you.

Re: Multiple stacked columns in timechart

Rocket66 — Thu, 20 Feb 2014 12:37:55 GMT

Great Jonuwz!
But:
This works in Splunk 5, but not in Splunk 6 😞
Any idea to have it for Splunk 6!?
Please! 🙂

Thx, Robert

Re: Multiple stacked columns in timechart

jonuwz — Sat, 22 Feb 2014 18:14:01 GMT

Never used splunk 6. From what I've heard, if you use simpleXML in splunk 6 the advanced charting capabilities are disabled. You'd need to convert the dashboard to advancedXML, then change the JSchart module to FlashChart. This will break PDF conversion though.