Splunk Search

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

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)
1 Solution
Motivator

| transpose
| search Total>0
| fields- Total
| fields - column

Motivator

| transpose
| search Total>0
| fields- Total
| fields - column

Path Finder

works perfectly as expected. Thank you very much!

Builder

You could use the transpose command to remove columns with only zeros:

`````` | transpose
| eval total=0
| foreach row* [eval total=total + '<<FIELD>>']
| where total > 0
|fields - total
| fields - column
``````
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!

Builder

Yup I've made it a bit over complicated ๐

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

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.

Super Champion

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

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"