topic Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values? in Splunk Search

How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

HattrickNZ — Sun, 13 May 2018 23:09:28 GMT

This is may sample search and ample dataset:

 | makeresults 
    | eval data = "
    1    2017-12-01 00:00:00    A    0    131033    84.1;
   2    2017-12-01 00:30:00    B    0    23627    95;
   3    2017-12-01 00:45:00    C    0    117185    99.6;
   " 
    | makemv delim=";" data 
    | mvexpand data
    | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+-\d+ \d+:\d+:\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
    | fields + date serial type attempts successfullAttempts sr 
    | rename date as _time 
    | search serial=*
|

Which gives:

_time   serial  type    attempts    successfullAttempts sr
1   2017-12-01 00:00:00 1   A   0   131033  84
2   2017-12-01 00:30:00 2   B   0   23627   95
3   2017-12-01 00:45:00 3   C   0   117185  99

How can I remove the field attempts which has all zero values? OR how do I show only fields with non-zero values?

I know I can do fields - attempts, but I am looking for a solution where I can apply it to many fields, and fields that I don't necessarily know the name of.

This answer is close to what I am thinking using transpose, but then I want to convert it back to timechart format with the _time the leftmost column. This answer does successfully find the columns that have non zero values, but it is not in the timechart format that I want.

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

jkat54 — Mon, 14 May 2018 01:21:42 GMT

How about something like

| where isnotnull(*)

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

jkat54 — Mon, 14 May 2018 01:25:04 GMT

| fillnull value=“NULL”
| search NOT NULL

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

HattrickNZ — Mon, 14 May 2018 03:24:16 GMT

tks, but they don't work.

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

xpac — Mon, 14 May 2018 07:52:41 GMT

That will filter the events containing a zero/NULL value... however, the request is to remove columns that contain zero/NULL for all events, and I've to admit nothing useful comes to my head right now.

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

harishalipaka — Mon, 14 May 2018 09:59:19 GMT

have a look into this link

| makeresults 
     | eval data = "
     1    2017-12-01 00:00:00    A    0    131033    84.1;
    2    2017-12-01 00:30:00    B    0    23627    95;
    3    2017-12-01 00:45:00    C    0    117185    99.6;
    " 
     | makemv delim=";" data 
     | mvexpand data
     | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+-\d+ \d+:\d+:\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
     | fields + date serial type attempts successfullAttempts sr 
     | rename date as _time 
     | search serial=* 
| transpose 
| rename "row 1" as f1,"row 2" as f2,"row 3" as f3 
| where f1 !=0 and f2!=0 and f3!=0 
| transpose header_field=column | fields - column

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

HattrickNZ — Mon, 14 May 2018 23:19:32 GMT

This is good, thanks. however,

1/ with | where f1 !=0 and f2!=0 and f3!=0 I lose my _time column and my type column with values A, B and C.

Also, if I can fix 1/ above then I want to be able to write this | rename "row 1" as f1,"row 2" as f2,"row 3" as f3 | where f1 !=0 and f2!=0 and f3!=0 more dynamically that is if I did not know how to many rows there were.

Re: How can I remove the fields X which has all zero values? OR how do I show only fields with non-zero values?

HattrickNZ — Sun, 20 May 2018 22:42:43 GMT

just had another look at this:

if all the values in a field are zero then remove it e.g. fields - fieldname but not sure if it can be done.
foreach * [eval <<FIELD>>=if('<<FIELD>>'==0,"delete",'<<FIELD>>')] | where * != "delete"
OR
where * > 0