Removing all-null columns from stats table

[PickleRick]

I searched if someone had done this already but haven't found a good solution. So I wrote my own and thought I'd share it 🙂

Sometimes you get some stats results which include columns that have null values in all rows. It's a typical result of | rest calls if you're trying to list some splunk objects. It's not that uncommon that out of several dozens or even hundreds of columns you get in your results, many of them are completely empty.

So I thought I'd clean the results so they're easier to browse (and a bit lighter on the internet browser you're using).

[PickleRick]

Moved it into a reply so I can mark it as solution.

Since you can't operate on whole columns in other ways that by manipulating  | fields (or a list of fields with | table), you have to do another trick - transpose, then filter, then transpose back.

<your base search>
| transpose 0
| eval allnulls=1
| foreach row*
  [ eval allnulls=if(isnull('<<FIELD>>'),allnulls,0) ]
| where allnulls=0
| fields - allnulls
| transpose 0 header_field=column
| fields - column

Save it as macro and have your tables cleaned from unneeded null columns 🙂

[PickleRick · Accepted solution]

It seems there is an easier and more elegant solution.

Simply transpose back and forth

<your search>
| transpose 0 include_empty=f
| transpose 0 header_field=column
| fields - column