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!

Discover Powerful New Features in Splunk Cloud Platform: Enhanced Analytics, ...

Hey Splunky people! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2408. In this ...

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...