Getting results from multiple searches without append or join

[Chandras11]

Hi All,

I have 2 sourcetypes as following:-

Sourcetype_A
 Ticket | Main_Ticket | Value | Line  | LinkedTicket

 Sourcetype_B
 Ticket | Main_Ticket | Value | Line | LinkedTicket

Now I need to find the results from sourcetype_B and if the sourcetype_B.Main_Ticket = Sourcetype_A.Ticket then I need to combine the results as:-

sourcetype_B.Ticket | sourcetype_B.Main_Ticket | sourcetype_B.Value | sourcetype_B.Line  | Sourcetype_A.Ticket | Sourcetype_A.Value 

Currently, I a using the join approach but I am not fetching all the results as my database have 500,000+ entries and join look for the first 50000 entries. The second approach is using "append" but still, it is very slow.

Is it possible to use Stats values(X) as X by Ticket if all the fields in both sourcetype are same but the field to compare (sourcetype_B.Main_Ticket = Sourcetype_A.Ticket) are different?

Thanks a lot in advance.

[woodcock]

Like this:

index=AlwaysSpecifyAnIndex AND (sourcetype=Sourcetype_A OR sourcetype=Sourcetype_B)
| fields sourcetype Ticket Main_Ticket Value Line LinkedTicket
| eval Joiner = if((sourcetype==Sourcetype_A), Ticket, Main_Ticket),
       ATicket = if((sourcetype==Sourcetype_A), Ticket, null()),
       AValue = if((sourcetype==Sourcetype_A), Value, null()),
       Ticket = if((sourcetype==Sourcetype_B), Ticket, null()),
       Main_Ticket = if((sourcetype==Sourcetype_B), Main_Ticket , null()),
       Value = if((sourcetype==Sourcetype_B), Value, null()),
       Line = if((sourcetype==Sourcetype_B), Line, null())
| stats values(*) AS * dc(sourcetype) AS numSourcetypes BY Joiner
| where numSourcetypes==2
| table Joiner Ticket Main_Ticket Value Line ATicket AValue

[Chandras11]

Sorry for the delay in response.
The only issue is that there are 64 columns in Sourcetpye_A and sourcetype_B and out of those 64 at least 14 are really needed. The code will be really big with it.

[HiroshiSatoh · Accepted solution]

Try this search sentence!

sourcetype=Sourcetype_A OR sourcetype=Sourcetype_B
|fields  Ticket,Main_Ticket,Value,Line,LinkedTicket,sourcetype
|eval match_key=if(sourcetype=Sourcetype_A,Ticket,Main_Ticket)
|stats latest(*) as * ,dc(sourcetype) as match_count by match_key
|where match_count=2

※|where match_count=2
Exclude unmatched logs

[Chandras11]

Thanks I am trying to use it in my query. 🙂