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!

Introducing the Splunk Community Dashboard Challenge!

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

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Wednesday, May 29, 2024  |  11AM PST / 2PM ESTRegister now and join us to learn more about how you can ...

Get Your Exclusive Splunk Certified Cybersecurity Defense Engineer Certification at ...

We’re excited to announce a new Splunk certification exam being released at .conf24! If you’re headed to Vegas ...