Splunk Search

Removing all-null columns from stats table

PickleRick
Ultra Champion

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
Ultra Champion

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
Ultra Champion

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...