topic Re: How to count the number of each distinct Product in the subsearch Splunk example? in Splunk Dev

How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Tue, 29 Sep 2020 18:26:15 GMT

Hello,

I am new to Splunk and trying to figure out how subsearches work.
My problem is : How to count the number of each distinct Product in this Splunk query example:

:sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count AS "Total Purchased", dc(productId) AS "Total Products", values(productId) AS "Products ID" by clientip | rename clientip AS "VIP Customer".

Thanks

Re: How to count the number of each distinct Product in the subsearch Splunk example?

richgalloway — Wed, 14 Mar 2018 01:33:50 GMT

The main thing to remember about subsearches is they execute before the rest of the query. The results of the subsearch then become part of the main query as if you had typed them yourself.

In your example, the subsearch looks for the most common IP address that made a purchase. The main search then looks for all purchases by that IP address and calculates the number of purchases, number of products purchased, and lists those products.

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Tue, 29 Sep 2020 18:26:28 GMT

Thanks for the explanation. However I am looking for a detail list of the count of each product that has been bought by the most common IP address.

I have tried different ways like below query but it does not produce the result that I want.

sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count AS "Total Purchased", dc(productId) AS "Total Products", values(productId) AS "Products ID", count(values(productId)) by clientip | rename clientip AS "VIP Customer"

Re: How to count the number of each distinct Product in the subsearch Splunk example?

HiroshiSatoh — Wed, 14 Mar 2018 07:34:59 GMT

I think that there is no option other than totaling separately for each product.

(your search)
| stats count as "Total Products" by clientip,productId
| addcoltotals labelfield=productId label="Total Purchased"

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Wed, 14 Mar 2018 07:42:07 GMT

I have tried this query too. But It does not return anything!

Re: How to count the number of each distinct Product in the subsearch Splunk example?

p_gurav — Wed, 14 Mar 2018 07:55:45 GMT

do you want count of purchase happened by each productID?

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Wed, 14 Mar 2018 08:02:15 GMT

Correct! I need the count of purchase happened by each productID.

Re: How to count the number of each distinct Product in the subsearch Splunk example?

p_gurav — Wed, 14 Mar 2018 08:14:16 GMT

You can get that by simple adding to your search.:

| stats count by productID

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Tue, 29 Sep 2020 18:26:33 GMT

This is what I tried at first:
sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count AS "Total Purchased", dc(productId) AS "Total Products", values(productId) AS "Products ID" by clientip | rename clientip AS "VIP Customer"| stats count by productId

which doesn't work!

Re: How to count the number of each distinct Product in the subsearch Splunk example?

p_gurav — Wed, 14 Mar 2018 08:31:43 GMT

You already rename productID , so try :
sourcetype=access_ status=200 action=purchase [search sourcetype=access_ status=200 action=purchase | top limit=1 clientip | table clientip] | stats count by productID

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Wed, 14 Mar 2018 08:36:20 GMT

I don't Think that matters since still no result found!

Re: How to count the number of each distinct Product in the subsearch Splunk example?

Robbie1194 — Wed, 14 Mar 2018 11:19:15 GMT

You can try use the MAP command like this:

index=whatever sourcetype=whatever status=200 action=purchase
| top limit=1 clientip
| map search="search index=whatever sourcetype=whatever clientip=$clientip$ | stats count as totalPurchased by productId"

What this does is it will find out the top client IP and then pass that value to the map search and run that and get the info you want. This might not work as I've not tested the logic or anything yet but even if it doesn't, hopefully it points you in the right direction or something.

Re: How to count the number of each distinct Product in the subsearch Splunk example?

Robbie1194 — Wed, 14 Mar 2018 11:20:17 GMT

If you wanted to do this for the top 10 customers then you could just change the limit to 10 in the top command etc etc.

Re: How to count the number of each distinct Product in the subsearch Splunk example?

sama_hp — Tue, 29 Sep 2020 18:27:05 GMT

Thanks Robbie.
I tried the following query based on your suggestion but i'm not sure if I am getting the correct result as the sum of the detailedPurchase does not match the Total Purchased.
Also the result only returns the map search but not the other statistics that I'm asking in my query!

sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count AS "Total Purchased", dc(productId) AS "Total Products", values(productId) AS "Products ID" by clientip | map search="search sourcetype=Access_* clientip=$clientip$ |stats count AS detaiedPurchase by productId"