Search code examples
splunksplunk-query

How to structure a splunk query to generate a count of events where the field is either null or not null?

I'm working with some access logs that may or may not have a user_name field. I don't need to do anything fancy, I'd just like to generate a single query that returns a stats table containing a count of events where this field is either null or not null. For example, my log is structured like this:

<timestamp><field1><field2><user_name><field4>

For anonymous connections, user_name is not logged, so these values are null. I can get all of the non-null values easily enough:

<base_query> user_name="*" | stats count

This gives me a nice table of the non-null user_name field:

count
------
812093

I can also get a count of the null fields with a little more work, but this seems messy:

<base_query> | fillnull user_name value=NULL| search user_name=NULL | stats count

And then I get a count of entries with a null user_name field. 

count
-----
31215

However, what I'm really looking for is a single query that combines both of these into a single stats table, ideally:

not_null | null
----------------
812093   | 31215

Thanks!

Solution

  • There is a function "isNull(field)" , you can use to evaluate that and then count 

    index=indxname search_condition
| eval countByFieldExists=if(isnull(field),"notExist","Exists") 
| stats count by countByFieldExists

    Something like this should work for you