topic Re: How to display only column(s) that has value greater than 0 in Splunk Search

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

Cbr1sg — Thu, 12 Apr 2018 05:22:24 GMT

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

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

cmerriman — Thu, 12 Apr 2018 11:42:14 GMT

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

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

Richfez — Thu, 12 Apr 2018 11:48:48 GMT

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

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

damien_chillet — Thu, 12 Apr 2018 12:31:34 GMT

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

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

HeinzWaescher — Thu, 12 Apr 2018 12:36:17 GMT

Try add this to your base search

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

Cbr1sg — Fri, 13 Apr 2018 01:57:39 GMT

works perfectly as expected. Thank you very much!

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

Cbr1sg — Fri, 13 Apr 2018 01:59:19 GMT

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!

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

Cbr1sg — Fri, 13 Apr 2018 02:00:57 GMT

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"

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

Cbr1sg — Fri, 13 Apr 2018 02:03:27 GMT

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.

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

damien_chillet — Fri, 13 Apr 2018 10:38:28 GMT

Yup I've made it a bit over complicated 😄