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

[mciudad]

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.

[martin_mueller · Accepted solution]

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]

This is exactly what the fillnull command is for.

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

[claudio_manig]

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

[wrangler2x]

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.

[dshpritz]

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]

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.

[piebob]

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