CODEWITHSUNDEEP
splunksplunk-query
John
John

Reputation: 3996

Splunk query to get all counts including events (_raw) where match does not exist

How do I get a count of all records for a given field including a count of all records where the field does not exist.

For example:

Given data that generally looks something like this:

{"source_host":"host1", "msg":"some message", "user":"jack"}
{"source_host":"host2", "msg":"some other message", "user":"jill"}

I can get a count of all records like this:

index="my_index" sourcetype=my_proj:my_logs | table _raw | stats count(_raw)

I can get a count of records for a given field like this:

index="my_index" sourcetype=my_proj:my_logs | stats count(_raw) by source_host

Gives a table like this

host       count
host_1     89
host_2     57

But I would like the query to also count records where the field exists but is empty, like this:

{"source_host":"", "msg":"some message", "user":"jack"}

And also count messages like this:

asdf asdf asdf asdf asd fasdfasdfafas 
foo bar
Some other Junk someone wrote to my log

To get a table like this

host       count
host_1     89
host_2     57
null       1
no_def     3

Upvotes: 0

Views: 4427

Answers (3)

Simon Duff
Simon Duff

Reputation: 2651

The following searchv should do it. It creates a new field based on the presence or content of the source host field, and then you just so stats on that. 

index="my_index" sourcetype=my_proj:my_logs | eval src_host=case(isnull(source_host),"Not defined", source_host="", "blank", true(), source_host) | stats count by src_host

Upvotes: 1

RichG
RichG

Reputation: 9926

The case function may help. In this example, if the source_host field doesn't exist (the value is null) then it is set to "no_def"; if the value is the empty string it is set to "null"; otherwise, it's set to itself.

index="my_index" sourcetype=my_proj:my_logs 
| eval source_host = case(isnull(source_host), "no_def", source_host=="", "null", 1==1, source_host)
| stats count() by source_host

Upvotes: 1

warren
warren

Reputation: 33453

Use fillnull thusly (docs.Splunk page for fillnull):

| fillnull value="N/A" <field or field list or leave blank for all fields>

In anything other than trivial searches, however, this will be very timeconsuming

Note: you can use a different value="" if you like

Upvotes: 1

Related Questions