How can i return values for a field that has 0 events?

[jayj]

Here is my query.

sourcetype="access_combined" product_name=* action=purchase
| chart count over product_name by action

When i run this query i only get the product_name's that have any value for purchase. I want to see all of my product_names regardless of whether purchase has any value or not. How can i return these product_name's when the purchase count=0?

I have tried methods listed here and here but they don't seem to work with my logic.

[somesoni2 · Accepted solution]

You've missed the link to the post that you're referring.

The query above only does aggregation on the data that is selected by your base search and within time range. If data for a product_name is not there, it won't show up. Your option would be to supply names of all products in the query, may be using a lookup table (static csv files which will contain all product_name values), like this

sourcetype="access_combined" product_name=* action=purchase | chart count over product_name by action | append [| inputlookup your_product_name_lookup.csv | table product_name] | fillnull value=0 | stats max(*) as * by product_name

[jayj]

Here and Here, not sure why they didn't work.

Thanks for the reply but i think you're misunderstand my question. I have the names of all the products in the time range, its just that none of them are returned that don't have any purchases on them. Maybe there is a different way of getting what i want to achieve.

I would like a report/table/visualisation that shows me all of the products and the amount of times they have been purchased over the last 24 hours. My problem is when the product hasn't been purchased it disappears from chart rather than showing as 0.

[jayj]

Ah sorry i understand what you are saying now. I get the chart i want with this:

sourcetype="access_combined" product_name=* 
| chart count over product_name by action 
| table product_name, purchase 
| sort product_name

I can see all of the products within certain time ranges, but if i reduce it to 15mins some of them disappear as there has been no events for those products in the time frame. So i can see why a lookup with all of the products would fix the problem as it would remain static over the time.

[somesoni2]

When you add action=purchase to your search, the base search sourcetype="access_combined" product_name=* action=purchase will not return product_names which doesn't have a purchase related events, hence they don't show up. For your requirement, I would suggest to try this (will be little in-efficient as it has to process all events now).

sourcetype="access_combined" product_name=* | eval action=if(action="purchase",1,0) | chart sum(action) as purchase by product_name

[Richfez]

Adding directly links won't work until you get more karma (it doesn't take much, I think 40 or 60?)

In the meantime, you can just paste in the urls directly. For everyone's convenience, I grabbed them from your answer:

https://answers.splunk.com/answers/467823/if-there-are-no-results-found-how-do-i-get-my-sear.html?ut...

https://answers.splunk.com/answers/229049/display-a-result-when-the-count-0.html