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.
Hi
| 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
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.
How about something like
| where isnotnull(*)
?
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
Or
| fillnull value=“NULL”
| search NOT NULL
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.
tks, but they don't work.