Calculate percentage from a join query

[justjosh]

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?

[justgrumpy]

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

1. The first stats line gets you a distinct list of product ids that will be needed later for the join.
2. The 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.
3. The join is basically the same except I removed the fields.
4. The next stats line gets a count of the items found from the join as well as carrying over the totalCount from the previous results.
5. The eval gets your percentage of total products that were returned from the initial search.