topic Re: Removing null columns from a table in Splunk Search

Removing null columns from a table

hogan24 — Thu, 04 Jun 2015 14:44:30 GMT

I've found some previous posts with similar questions but the results dont seem to be correct so I'm hoping someone can help with this one...

I have something like this:
mysearch | table column1 column2 column3

With results like this:
column1 --- column2 --- column 3
asdfasdf --- 123456
bsdfasdf --- 234567
csdfasdf --- 345678

Sometimes column3 will have data, sometimes it wont. How (after piping to table) can I remove column3 if it has no data (and preferably keep the columns in order as well)? Thanks.

Re: Removing null columns from a table

MichaelPriest — Thu, 04 Jun 2015 14:48:45 GMT

So when you do the search column 3 sometimes contains data and other times it doesn't, and when it doesn't the column should be removed?

Re: Removing null columns from a table

hogan24 — Thu, 04 Jun 2015 15:21:10 GMT

Correct. This is for a dashboard view that I will be turning over to users so I want to simplify the view if a column does not have data.

Re: Removing null columns from a table

jeffland — Thu, 04 Jun 2015 15:21:43 GMT

If you don't want to show column 3, don't include it in your table command.

Other than that, how do you think it would work? You can either have a column or don't have it, but you can't have it in some results only - although in a way, that is exactly what happens with a row which is empty in column 3 - it doesn't have a value for column 3. If you could try to show me an example of how the table would look like the way you want it, that would be great.

If you don't want to display empty fields, you could use

your search | table column1 column2 column3 | fillnull column3 ("No entry for column 3")

to have them display that text instead.

Re: Removing null columns from a table

hogan24 — Thu, 04 Jun 2015 16:32:14 GMT

@jeffland, this is a comment, not an answer. can you delete this as an answer?

"...how do you think it would work?" That's what i'm asking.

I want to include it in the table b/c it may have data. I want to include all columns that may have data. Then remove the column if it does not have data.

Re: Removing null columns from a table

mrobichaud_splu — Thu, 04 Jun 2015 20:15:12 GMT

This works...

 your search | fields column1 column2 column3 | table * | fields - _*

However, do you really need to pipe to table?

If you just show events and format it as a table, then the extra column would disappear. In the search, you can select "Table" on the events tab. Unfortunately, if you save this as a dashboard panel the settings are getting lost. In SimpleXML, here are the settings you need:

<panel>
  <event>
    <search>
      <query>your search</query>
    </search>
    <option name="count">10</option>
    <option name="rowNumbers">0</option>
    <option name="type">table</option>
    <fields>column1, column2, column3</fields>
  </event>
</panel>

Note, this method would also include _time.

Re: Removing null columns from a table

jeffland — Fri, 05 Jun 2015 05:15:34 GMT

Let me rephrase that question: What do you want to see?

I can't imagine how a table would look that has a third column sometimes and sometimes it doesn't. Should the first page of results contain such a column while the second page doesn't?

Re: Removing null columns from a table

pj — Tue, 22 Dec 2015 16:39:45 GMT

mysearch | table column1 column2 column3
| streamstats count as temp_count
| stats values(*) as * by temp_count
| fields - temp_count

The streamstats gives each row a unique number
The stats values will remove null columns

Re: Removing null columns from a table

arorarahul23 — Wed, 08 Feb 2017 07:40:00 GMT

Above answer is not working in splunk 6.5 version

Re: Removing null columns from a table

jonasmeier — Thu, 23 Nov 2017 19:39:12 GMT

Here's a solution that eliminates empty columns AND keeps the order of the columns. As table is often used to define the order of fields, keeping it is essential. Tested about a dozen possibilities and the only generic one I found (where you don't have to know the column names) is using transpose, which eliminates empty columns when creating rows of it:
`| makeresults count=3
| rename comment as "generate some test data"
| streamstats count
| eval f2=if(count=2,"YYY",null), f3=if(count=3,"ZZZ",""),f5="XXX",f6=null
| rename comment as "f1, f4 and f6 have no values in any row"
| table f6 f5 f4 f3 f1 f2 count

| rename comment as "now the transpose magic"
| transpose
| transpose header_field=column
| fields - column`

Re: Removing null columns from a table

romanwaldecker — Fri, 09 Feb 2018 08:04:52 GMT

Awesome answer - simple and works very well! Should be accepted as the right answer imho.
The only downside to consider: The transpose commands demand a lot of computing power = time.

Re: Removing null columns from a table

romanwaldecker — Mon, 27 Aug 2018 07:18:01 GMT

This should be a comment to the mentioned answer rather than another 'answer'. Very misleading as the order of answers changes.

Re: Removing null columns from a table

jadomaai — Wed, 16 Oct 2019 08:10:57 GMT

There's a simple way to remove columns with all null values using foreach:

if you have cols a, b and c and c has all NULL values:

| makeresults 
| eval a=10
| eval b=20
| eval c=30
| append 
    [
    | makeresults
    | eval a=100
    | eval b=200
    | eval c=300
    ]
| append 
    [
    | makeresults
    | eval a=1000
    | eval b=2000
    | eval c=3000
    ]
| table a b c
| eval c=NULL
| rename * AS X_*_NEW
| foreach X_*_NEW 
    [ eval <<MATCHSTR>>=<<FIELD>> ]
| fields - X*

will leave cols a and b, as during the eval Splunk removes any col which is all NULL values.

Re: Removing null columns from a table

nick405060 — Wed, 08 Jan 2020 00:48:27 GMT

This solution does not work for every possibility. e.g. I am tabling winevent data for multiple eventcodes. It seems that if a field has null data for just one data point, when the rest are populated, then that entire field is not displayed. Have tried this every way I can and it still excludes multiple fields of mine that contain data.

Re: Removing null columns from a table

nick405060 — Wed, 08 Jan 2020 00:50:05 GMT

This reorders your columns so in a dashboard it is not much of a solution 😕

Re: Removing null columns from a table

nick405060 — Wed, 08 Jan 2020 00:59:00 GMT

This also reorders your results

Re: Removing null columns from a table

nick405060 — Wed, 08 Jan 2020 21:38:59 GMT

Bumping this.

Re: Removing null columns from a table

jadomaai — Wed, 08 Jan 2020 23:20:43 GMT

Fair point about the order. Hadn't picked up on that constraint. Can't find a way to preserve the order when running a search in the search editor, but have a solution for a dashboard context by virtue of using a token to preserve the desired order. Arguably, this shouldn't work, as we're effectively 're-creating' the lost columns by running the tokenized fields command (try opening the second panel's search in the search editor to see what I mean)... but for some reason this works in the context of the dashboard. For anyone who doesn't have access to _internal index, substitute your own search and modify the relevant commands in the base search to make the following work

<dashboard>
  <label>remove_null_cols_and_keep_column_order</label>
  <search id="baseSearch">
    <query>
      index=_internal sourcetype=scheduler earliest=-1m
    | table _time host user concurrency_category status scheduled_time run_time result_count
    | eval user = NULL
    | eval scheduled_time = NULL
    | eval _flds = "| fields _time host user concurrency_category status scheduled_time run_time result_count"
    </query>
    <done>
      <condition match="$job.resultCount$!=0">
        <set token="displayFieldsInOrder_tok">$result._flds$</set>
      </condition>
    </done>
    <sampleRatio>1</sampleRatio>
  </search>
  <row>
    <panel>
      <title>Original table of results showing all columns including 'user' and 'scheduled_time' which are set to all NULL values</title>
      <table>
        <search base="baseSearch"></search>
        <option name="count">5</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">false</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Results after removing NULL cols</title>
      <table>
        <search base="baseSearch">
          <query>
            <![CDATA[
               | rename * AS X_*_NEW
               | foreach X_*_NEW 
               [ eval <<MATCHSTR>>=<<FIELD>> ]
                 $displayFieldsInOrder_tok$
               ]]>
          </query>
        </search>
        <option name="count">5</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">false</option>
      </table>
    </panel>
  </row>
</dashboard>

Re: Removing null columns from a table

pj — Thu, 09 Jan 2020 02:10:04 GMT

To you perhaps, but the question only expressed a preference to keep the order, it wasn't mandated.

Re: Removing null columns from a table

jonasmeier — Thu, 09 Jan 2020 07:53:05 GMT

Use | transpose 0, which indicates that the number of rows to transpose is unlimited. (and be carefull with event counts > 10000, it trashed my browser)