Splunk Search

How to display only column(s) that has value greater than 0

Cbr1sg
Path Finder

Hi all,
I have table looks like this

Column1,Column2,Column3,....,ColumnX
1,2,0,....5
1,0,5,....3
2,3,0,....0

Sometimes, depending on the search duration, the result could look like this

Column1,Column2,Column3,....,ColumnX
1,0,0,....3
2,0,0,....0
3,0,0,....3

I want to filter the column(s) that has only value "0" inside, so result will only show:
Column1,....ColumnX
1,....3
2,....0
3,....3

Is it possible?

Thanks

Tags (3)
0 Karma
1 Solution

HeinzWaescher
Motivator

Try add this to your base search

| transpose
| addtotals
| search Total>0
| fields- Total
| transpose header_field=column
| fields - column

View solution in original post

HeinzWaescher
Motivator

Try add this to your base search

| transpose
| addtotals
| search Total>0
| fields- Total
| transpose header_field=column
| fields - column

Cbr1sg
Path Finder

works perfectly as expected. Thank you very much!

0 Karma

damien_chillet
Builder

You could use the transpose command to remove columns with only zeros:
(Append to your search)

 | transpose 
 | eval total=0
 | foreach row* [eval total=total + '<<FIELD>>']
 | where total > 0
 |fields - total
 | transpose header_field=column
 | fields - column

Cbr1sg
Path Finder

The solution by HeinzWaescher is shorter and it's not allowed to accept more than 1 answer, but this works as well. Thank you very much!

damien_chillet
Builder

Yup I've made it a bit over complicated 😄

0 Karma

Richfez
SplunkTrust
SplunkTrust

And to be clear - you want to remove columnX for displaying if ALL columnX are zero, but display that column if any value in it is non-zero?

E.g.

1, 4, 0, 0
5, 0, 0, 4
2, 1, 0, 9
4, 0, 0, 0
0, 0, 0, 0

In that case, all 5 rows display, and columns 1, 2 and 4 are the only ones showing? is that right?

And did you specifically want a dot in the column, or how do you want to display it? Not at all? like...
1, 4, 0
5, 0, 4
2, 1, 9
4, 0, 0
0, 0, 0
(With column headers?)

0 Karma

Cbr1sg
Path Finder

Yes, you got my question correctly. The dot is just a way to tell that there are more than 3 columns (could be 4, 5 or 20) in the table.
Yes, the column header needs to be retained at the final result.

0 Karma

cmerriman
Super Champion

why is Column 1 in the final result? There is no 0 in the column.

0 Karma

Cbr1sg
Path Finder

because Column1 doesn't has only value "0" inside.
Column2 and 3 are filtered because all of the values inside those 2 columns are "0"

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...