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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...