I need to calculate the percentage of products that I have searched for that exist in a specific product catalog.
I have written an inner join, which gets all the product_ids from the search and does as inner join into the product catalog:
sourcetype=products | fields product_id |join product_id [search sourcetype=catalogue |fields product_id ] | stats count
I added stats count which gives the number of products from the search in the catalog.
How can I modify the query so it returns the count as a percentage of total products that were returned from the initial search?
I'm not sure if there is some built-in function or command to get what you want, but I believe the following query will get you close:
sourcetype=products
| stats dc(product_id) as count by product_id
| eventstats dc(product_id) as totalCount
| join product_id [search sourcetype=catalogue]
| stats count as lookupCount values(totalCount) as totalCount
| eval percTotal=lookupCount/totalCount
stats
line gets you a distinct list of product ids that will be needed later for the join
.eventstats
line adds a count of the number of products found to each result from the previous stats
. This is needed later for the calculation of percentage of total products.join
is basically the same except I removed the fields
.stats
line gets a count of the items found from the join as well as carrying over the totalCount from the previous results.eval
gets your percentage of total products that were returned from the initial search.