How to change from join command (executed twice) to stats command?

[tehong]

Hi Experts!

I am trying to REPLACE the join command to the stats command because the subsearch result exceeds 50000.

However, I don't know how to replace the join command to the stats command because this spl uses join twice.

Can you please advise?　Thanks in advance!!

index=myindex sourcetype=A  LogicalName="new_endpoiint"
| join left=L right=R where L.new_contract.Name = R.new_contract_code
[ search index=myindex sourcetype=A LogicalName="new_contract" ]
| join left=L2 right=R2 where L2.R.new_circuit.Name = R2.new_circuit_code
[ search index=cmdb sourcetype=A LogicalName="new_circuit" ]

[bowesmana · Accepted solution]

Use something along the lines of this

(index=myindex sourcetype=A (LogicalName="new_endpoiint" OR LogicalName="new_contract") OR (index=cmdb sourcetype=A LogicalName="new_circuit")
| eval ContractCode=coalesce(new_contractName, new_contract_code)
| eval CircuitCode=coalesce(new_circuitName, new_circuit_code)
| stats values(*) as * by ContractCode CircuitCode

where you are searching initially for data_set_1, data_set_2 and data_set_3. which are your 3 searches. Then you would 'join' the common variables with the coalesce command and the stats command would normally have a split by clause of the join fields, in this case ContractCode and CircuitCode as above.

Hopefully this puts you on the right path

[PickleRick]

I would be a little bit more explicit. If there are no overlappong fields in the events, your code is perfectly fine but we don't know if it is so. So I'd explicitly choose the files for matching

| eval joiner1=case(LogicalName="new_endpoint",new_contract_name,LogicalName="new_co tract",new_contract_code,1==1,null())
| eval joiner2= <same logic but for the other field>
| stats values(*) as * by joiner1 joiner