Splunk Search

Multiple stacked columns in timechart

New Member

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.

0 Karma
1 Solution

Influencer

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 :

alt text

and will create a chart like this :

alt text

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

View solution in original post

0 Karma

Influencer

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 :

alt text

and will create a chart like this :

alt text

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

View solution in original post

0 Karma

Influencer

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.

0 Karma

Communicator

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

Thx, Robert

0 Karma

New Member

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.

0 Karma

Influencer

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

0 Karma

New Member

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

0 Karma

Path Finder

Could you please provide your search?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!