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

[sama_hp]

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

[Robbie1194 · Accepted solution]

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.

[Robbie1194]

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.

[sama_hp]

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"

[p_gurav]

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

| stats count by productID

[sama_hp]

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!

[p_gurav]

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

[sama_hp]

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

[p_gurav]

do you want count of purchase happened by each productID?

[sama_hp]

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

[HiroshiSatoh]

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"

[sama_hp]

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

[richgalloway]

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.

[sama_hp]

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"