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!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...