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.
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
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
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.
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
Thanks I am trying to use it in my query. 🙂