How to search for multiple fields with 2 sourcetypes?

[Chandras11]

HI All,

I need to search two sourcetypes and multiple fields at the same time.
Following query is working correctly to find a Main_Ticket C2995A in both source types (below tables).

index="Index_Source" sourcetype="Sourcetype_A" or sourcetype="Sourcetype_B" Main_Ticekt="C2995A"| table  Ticket,Main_Ticket,  Value, Line, LinkedTicket
Sourcetype_A
Ticket  |   Main_Ticket |  Value  | Line |   LinkedTicket
A2345A    | A2345A   |     DES  |   L1     |
C2995B001  | C2995B     |   DTS |   X2     |
C2995A    | C2995A     |   DPU  |   L1   |  Z4563A
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    | C2995A Z4563C 
Z4563A   |   Z4563A   |     SUB  |  S1   | C2995A  Z4563B Z4563C
Z4563C  |   Z4563A     |   SUB  |   S1   |  C2995A  Z4563A Z4563B

However, I also need to check the Linkedtickets from Sourcetype_B as well. if there is a linked ticket, I need to get the information on all linked tickets as well. For example, C2995A is in the linked ticket for Z4563A, so I need to extract the info for Z4563A, Z4563B and Z4563C as well. The value and line for such sub tickets are always "SUB" and "S1". Please note that the SUB ticket information is only in "Sourcetype_B" and the main_tickets are always in "Sourcetype_A". Please also note that linked ticket is a string, containing multiple tickets id.
Thanks a lot for your help.

[DalJeanis · Accepted solution]

Try this -

  (index="Index_Source"  sourcetype="Sourcetype_A" Main_Ticket="C2995A") OR
  (index="Index_Source"  sourcetype="Sourcetype_B" Value="SUB"  Line="S1")
  | fields sourcetype Ticket Main_Ticket Value Line LinkedTicket
  | eval MyTickets = if(sourcetype="Sourcetype_A",Main_Ticket,LinkedTicket)
  | makemv delim=" " MyTickets 
  | mvexpand MyTickets
  | where MyTickets=Main_Ticket
  | fields Ticket Main_Ticket Value Line LinkedTicket

This is based on the assumption that you want all records in Sourcetype A that have Main_Ticket as your value, and you want all Sourcetype B records that have your value as a space-delimited string in the LinkedTicket field. Together, the eval,makemv, mvexpand, and where clauses create a throwaway copy of the LinkedTicket field, turn it into a multivalue field, create one event per value in that field, and keep only the ones that have the desired Main_Ticket value. Then it throws away that field completely, since the data is already still there in the LinkedTicket field.

It might be more efficient if that second line were like this...

 (index="Index_Source"  sourcetype="Sourcetype_B" Value="SUB"  Line="S1" "C2995A")

...but without more information about the underlying data layout, I cannot be sure that would work as desired.

[Chandras11]

Perfect. That's exactly what I was looking for. I can easily check and extract the information for the linked tickets. Thanks a lot.