topic Re: How to remove an entire column from results if all the values of the column are zero? in Splunk Search

How to remove an entire column from results if all the values of the column are zero?

Naren26 — Mon, 09 Oct 2017 05:48:25 GMT

Is there any possibility to remove an entire column if all the values of the column are zero?

Re: How to remove an entire column from results if all the values of the column are zero?

cpetterborg — Mon, 09 Oct 2017 06:15:40 GMT

Column? Is that a column from a CSV? Or do you mean field? Or do you have something else in mind?

Do you want to do this at search time, or index time?

What is the purpose for removing the "column?" (in case there is something else that will do what you want)

Re: How to remove an entire column from results if all the values of the column are zero?

niketn — Mon, 09 Oct 2017 07:50:21 GMT

@Naren26, Please find below one of the options (with a run anywhere example dashboard), which sorts the field you want to check in descending order for all Null values to pick first value and see whether it is null or not.

I have used a base search with id="baseSearch", where search event handler <done> is used to capture the value of field test, using predefined token $result.<fieldname>$ i.e. $result.test$ to access the first row result. If it is null the $partialQuery$ token is set to | fields - test to remove test field in the post-process search.

PS: Just to test the difference of test field with and without values, I have added | eval test="DummyValue" to the base search below. You can remove this pipe to confirm that test field gets removed if all values are null.

<dashboard>
  <label>Remove Column with all Null values</label>
  <search id="baseSearch">
    <query>| gentimes start="10/01/2017"
| eval _time=starttime
| eval test="DummyValue"
| table _time test
| sort - test</query>
    <earliest>0</earliest>
    <sampleRatio>1</sampleRatio>
    <done>
      <!-- Picks only the text field value from first row which is sorted in descending order-->
      <eval token="partialQuery">if(isnull($result.test$)," | fields - test | table _time", " | table _time test")</eval>
    </done>
  </search>
  <row>
    <panel>
      <table>
        <search base="baseSearch">
          <query>$partialQuery$
          </query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

Re: How to remove an entire column from results if all the values of the column are zero?

lfedak_splunk — Tue, 10 Oct 2017 00:13:56 GMT

Hey @Naren26, if @niketnilay solved your problem, remember to "√Accept" an answer to award karma points 🙂

Re: How to remove an entire column from results if all the values of the column are zero?

thaghost99 — Sat, 01 Aug 2020 20:16:21 GMT

hi guys i need help.

index=data sourcetype=data source=data1 TestcaseId=4729484 | eval Timestamp=strftime(Timestamp/1000, "%H:%M:%S %d-%m-%Y")
| eval TCDuration=strftime(TCDuration/1000, "%M:%S")
| eval CC_RecognizedPrompt = a_hlr
| table Description Verdict Timestamp errorText a_number b_number ExternalNumber CC_RecognizedPrompt a_location b_location a_RxLeveld b_RxLeveld TestcaseId OrderId TCDuration

i need help where some of the columns/fields may be empty based on the TestcaseId passed. i need help removing those empty columns and still keep them in the same ORDER as i have listed in the table.

i initially have the last line, but its not in the order i want it to be.

| streamstats count as data | stats values(*) as * by data | fields - data

any help is appreciated.