Splunk Search

Can I use join by using multiple fields from the main search to match a single field on the subsearch?

christopheryu
Communicator

I have a search with the following table as output:

time  customer   circuit_id   parent_circuit   device_card  
8:10   zzzzzzzz  aaaaaaa   bbbbbbbbbbb   ccccccccccc

Is it possible to use the values of the fields "circuit_id", "parent_circuit" & "device_card" using join command (or whatever command will work) to match a single field "prineid" from another index (main) and sourcetype (tickets)? So basically the "prineid" field of index=main sourcetype=tickets can have the values of aaaaaaa OR bbbbbbbbbbb OR ccccccccccc. I want the output/table to include another column "ticket" which is a field from index=main sourcetype=tickets:

time  customer   circuit_id   parent_circuit   device_card  ticket
8:10   zzzzzzzz  aaaaaaa   bbbbbbbbbbb   ccccccccccc  dddd

As additional info, the main search is an alert for an outage and the subsearch looks for any tickets that may have been already opened for the outage.

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

your current search giving fields time,customer,circuit_id,parent_circuit,device_card  
| eval prineid=mvappend(circuit_id,parent_circuit,device_card)
| mvexpand prineid
| join type=left prineid [search index=main sourcetype=tickets | stats values(ticket) as ticket by prineid]
| stats values(ticket) as ticket by time,customer,circuit_id,parent_circuit,device_card 

View solution in original post

somesoni2
Revered Legend

Give this a try

your current search giving fields time,customer,circuit_id,parent_circuit,device_card  
| eval prineid=mvappend(circuit_id,parent_circuit,device_card)
| mvexpand prineid
| join type=left prineid [search index=main sourcetype=tickets | stats values(ticket) as ticket by prineid]
| stats values(ticket) as ticket by time,customer,circuit_id,parent_circuit,device_card 

christopheryu
Communicator

thank you, I replaced that last stats command (5) with the table command I am using that I moved from the main search and adding also the ticket column

0 Karma

somesoni2
Revered Legend

How many rows can be there in the main/alert search?

0 Karma

christopheryu
Communicator

Multiple, can be up to hundreds. There are also other columns on the table from the main search and sometimes the circuit_id and/or parent_circuit columns are blanks (these values are derived from lookup table).

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...