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.
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
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
Thanks a lot, let me check it... I just need the first one so I can use the | head 1 | command 🙂
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
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.
Its just showing me: no result found.
Ok I'm sorry I cannot check now, I will try to take a look later today for you.
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.