Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

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

tretos53
New Member
‎05-01-2018 10:38 AM

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
Reply

tretos53
New Member
‎05-02-2018 10:40 AM

alt text

almost3_PQcos_dot_ALL_csv

<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
Reply

TISKAR
Builder
‎05-01-2018 02:42 PM

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
Reply

tretos53
New Member
‎05-02-2018 02:00 AM

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
Reply

tretos53
New Member
‎05-02-2018 03:50 AM

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
Reply

somesoni2
Revered Legend
‎05-01-2018 11:52 AM

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
Reply

tretos53
New Member
‎05-01-2018 01:20 PM

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
Reply

somesoni2
Revered Legend
‎05-01-2018 02:06 PM

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

0 Karma
Reply
Get Updates on the Splunk Community!

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...