Splunk Search
Highlighted

How to create an area chart from CSV with multiple data series?

New Member

Hi,
I just started using Splunk and find it to be a very powerful tool but I don't seem to be able to create an Area Chart graph with multiple data series. I know it's possible but difficult and if someone could help me to write a search query it would be very much appreciated.
Basically, I would like to create something like that: https://plot.ly/~tretos/4/ with exact the same data. I already imported the same data to plunk but can't figure this out.

The data I have is:

X - Time
Y1 - L1
Y2 - L2
Y3 - L3
Y4 - Total
0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

SplunkTrust
SplunkTrust

For area chart , your final data should be like this

Time   Series1 Series2 Series3 Total
t1        s11          s21         s31         tot1
t2        s12         s22         s32         tot1
t3        s13         s22         s33         tot1

How does you data look in Splunk and what all fields are available?

0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

New Member

Hi,
Yes, it is like that exactly, if you check that link https://plot.ly/~tretos/4/ on data tab you will see the exact data I have in Splunk.
The data I showed above is the data I would like to have on the graph:
Time on X axis
L1,L2,L3,Total on Y axis.

Sorry for not explaining that better.

0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

SplunkTrust
SplunkTrust

How did you import your CSV data to Splunk, indexed data or as lookup table?

0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

Builder

Hello,

If you add lookup in your app, and chose the area visualisation, you must have what you need:

| inputlookup Ptest.csv

Dashboard Example:

<dashboard>
  <label>Ptest</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| inputlookup Ptest.csv</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">area</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>
0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

New Member

Hi,
Thanks for that, after playing with some pivot tables I managed to get something like this, which is a start, but I still cant add another column to Y axis.

alt text

almost

<panel>
  <chart>
    <search>
      <query>| pivot almost RootObject sum(P_L1) AS "Sum of P_L1" SPLITROW _time AS _time PERIOD auto FILTER Czas is "*" SORT 0 _time ROWSUMMARY 0 COLSUMMARY 0 SHOWOTHER 1</query>
      <earliest>0</earliest>
      <sampleRatio>1</sampleRatio>
    </search>
   <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
     <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
     <option name="charting.axisTitleX.visibility">visible</option>
     <option name="charting.axisTitleY.visibility">visible</option>
     <option name="charting.axisTitleY2.visibility">visible</option>
     <option name="charting.axisX.abbreviation">none</option>
     <option name="charting.axisX.scale">linear</option>
     <option name="charting.axisY.abbreviation">none</option>
     <option name="charting.axisY.scale">linear</option>
     <option name="charting.axisY2.abbreviation">none</option>
     <option name="charting.axisY2.enabled">0</option>
     <option name="charting.axisY2.scale">inherit</option>
     <option name="charting.chart">area</option>
     <option name="charting.chart.bubbleMaximumSize">50</option>
     <option name="charting.chart.bubbleMinimumSize">10</option>
     <option name="charting.chart.bubbleSizeBy">area</option>
     <option name="charting.chart.nullValueMode">gaps</option>
     <option name="charting.chart.showDataLabels">none</option>
     <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
     <option name="charting.chart.stackMode">default</option>
     <option name="charting.chart.style">shiny</option>
     <option name="charting.drilldown">none</option>
     <option name="charting.layout.splitSeries">0</option>
     <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
     <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
     <option name="charting.legend.mode">standard</option>
     <option name="charting.legend.placement">right</option>
     <option name="charting.lineWidth">2</option>
     <option name="trellis.enabled">0</option>
     <option name="trellis.scales.shared">1</option>
     <option name="trellis.size">medium</option>
  </chart>
</panel>
0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

New Member

Ok, I managed to do something like that but I have no idea how I did that or what those commands mean:

alt text

almost

<panel>
  <chart>
    <search>
      <query>| pivot almost RootObject sum(P_L1) AS "Sum of P_L1" sum(P_L2) AS "Sum of P_L2" sum(P_L3) AS "Sum of P_L3" sum(P_total) AS "Sum of P_total" SPLITROW _time AS _time PERIOD auto FILTER Czas is "*" SORT 0 _time ROWSUMMARY 0 COLSUMMARY 0 SHOWOTHER 1</query>
      <earliest>0</earliest>
      <sampleRatio>1</sampleRatio>
    </search>
 <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
     <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
     <option name="charting.axisTitleX.visibility">visible</option>
     <option name="charting.axisTitleY.visibility">visible</option>
     <option name="charting.axisTitleY2.visibility">visible</option>
     <option name="charting.axisX.abbreviation">none</option>
     <option name="charting.axisX.scale">linear</option>
     <option name="charting.axisY.abbreviation">none</option>
     <option name="charting.axisY.scale">linear</option>
     <option name="charting.axisY2.abbreviation">none</option>
     <option name="charting.axisY2.enabled">0</option>
     <option name="charting.axisY2.scale">inherit</option>
     <option name="charting.chart">area</option>
     <option name="charting.chart.bubbleMaximumSize">50</option>
     <option name="charting.chart.bubbleMinimumSize">10</option>
     <option name="charting.chart.bubbleSizeBy">area</option>
     <option name="charting.chart.nullValueMode">gaps</option>
     <option name="charting.chart.showDataLabels">none</option>
     <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
     <option name="charting.chart.stackMode">default</option>
     <option name="charting.chart.style">shiny</option>
     <option name="charting.drilldown">none</option>
     <option name="charting.layout.splitSeries">0</option>
     <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
     <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
     <option name="charting.legend.mode">standard</option>
     <option name="charting.legend.placement">right</option>
     <option name="charting.lineWidth">2</option>
     <option name="trellis.enabled">0</option>
     <option name="trellis.scales.shared">1</option>
     <option name="trellis.size">medium</option>
  </chart>
</panel>
0 Karma
Highlighted

Re: How to create an area chart from CSV with multiple data series?

New Member

alt text

almost3PQcosdotALLcsv

<panel>
  <chart>
    <search>
      <query>| pivot almost RootObject sum(P_L1) AS "Sum of P_L1" sum(P_L2) AS "Sum of P_L2" sum(P_L3) AS "Sum of P_L3" sum(P_total) AS "Sum of P_total" SPLITROW _time AS _time PERIOD auto FILTER Czas is "*" SORT 0 _time ROWSUMMARY 0 COLSUMMARY 0 SHOWOTHER 1</query>
      <earliest>0</earliest>
      <sampleRatio>1</sampleRatio>
    </search>
    <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
    <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
    <option name="charting.axisTitleX.visibility">visible</option>
    <option name="charting.axisTitleY.visibility">visible</option>
    <option name="charting.axisTitleY2.visibility">visible</option>
    <option name="charting.axisX.abbreviation">none</option>
    <option name="charting.axisX.scale">linear</option>
    <option name="charting.axisY.abbreviation">none</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.abbreviation">none</option>
    <option name="charting.axisY2.enabled">0</option>
    <option name="charting.axisY2.scale">inherit</option>
    <option name="charting.chart">area</option>
    <option name="charting.chart.bubbleMaximumSize">50</option>
    <option name="charting.chart.bubbleMinimumSize">10</option>
    <option name="charting.chart.bubbleSizeBy">area</option>
    <option name="charting.chart.nullValueMode">connect</option>
    <option name="charting.chart.showDataLabels">none</option>
    <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
    <option name="charting.chart.stackMode">default</option>
    <option name="charting.chart.style">shiny</option>
    <option name="charting.drilldown">none</option>
    <option name="charting.layout.splitSeries">0</option>
    <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
    <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.mode">standard</option>
    <option name="charting.legend.placement">left</option>
    <option name="charting.lineWidth">2</option>
    <option name="trellis.enabled">0</option>
    <option name="trellis.scales.shared">1</option>
    <option name="trellis.size">medium</option>
  </chart>
</panel>
0 Karma