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.
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
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
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.
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.
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
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/229049/display-a-result-when-the-count-0.html