topic Re: How do I display values of two fields in a stacked column chart? in Splunk Search

How do I display values of two fields in a stacked column chart?

russell120 — Wed, 29 Aug 2018 14:23:33 GMT

My intent of this panel is to show the proportion of Compliant IPs (a field) to their respective Total IPs (another field).

With the Visualization > Column Chart selected and the Format Visualization > Stacked Mode > Stack selected this query returns the below chart:

|inputlookup FakeData.csv
|inputlookup append=t FakeData2.csv 
|search System = $system$
|chart list("Compliant IPs") over "Control Number"

For additional context, Compliant IPs and Total IPs are fields with integer values, and Control Number is a field with string values (despite the field name). What query do I need to display the values of Total IPs for each respective series/column over top the orange columns in the current chart?

Side note: I'm unsure why the Control Number values do not show along the x-axis of this column chart. Perhaps because there are 40 values for Control Number. I'm currently unable to get them to appear.

Re: How do I display values of two fields in a stacked column chart?

niketn — Wed, 29 Aug 2018 20:20:29 GMT

@russell120 can you add some sample data for Control Number and Compliant IPs. Your current query does not have a mention of Total IP. Please provide sample data/Field Name for that as well. Mock up/Anonymize any sensitive data.

FYI, If your csv data has one to one mapping for Compliant IPs and Total IPs for each Control Number, then list() will work otherwise it will show multiple values which can not be plotted on chart.

For rotating x-axis labels use the following Simple XML Chart configuration:

    <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
    <option name="charting.axisLabelsX.majorLabelStyle.rotation">-90</option>

Re: How do I display values of two fields in a stacked column chart?

russell120 — Thu, 30 Aug 2018 13:04:14 GMT

Sure, please view the anonymized table below here.

https://pasteboard.co/HBzluq1.png

Re: How do I display values of two fields in a stacked column chart?

niketn — Thu, 30 Aug 2018 13:49:35 GMT

@russell120 please find below a sample dashboard based on cooked up data from sample you have provided. I have calculated Non Compliant IPs so that Stack represents Total IPs.

Following is the Simple XML Code for run anywhere dashboard with stack mode turned on and all data labels displayed i.e. <option name="charting.chart.showDataLabels">all</option>
PS: The SPL commands from |makeresults till | extract pairdelim="," kvdelim="=" are used for generating sample data as per your requirement. You can use your original search instead.

<dashboard>
  <label>Stacked Column Chart</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults
| fields - _time
| eval data="\"Control Name\"=\"AC-2.1\",\"Compliant IPs\"=22,\"Total IPs\"=187;\"Control Name\"=\"AC-2.3\",\"Compliant IPs\"=22,\"Total IPs\"=187;\"Control Name\"=\"AC-11\",\"Compliant IPs\"=81,\"Total IPs\"=146;\"Control Name\"=\"AU-2\",\"Compliant IPs\"=91,\"Total IPs\"=168;\"Control Name\"=\"AU-3\",\"Compliant IPs\"=1,\"Total IPs\"=184;\"Control Name\"=\"BU-3.1\",\"Compliant IPs\"=120,\"Total IPs\"=149;\"Control Name\"=\"BC-2.1\",\"Compliant IPs\"=22,\"Total IPs\"=187;\"Control Name\"=\"BC-2.3\",\"Compliant IPs\"=22,\"Total IPs\"=187;\"Control Name\"=\"BC-11\",\"Compliant IPs\"=81,\"Total IPs\"=146;\"Control Name\"=\"BU-2\",\"Compliant IPs\"=91,\"Total IPs\"=168;\"Control Name\"=\"BU-3\",\"Compliant IPs\"=1,\"Total IPs\"=184;\"Control Name\"=\"BU-3.1\",\"Compliant IPs\"=120,\"Total IPs\"=149;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| extract pairdelim="," kvdelim="="
| eval NonCompliant_IPs=Total_IPs-Compliant_IPs
| stats last(Compliant_IPs) as Compliant_IPs last(NonCompliant_IPs) as NonCompliant_IPs by Control_Name</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">-90</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">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.showDataLabels">all</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.chart.style">minimal</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="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>

Re: How do I display values of two fields in a stacked column chart?

bgennerm — Tue, 29 Sep 2020 21:05:38 GMT

yeah just like @niketn said...

For me it even worked with just a table of those 3 fields (|table Control_Name, Compliant_IPs, Total_IPs), as long as Control_Name is unique, otherwise just use | stats sum(Compliant_IPs), sum(Noncompliant_IPs) by Control_Name. Noncompliant_IPs is ofc Total_IPs minus Compliant_IPs.

and I would recommend to not use any spaces in field names.

Try to use stack 100% to make it more visible, if it should only represent the percentage.

Re: How do I display values of two fields in a stacked column chart?

russell120 — Thu, 30 Aug 2018 15:37:04 GMT

This works, thank you. I actually found the solution a little bit ago with some trial and error, but your answer provided additional insight into working with these column graphs. I appreciate it.