https://community.splunk.com/t5/Splunk-Search/Calculate-percentage-from-a-join-query/m-p/106578#M27726 <P>I need to calculate the percentage of products that I have searched for that exist in a specific product catalog.</P> <P>I have written an inner join, which gets all the product_ids from the search and does as inner join into the product catalog:</P> <P><STRONG>sourcetype=products | fields product_id |join product_id [search sourcetype=catalogue |fields product_id ] | stats count</STRONG></P> <P>I added <STRONG>stats count</STRONG> which gives the number of products from the search in the catalog.</P> <P>How can I modify the query so it returns the count as a percentage of total products that were returned from the initial search?</P> Mon, 28 Sep 2020 12:42:39 GMT justjosh 2020-09-28T12:42:39Z https://community.splunk.com/t5/Splunk-Search/Calculate-percentage-from-a-join-query/m-p/106578#M27726 <P>I need to calculate the percentage of products that I have searched for that exist in a specific product catalog.</P> <P>I have written an inner join, which gets all the product_ids from the search and does as inner join into the product catalog:</P> <P><STRONG>sourcetype=products | fields product_id |join product_id [search sourcetype=catalogue |fields product_id ] | stats count</STRONG></P> <P>I added <STRONG>stats count</STRONG> which gives the number of products from the search in the catalog.</P> <P>How can I modify the query so it returns the count as a percentage of total products that were returned from the initial search?</P> Mon, 28 Sep 2020 12:42:39 GMT https://community.splunk.com/t5/Splunk-Search/Calculate-percentage-from-a-join-query/m-p/106578#M27726 justjosh 2020-09-28T12:42:39Z https://community.splunk.com/t5/Splunk-Search/Calculate-percentage-from-a-join-query/m-p/106579#M27727 <P>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:</P> <PRE><CODE>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 </CODE></PRE> <OL> <LI>The first <CODE>stats</CODE> line gets you a distinct list of product ids that will be needed later for the <CODE>join</CODE>.</LI> <LI>The <CODE>eventstats</CODE> line adds a count of the number of products found to each result from the previous <CODE>stats</CODE>. This is needed later for the calculation of percentage of total products.</LI> <LI>The <CODE>join</CODE> is basically the same except I removed the <CODE>fields</CODE>.</LI> <LI>The next <CODE>stats</CODE> line gets a count of the items found from the join as well as carrying over the totalCount from the previous results.</LI> <LI>The <CODE>eval</CODE> gets your percentage of total products that were returned from the initial search.</LI> </OL> Thu, 01 Nov 2012 14:37:50 GMT https://community.splunk.com/t5/Splunk-Search/Calculate-percentage-from-a-join-query/m-p/106579#M27727 justgrumpy 2012-11-01T14:37:50Z