topic Re: How to remove all null fields to prevent gaps in my table of results? in Splunk Search

How to remove all null fields to prevent gaps in my table of results?

gracemaher — Fri, 12 Jun 2015 13:53:07 GMT

Hi there,
I have a table with four fields inputted, but the issue is that some are blank in some of the events so it has huge gaps!
Is there a way to remove all null fields?

Thanks.

Re: How to remove all null fields to prevent gaps in my table of results?

stephanefotso — Fri, 12 Jun 2015 14:29:10 GMT

Hello! Here is one option: Just say donot display null events where field=blank. ...|where field!=" "

index=_internal sourcetype=*|stats count by sourcetype|where  sourcetype!= " "

Thanks

Re: How to remove all null fields to prevent gaps in my table of results?

gracemaher — Fri, 12 Jun 2015 14:52:22 GMT

Hey Stephanefotso - this doesnt really seem to do anything for me lol...

Re: How to remove all null fields to prevent gaps in my table of results?

masonmorales — Fri, 12 Jun 2015 14:54:27 GMT

yoursearch | fillnull value="NULL" | search NOT NULL

Re: How to remove all null fields to prevent gaps in my table of results?

gracemaher — Fri, 12 Jun 2015 14:56:19 GMT

Same again, doesnt change anything in the table. 😞

Re: How to remove all null fields to prevent gaps in my table of results?

masonmorales — Fri, 12 Jun 2015 14:58:45 GMT

Sorry to hear that. Can you post the search you are using to create the table and some sample data please?

Re: How to remove all null fields to prevent gaps in my table of results?

stephanefotso — Fri, 12 Jun 2015 15:22:50 GMT

Lets get your search query please.

Re: How to remove all null fields to prevent gaps in my table of results?

gracemaher — Fri, 12 Jun 2015 16:23:54 GMT

Its ok, thank you for your help. had to do it via props.conf 🙂

Re: How to remove all null fields to prevent gaps in my table of results?

fjordz — Wed, 08 Feb 2017 02:03:02 GMT

...|where field!=" " is the one that I'm looking for to delete a value that I don't need in a field. But how about if I have multiple values to delete? How do I do that?

For example:

...| where src!= "N/A" --> removes N/A values

I also want to remove all internal IP addresses under this field. I would like to filter it out by using 10.* but how do I include that in the syntax? Thanks!

Re: How to remove all null fields to prevent gaps in my table of results?

rnotch — Mon, 18 Dec 2017 21:51:30 GMT

What worked for me was something like this:

index=yourindex sourcetype=yoursourcetype | rex [if you are using rex to extract fields, it goes before fix. If not, you can skip this] | search fieldname1=* OR fieldname2=* OR fieldname3=* OR fieldname4=* | stats [or whatever table you are using]

What's happening here is it searches only field names that have a result (the * does not include nulls), and by using "OR" you make sure that if any result is in any of the four fields, that row stays in. This fix might not work well for 50 fields, but it is nice for a few.

Re: How to remove all null fields to prevent gaps in my table of results?

MMCC — Tue, 29 Sep 2020 17:54:28 GMT

Thanks guys this lead me to the my solution. I added a where clause at the end. Currently we have some issues with the network trying to evaluate if there are peaks when the issues occur with tracert
index=main
| eval rtt1_ms=trim(RTT1, " ms")
| eval rtt2_ms=trim(RTT2, " ms")
| eval rtt3_ms=trim(RTT3, " ms")
| table timestamp, HopNr, rtt1_ms, rtt2_ms, rtt3_ms, IP | addtotals fieldname=totalDuration rtt*_ms
| sort timestamp
| fillnull value="NULL"
| where HopNr!="NULL"

Re: How to remove all null fields to prevent gaps in my table of results?

deepak_acalvio — Fri, 09 Feb 2018 07:48:26 GMT

This is the correct answer, worked for me. Thanks for explaining about using OR as well.