Splunk Search

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

tehong
Explorer

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" ]
Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...