Splunk Search

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

Explorer

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.

Tags (3)
1 Solution

Explorer

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.

View solution in original post

Explorer

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.

View solution in original post

Explorer

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

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

Explorer

Same again, doesnt change anything in the table. 😞

0 Karma

Influencer

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

0 Karma

Explorer

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

0 Karma

Explorer

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 rtt1ms=trim(RTT1, " ms")
| eval rtt2
ms=trim(RTT2, " ms")
| eval rtt3ms=trim(RTT3, " ms")
| table timestamp, HopNr, rtt1
ms, rtt2ms, rtt3ms, IP | addtotals fieldname=totalDuration rtt*_ms
| sort timestamp
| fillnull value="NULL"
| where HopNr!="NULL"

0 Karma

Motivator

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

New Member

...|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!

0 Karma

Explorer

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

0 Karma

Motivator

Lets get your search query please.

0 Karma