Splunk Search

Removing all-null columns from stats table

PickleRick
SplunkTrust
SplunkTrust

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

Labels (1)
Tags (2)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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 🙂

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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 🙂

Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...