Splunk Search

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

tretos53
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

tretos53
New Member

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

TISKAR
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

tretos53
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

tretos53
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

somesoni2
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

tretos53
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...