How to do cross validation in Splunk with a multisearch

[andres91302]

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="pur.ok"
| search status="PAY.ok"
| fields ID PRODUCT_BOUGHT]
[|search index="corp_line"
    | 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!

[andres91302]

Hello @ITWhisperer  thank you SO MUCH for helping me out..  I recieved an error from splunk letting me know that a stats comand cannot be put inside a multisearch, .... Is there a way to solve this limitation? thank you so much again for helping me out

[ITWhisperer]

How about something like this

| multisearch 
[| search index="pur.ok"
| search status="PAY.ok"
| fields ID PRODUCT_BOUGHT
| rename PRODUCT_BOUGHT as PRODUCT_AND_CASES]
[|search index="corp_line"
    | search in_calls="corp_cx_cases"
| fields ID CASE_NUMBER
| rename CASE_NUMBER as PRODUCT_AND_CASES]
| stats count(eval(if(index="pur.ok"))) as CALLS_ON_THE_LINE values(PRODUCT_AND_CASES) as PRODUCT_AND_CASES by ID
| eval PRODUCT_AND_CASES=mvjoin(PRODUCT_AND_CASES, "-")

[andres91302]

@ITWhisperer  Thank you so much my friend that would be an awesome solution but I may need to list in the future the total of products bought so values will remove duplicates from this solution and also I believe that in some instances a person may by the same product and then complain and thus values will not let me know how many times that product was bouught, thank you for using that eval in the stats count command that is something that I learnt from you and thats totally new... but the issue is that I may also wanna apply this kind of annalysis to other data where the events come from the same index... so that would be confusing..
 I was thinking of something like this..

| stats list(PRODUCT) as PRODUCT list(CASE_NUMBER) by ID 

but splunk in some instances displays some werid outcome such as:

12538
out_ct.command.search.calcfields
12501
out_ct.command.search.fieldalias
12501
out_ct.command.search.lookups
12501
out_ct.command.search.tags
12501
out_ct.command.search.typer
12501
out_ct.command.spath
12501
prereport_events

😞 I trully trully feel happy and grateful for your help ! thank you so much I may will gie this another try before quitting 

[ITWhisperer]

Try something like this

| multisearch 
[| search index="pur.ok"
| search status="PAY.ok"
| fields ID PRODUCT_BOUGHT
| stats values(PRODUCT_BOUGHT) as PRODUCT_AND_CASES by ID]
[|search index="corp_line"
    | search in_calls="corp_cx_cases"
| fields ID CASE_NUMBER
| stats count as CALL_ON_THE_LINE values(CASE_NUMBER) as PRODUCT_AND_CASES by ID]
| stats sum(CALLS_ON_THE_LINE) as CALLS_ON_THE_LINE values(PRODUCT_AND_CASES) as PRODUCT_AND_CASES by ID
| eval PRODUCT_AND_CASES=mvjoin(PRODUCT_AND_CASES, "-")

Essentially, the stats in the first search gathers the products bought, the stats in the second search counts the calls and gathers the case numbers, the stats outside the multisearch adds the call counts (there should only be zero or one count anyway) and gathers the products and case numbers, and finally, the product and case numbers as joined as you have shown