Splunk Search

Can you help me make a stacked column chart over time with totals for each row?

drico618
New Member

Let's say I have a lookup table and I have it formatted and "searched" down to:

_time      | Cat_1      | Cat_2      | Cat_3     | Cat_4          | totalCount
2018-04.       1            1          0             5                7
2018-05        2            3          1             0                6 
2018-06        3            1          0             0                4

using:

| inputlookup File.csv
| eval _time=strptime(Date, "%m/%d/%Y") 
| where _time>relative_time(now(), "-5mon@m")
| timechart span=1mon count by "other_field"  
| addtotals fieldname=totalCount Cat_1 Cat_2 Cat_3 Cat_4

What I want is a stacked column chart over time by month. Essentially a way to count the TOTAL number of ALL events from month to month, displaying it as an overlay. I have gotten it working with non-lookups but in this case, it requires a lookup.

I can modify the lookup as needed. I'd like to be able to show the categories per month and then an overlaid line showing a positive upwards trend with the totals. I tried bucket but am not getting the results I desire.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@drico618
Can you please try below XML?

<dashboard>
  <label>dummy1</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>|makeresults | eval _raw="
_time   Cat_1   Cat_2   Cat_3   Cat_4   totalCount
2018-04 1   1   0   5   7
2018-05 2   3   1   0   6 
2018-06 3   1   0   0   4
" | multikv | table time Cat_1  Cat_2   Cat_3   Cat_4   totalCount</query>
          <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.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</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.overlayFields">totalCount</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</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.placement">right</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

drico618
New Member

My concern is that the below query isn't looking at the lookup table which is in CSV format. It's standard comma delimited:

|makeresults | eval _raw=
_time    Cat_1    Cat_2    Cat_3    Cat_4    totalCount
2018-04    1    1    0    5    7
2018-05    2    3    1    0    6 
2018-06    3    1    0    0    4
" | multikv | table time Cat_1    Cat_2    Cat_3    Cat_4    totalCount
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...