Hello,
First, I am aware that there are multiple posts regarding my question, but I can't seem to use them in my scenario.
Please see an example below. There are two fields, location and name. I need to filter out name that contain "2" and stats count name based on location.
I came up with this search, but the problem is it did not include location A (because the count is zero)
Please suggest. I appreciate your help. Thanks
| makeresults format=csv data="location, name
location A, name A2
location B, name B1
location B, name B2
location C, name C1
location C, name C2
location C, name C3"
| search name != "*2*"
| stats count by location
Data
location | name |
location A | name A2 |
location B | name B1 |
location B | name B2 |
location C | name C1 |
location C | name C2 |
location C | name C3 |
Expected output:
location | count(name) |
location A | 0 |
location B | 1 |
location C | 2 |
One way to do it is this
| makeresults format=csv data="location, name
location A, name A2
location B, name B1
location B, name B2
location C, name C1
location C, name C2
location C, name C3"
| search name != "*2*"
| stats count by location
| append
[| makeresults format=csv data="location, name
location A, name A2
location B, name B1
location B, name B2
location C, name C1
location C, name C2
location C, name C3"
| eval count=0
| fields location count
| dedup location]
| stats sum by location
but as @PickleRick said, Splunk is not good with non existent values.
r. Ismo
Hello,
Is there any other way to do this?
The data is dynamic. If I am doing this way, I have to have another process to dump a CSV file.
Thanks
One way to do it is this
| makeresults format=csv data="location, name
location A, name A2
location B, name B1
location B, name B2
location C, name C1
location C, name C2
location C, name C3"
| search name != "*2*"
| stats count by location
| append
[| makeresults format=csv data="location, name
location A, name A2
location B, name B1
location B, name B2
location C, name C1
location C, name C2
location C, name C3"
| eval count=0
| fields location count
| dedup location]
| stats sum by location
but as @PickleRick said, Splunk is not good with non existent values.
r. Ismo
And how is Splunk supposed to know that there should be something there if there isn't? You have to tell it somehow.
If you know that there is alway something with the 2 at the end and it is to be the base of your count, that's a completely different use case and can be done by some groupping and filtering.
Hi,
My understanding is the data is there somewhere, Splunk decided to not display 0 when using stats count.
Thanks
No. Your understanding is wrong. At each step of the search pipeline Splunk only has the data from the previous step in the pipe. So when you filter your data with | search, it onky retains the results matched by your command.
The data from before that command is no longer there so stats cannot use it to group by. So how should it know what are the possible values? Splunk cannot find what is not there so it cannot generate a zero result for a potential non-existing by-field value because it doesn't know what value that would be.
The only case when stats do return a zero value is when you do an overall stats count and you have no results at all.
This is the typical "proving the negative" case. https://www.duanewaddle.com/proving-a-negative/
Append (from a lookup or a here-document), sum and you're set.