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
Motivator

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.

Tags (2)
0 Karma

harishalipaka
Motivator

Hi

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
Thanks
Harish

HattrickNZ
Motivator

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.

0 Karma

jkat54
SplunkTrust
SplunkTrust

How about something like

| where isnotnull(*)

?

0 Karma

HattrickNZ
Motivator

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

Or

| fillnull value=“NULL”
| search NOT NULL

0 Karma

xpac
SplunkTrust
SplunkTrust

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.

HattrickNZ
Motivator

tks, but they don't work.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...