Splunk Search

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

russell120
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
1 Solution

niketn
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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

bgennerm
Engager

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.

0 Karma

niketn
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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

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

niketn
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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

russell120
Communicator

Sure, please view the anonymized table below here.

https://pasteboard.co/HBzluq1.png

0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...