How to show only non-null values in a table

[andres91302]

Hello everyone hope you are having a good time...

Turns out I am degenerating a table that can contain up to 5 different fields I do so by:

index=x
| fields B D F G C
| stats values(*) as *
| table *

some of this values are going to be either null or empty .. and I cant seem to find a way to make splunk only display fields with non-null non-empty values I know I can "fillnull" but I dont think is the right thing to code something like:

| where isnotnull(B) isnotnull(F) ... isnotnull(G) 

Is there a faster more efficent way to display the fields in the table that are not null or empty thank you so much

[bowesmana]

From your query, you will end up with a single row containing 1-5 fields, 

B D F G C

Each of these will exist if there is ANY event containing at least one of these fields. If no events contain field F, then you will not get field F after your stats command.

If events 1-3 have only this data

Event 1 - D="X"
Event 2 - Does not have D
Event 3 - D="Z"

what do you want to see in your result, as stats values(*) as * will give you the field D with 2 values,  X and Z. You will have no fields B, F, G, C

so, can you clarify what you mean by showing non-null values in the table

[andres91302]

Hello @bowesmana  you are absolutely right! I have correct myself the issue is that some null values will actually have the string "null" or "NULL" and that is what I do not want to show.. some Fields will have a string or a value but others will have the string "NULL" or "null" I do not want to show them if thats the value... I wonder if there is a way to tell splunk something like this:

| Where (*)!="NULL" OR (*)!="null"

I know that is incorrect.. but I want splunk to show me all fields that do not have the string "null" or "NULL".. please note that this table. Will only give me one value per fields so no problems on that part... Thank you so much for helping me out you are such a smart splunker

[ITWhisperer · Accepted solution]

Do you just want to remove "null" and "NULL" values from your fields?

| foreach B D F G C
    [| eval <<FIELD>>=if(match(<<FIELD>>,"(?i)null"),null,<<FIELD>>)]