Splunk Search
Highlighted

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

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 "circuitid", "parentcircuit" & "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
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Communicator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

Highlighted

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

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