In this blog we are going demonstrate splunk search for stats count to include zero count fields using stats command. Usually Whenever we use stats command we can see only those values which has its count greater than zero. So here we will talk about those fields which does not conatin any values.

Problem Statement for stats count to include zero count in splunk:

Here as we see in the below query we are looking for 4 components that is ClientSessionsManager, ExecProcessor, DateParserVerbose and SavedSplunker.
But currently there is no value for component=ClientSessionsManager, yet we want to display it in the statistics table.

Query Before:

index=_internal earliest=07/11/2022:20:45:00 latest=07/11/2022:21:00:00 
| search component="ClientSessionsManager" OR component="ExecProcessor" OR component="DateParserVerbose" OR component="SavedSplunker" 
| stats count by component 
| addcoltotals count

Note: Remember the Total count in the above image which is 30.

Query After using stats count to include zero count in splunk::

index=_internal earliest=07/11/2022:20:45:00 latest=07/11/2022:21:00:00 
| search component="ClientSessionsManager" OR component="ExecProcessor" OR component="DateParserVerbose" OR component="SavedSplunker" 
| append 
    [ search index=_internal earliest=07/11/2022:20:45:00 latest=07/11/2022:21:00:00 component="ClientSessionsManager" OR component="ExecProcessor" OR component="DateParserVerbose" OR component="SavedSplunker" 
    | eval new_input="ClientSessionsManager,ExecProcessor,DateParserVerbose,SavedSplunker" 
    | eval new_input=split(new_input,",") 
    | mvexpand new_input 
    | rename new_input as component 
    | table component] 
| stats count by component 
| eval count=count-30 
| addcoltotals count

Explanation:

The above problem statement can be solved by using append and you must know the exact values of the field who’s value as zero you are looking for in our case we have 4 component’s and hence we have values in 3 components except 1.

In order to achieve the zero count, we will append a search using the same index name (index=_internal) and filters which were used for the main search, we will create a new field which will include same values as component field which is present in index.

Creating values: | eval new_input="ClientSessionsManager,ExecProcessor,DateParserVerbose,SavedSplunker"

once the exact same field is created which in our case is new_input, we will need to splitting these values in to different events or different lines using split function provided by eval command because if we are not splitting these newly created values will be in the same row and we would not be able to match them further separately with our indexed values.

Splitting & Expanding Values: | eval new_input=split(new_input,",") | mvexpand new_input

once the exact same field is created which in our case is new_input, we will be splitting these values in to different events or different lines using split function provided by eval command.
Now we need to rename the new_input field as component field which is present in index by doing this it will match the new_input field with the component field and the values are combined for both the sub-search and main search.
Table command is purposely added to only focus or match component field with component field and the chances of any errors are very minimal.

Rename & Display: | rename new_input as component | table component

Now here the logic comes in after matching the values with component field and the values which were missing that is ClientSessionsManager is missing is explicitly added in to the result but the count will not be zero as due to these field created by eval and mvexpand the count is duplicated by all the component values.

In order to rectify this as you have already read the NOTE above we have asked you to remember the Total count of all the component which was 30, it was the total count of all the component values in the index.

Proceeding further we will minus the total count of 30 from the count and this will give us the exact count of the values in component field.
Hurrayy!!! We achieved stats count to include zero count in splunk

Minus Count: | eval count=count-30

Hurrayy!!! We achieved our results that is added zero in our component field values.

Also look for Splunk Certification ?

If you are still facing issue regarding stats count to include zero count in splunk Feel free to Ask Doubts in the Comment Box Below and Don’t Forget to Follow us on social platforms, happy Splunking >