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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...