Reporting

How to show only non-null values in a table

andres91302
Communicator

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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>>)]

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

andres91302
Communicator

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

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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>>)]
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...