topic Re: How to ignore empty fields in a split in stats/tstats in Splunk Search

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

mciudad — Mon, 17 Aug 2015 16:14:37 GMT

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.

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

woodcock — Mon, 17 Aug 2015 16:18:05 GMT

This is exactly what the fillnull command is for.

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

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

dshpritz — Thu, 03 Mar 2016 21:31:22 GMT

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.

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

piebob — Mon, 14 Mar 2016 20:14:01 GMT

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

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

martin_mueller — Mon, 14 Mar 2016 20:15:15 GMT

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.

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

wrangler2x — Mon, 14 Mar 2016 23:37:23 GMT

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.

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

claudio_manig — Fri, 06 Oct 2017 06:21:54 GMT

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

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

woodcock — Sat, 07 Oct 2017 19:06:52 GMT

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.