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!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...