Splunk Search
Highlighted

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

Communicator

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"

alt text

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.

0 Karma
Highlighted

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

Legend

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



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

Communicator

Sure, please view the anonymized table below here.

https://pasteboard.co/HBzluq1.png

0 Karma
Highlighted

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

Legend

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

alt text

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>



| eval message="Happy Splunking!!!"


View solution in original post

Highlighted

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

Communicator

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.

Highlighted

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

Engager

yeah just like @niketnilay said...

For me it even worked with just a table of those 3 fields (|table ControlName, CompliantIPs, TotalIPs), as long as ControlName is unique, otherwise just use | stats sum(CompliantIPs), sum(NoncompliantIPs) by ControlName. NoncompliantIPs is ofc TotalIPs minus CompliantIPs.

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.

0 Karma