Parameter passing between 2 searches as input as well as output

[Chandras11]

HI All,

I need to give input from search1 to search2 and then get a single result from search 2 with the values from search 1.

For example, in the tables below, the correct Main_Ticket for Z4563A/B/C/* is C2995A. To find it, first I need just first 5 Character from the Sourcetype_B Ticket (Z4563), Then I need to pass it to another query, where I can search Z4563 in the Sourcetype_A linked tickets. If found, I need to return Sourcetype_A Ticket as output(Here C2995A).

 Sourcetype_A
 Ticket  |   Main_Ticket |  Value  | Line |   LinkedTicket
 A2345A    | A2345A   |     DES    |   L1       |
 C2995B001  | C2995B     |   DTS    |   X2       |
 C2995A    | C2995A     |   DPU    |   L1     |  Z4563A, C2995A001, C2995B001
 C2995A001 |  C2995A   |     DTS    |   X2    |

 Sourcetype_B
 Ticket    | Main_Ticket |    Value  | Line   | LinkedTicket
 A2345A002  | A2345A    |    DES    |   L1    |   
 C2995B002  | C2995B     |   DTS    |   X2      | 
 C2995A003  | C2995A      |  DPU     |  L1       |
 Z4563B     | Z4563A    |    SUB  |    S1    | Z4563A Z4563C 
 Z4563A   |   Z4563A   |     SUB     |  S1   | Z4563B Z4563C
 Z4563C  |   Z4563A     |   SUB    |   S1   |  Z4563A Z4563B

First I tried with eval and subquery as:

index="Index_Source" sourcetype="Sourcetype_B" SUB | rename Ticket as B_Ticket | 
eval Main_Ticekt_5=substr(B_Ticket,1,5) | table  Main_Ticekt_5 | 
eval B_MAIN_TIcket = [ search sourcetype="Sourcetype_A" | rename Ticket as A_Ticket | 
 rename LinkedTicket as A_LinkedTicket | search( A_LinkedTicket=*$Main_Ticekt_5$*) |
 eval B_SUB_MAINTICKET="\"$A_Ticket$\"" | 
 return $B_SUB_MAINTICKET ] | table B_Ticket, B_SUB_MAINTICKET

However, It is not working. I read online that it is not possible to pass variables in eval search. Is there any other possible way to do it.
Just a quick note: In such a situation, is it better to use left join or map search? The data given here is a dummy but in real life, I have 10k+ events, where I need to calculate the B_SUB_MAINTICKET.
My apologies for such a long post. Thanks a lot in advance for your help.

[KailA · Accepted solution]

Hi,

I don't really know how to use map so I will try to provide you a working query with a join.

index="Index_Source" sourcetype="Sourcetype_B" SUB 
| rename Ticket as B_Ticket 
| eval Main_Ticket_5=substr(B_Ticket,1,5) 
| table Main_Ticket_5 
| join Main_Ticket_5 
    [ search index="Index_Source" sourcetype="Sourcetype_A" 
    | rename Ticket as A_Ticket 
    | eval Main_Ticket_5 = split(LinkedTicket,",") 
    | mvexpand Main_Ticket_5 
    | table Main_Ticket_5,A_Ticket]

Tell me if it works (hope there is not too much error, I have nothing to test it :p)
Also, just remember than a sub-search can produce up to 50 000 events.

Kail

[Chandras11]

Thanks a lot, let me check it... I just need the first one so I can use the | head 1 | command 🙂

[KailA]

If it works for you with the |head 1, mark the answer as accepted !
Instead, just tell me what's going on, I will try to help you again.

Kail

[Chandras11]

Hi, Sorry for delay but it didnot work at my end. The problem is where are you cheking if Main_Ticket_5 is a substring of LinkedTicket. I need to find the A_Ticket where Main_Ticket_5 is a substring of LinkedTicket.
I was thinking if we can check substring Main_Ticket_5 in Linked ticket.

[Chandras11]

Its just showing me: no result found.

[KailA]

Ok I'm sorry I cannot check now, I will try to take a look later today for you.

[Chandras11]

You helped me already so please don't be sorry. I am not accepting it as of now because it might mislead someone in future. However, Once I solve this issue, I will write it in the comment and accept the answer. Thanks for the helping hand.