How to do cross validation and counts between two search queries using Multisearch

Question

Hello Everyone I hope everyone is doing well...

It turns out I have to find how many times a custumer that has made a purchase has contacted the corporate line to complain... I can generate a table that shows me the custumers that have made an actual purchase by ID , and also I can make a table of the custumer that have called on the line to make a complain..

first table would look like this:

ID    PRODUCT_BOUGHT
41545    x_98
1428     x_98
4856     x_91
8596     x_91
1254     x_96

and the second table would look like this..

ID     CASE_NUMBER
41545     001
4856      002
4856      003
41545     004
1254      005
1254      006

The issue is that I need to count how many times each ID has called on the line and bring also the product bought and the case number recieved on the line... BUT I can only think of a multiseach in order to create the table but I cant seem to find any documentation on how to do the cross validation or even count and I feel like Im hitting my head against a wall...

This is the multisearch that I am using:

| multisearch
[| search index="auxpik"
 | search status="PAY.ok"
 | fields ID PRODUCT_BOUGHT]
[|search index="auxpik"
 | search in_calls="corp_cx_cases")
 | fields ID CASE_NUMBER]

but since I am a python user trying to learn splunk I cant seem to find a way to obtain this table:

desired results:

ID       CALLS_ON_THE_LINE    PRODUCT_AND_CASES
41545         2                x_98-001-004
4856          2                x_91-002-003
1254          2                x_96-005-006
1428          0                   x_98
8596          0                   x_91

THank you a million to everyone that can help me out with a guidance or documentation on how to achieve this like form the bottom of my hart thank you so much!!!!!! Im sending you a big hug from Texas!

Answer 1

Multisearch shouldn't be necessary. Try this

(index="auxpik" status="PAY.ok") OR (index="auxpik" in_calls="corp_cx_cases")
| fields ID PRODUCT_BOUGHT CASE_NUMBER
| stats values(*) as * by ID

If you really want to use multisearch then the stats command should produce the results you want.