Splunk Search

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

sidsinhad
Engager

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?

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Path Finder

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.

0 Karma

mschaaf
Path Finder
| 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_groupdc(splunk_server)values(splunk_server)
Group13

Server1

Server2

Server3

null3

Server4

Server5

Server6

0 Karma

sidsinhad
Engager

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
0 Karma

woodcock
Esteemed Legend

You can do this:

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

0 Karma

sidsinhad
Engager

Awesome! Thanks woodcock

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...