Splunk Search

How to ignore empty fields in a split in stats/tstats

mciudad
Explorer

Hi,

I'm trying to find the cardinality of the fields for my indexes. The problem is that some fields sometimes have a value and sometimes they don't, so when I split with tstats/stats using the "by" clause, if one of the fields is empty, it returns nothing.

Example:

| tstats count where index=summary by host

works perfectly. But if I add the field "asset" which for the last 7 days has had no values:

| tstats count where index=summary by host, asset

it returns "No results found".

How can I make Splunk ignore that "asset" field so if it's empty it shows the data with rest of the splits?

Thank you.

Tags (4)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

You can solve this in a two-step search:

| tstats count where index=summary asset=* by host, asset | append [tstats count where index=summary NOT asset=* by host | eval asset = "n/a"]

For regular stats you can indeed use fillnull as suggested by woodcock.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

You can solve this in a two-step search:

| tstats count where index=summary asset=* by host, asset | append [tstats count where index=summary NOT asset=* by host | eval asset = "n/a"]

For regular stats you can indeed use fillnull as suggested by woodcock.

woodcock
Esteemed Legend

This is exactly what the fillnull command is for.

... | fillnull asset | tstats count where index=summary by host, asset

claudio_manig
Communicator

saved a lot of headache on a stats query - thx a lot!

0 Karma

wrangler2x
Motivator

I downvoted this post because just realized this is great for stats (i use it all the time) but not for tstats, which is what is being discussed.

0 Karma

dshpritz
SplunkTrust
SplunkTrust

This doesn't actually work. Per docs:
http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Tstats

The tstats command is a generating command. Generating commands use a leading pipe character. The tstats command must be the first command in a search pipeline, except when (append=true).

As such, to answer above will not work.

woodcock
Esteemed Legend

Yes, I sometimes answer both without reading closely and without testing. I did not notice the t in front of `stats. Everyone commenting is quite correct.

0 Karma

piebob
Splunk Employee
Splunk Employee

i've unaccepted this Answer per discussion, woodcock is going to give it another crack 🙂

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...