tstats count query 'by' when a field may or maynot exist

[sidsinhad]

I would like to search for events by certain fields, and the field may or may not exist. I want to show all results and if the field does not exist, the value of which should be "Null", and if exists, the value should be displayed in the table.

Example:

| tstat count WHERE index=cartoon channel::cartoon_network by field1, field2, field3, field4

however, field4 may or may not exist. The above query returns me values only if field4 exists in the records. I want to show results of all fields above, and field4 would be "NULL" (or custom) for records it doesnt exist.

Could you please help?

[woodcock · Accepted solution]

The only option is this:

| tstat count values(field4) AS field4 WHERE index=cartoon channel::cartoon_network by field1, field2, field3
| fillnull value="UNKNOWN" field4

[mschaaf]

How about for splunk_server_group, since aggregations are not supported for it? Specifically for versions earlier than 8.0, which now has the fillnull_value="string" parameter.

[mschaaf]

| tstats count by splunk_server splunk_server_group prestats=t
| eval splunk_server_group=coalesce(splunk_server_group, "null")
| stats count by splunk_server_group splunk_server
| stats dc(splunk_server) values(splunk_server) by splunk_server_group

Nevermind. Using prestats=t and an eval worked:

splunk_server_group	dc(splunk_server)	values(splunk_server)
Group1	3	Server1 Server2 Server3
null	3	Server4 Server5 Server6

[sidsinhad]

Brilliant! Thanks a lot woodcock! It worked well.
I have multiple such fields, is this the optimal way of doing it:

| tstat count values(field4) AS field4, values(field5) AS field5 WHERE index=cartoon channel::cartoon_network by field1, field2, field3
 | fillnull value="UNKNOWN" field4 | | fillnull value="UNKNOWN" field5

[woodcock]

You can do this:

... | fillnull value="UNKNOWN" field4 field5 ...

[sidsinhad]

Awesome! Thanks woodcock