Splunk Search

question on stats and blank values

jiaqya
Builder

i have a table like below.
cola:colb:colc:cold
1::2:3:
::::
1:2:3:4

when i do a stats , i only get non-null values
is it possible to show null values in the form of table and eval them to 0...

0 Karma
1 Solution

to4kawa
Ultra Champion
index=yourIndex 
| fillnull cola colb colc cold
| rest_your_search

try, simply.

View solution in original post

0 Karma

to4kawa
Ultra Champion
index=yourIndex 
| fillnull cola colb colc cold
| rest_your_search

try, simply.

0 Karma

jiaqya
Builder

fillnull works for me.. thanks..

0 Karma

marycordova
SplunkTrust
SplunkTrust

It depends on your stats.

This returms all the values, regardless of null:

<base search>
| fields cola colb colc cold
| stats values(*) as *

<output>
cola colb colc cold
1    2    3    4

This returns only the values where cold is not null:

<base search>
| fields cola colb colc cold
| stats values(*) as * by cold

<output>
cold
4

The important thing about the by clause in the stats is that it will omit any log events where the fields in that by clause are null, so if you had 2 fields both must be populated for results to be returned, if one of the fields in the by clause is null that log event will not be present in your result set.

If you do the fillnull as per the other suggestion you would get this:

<base search>
| fillnull value="0"
| fields cola colb colc cold
| stats values(*) as *

<output>
cola colb colc cold
0    0    0    0
1    2    3    4

So really, the point is, what do you want the data to look like at the end, what is it you're trying to ask, what is the story you're trying to tell?

@marycordova
0 Karma

jiaqya
Builder

Thanks for responding . My situation is really the "by" one.. ( | stats values(*) as * by cold)

when i do the stats by, i lose anything that had null value.
but we want to see those null ones and they should have value=0

we are looking at non-compliant numbers and null basically means there are none , so its a good situation, but we are not seeing it in stats.

so instead of dissapearing , they should show as 0.
will that be possible if i do fillnull value=0 for all the fields before i do a stat by ?
would this made the stats by to show everything....

0 Karma

marycordova
SplunkTrust
SplunkTrust

also, post your current spl to get better help 🙂

@marycordova
0 Karma

jiaqya
Builder

fillnull works for me, unfortunately i can mark only 1 answers are "Accept". both your answers were correct..

0 Karma

to4kawa
Ultra Champion

How about fillnull ?

jiaqya
Builder

will try this...

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...