Splunk Search

Multiple stacked columns in timechart

alexr
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

jonuwz
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

jonuwz
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

0 Karma

jonuwz
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

Rocket66
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

alexr
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

jonuwz
Influencer

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

0 Karma

alexr
New Member

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

0 Karma

rechteklebe
Path Finder

Could you please provide your search?

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...