Hi Everyone,
I am trying to check a certain a ticket-series in Sourcetype_A or Sourcetype_B. If found, I need to check if it is available in SourceType_C as well and extract the values "Linespecs, Linedescription, Other" from SourceType_C.
Sourcetype_A
Ticket | Main_Ticket | Value | Line | LinkedTicket
A2345A | A2345A | DES | L1 |
C2995B001 | C2995B | DTS | X2 |
C2995A | C2995A | DPU | L1 | Z4563A$Z4575A
C2995A001 | C2995A | DTS | X2 |
Sourcetype_B
Ticket | Main_Ticket | Value | Line | LinkedTicket
C2995B002 | C2995B | DTS | X2 |
C2995A003 | C2995A | DPU | L1 |
Z4563B | Z4563A | SUB | S1 | Z4563C
SourceType_C
Ticket | Linespecs | Linedescription | Other
C2995A001 | "Some Text" | "Some other Text" | other
C2995B002 | "Some Text" | "Some other Text" | other
A8743B002 | "Some Text" | "Some other Text" | other
Therefore, I tried to use the left join but it is not giving all the results.
index="index1" sourcetype="Sourcetype_A" OR sourcetype="Sourcetype_B" Ticket="C2995*" | dedup Ticket | join type=left [ search index="index1" sourcetype="SourceType_C"| table Linespecs Linedescription Other] |table Ticket, Main_Ticket, Value, Line, Linespecs, Linedescription, Other
The Output should be like:
Ticket | Main_Ticket | Value | Line |Linespecs | Linedescription | Other
C2995A | C2995A | DPU | L1 |
C2995A001 | C2995A | DTS | X2 | "Some Text" | "Some other Text" | other
C2995B002 | C2995B | DTS | X2 | "Some Text" | "Some other Text" | other
C2995A003 | C2995A | DPU | L1 |
Which means that if the same ticket is found, it can combine the values. But if the ticket is not found, it should leave it empty. Is there any other possible way to achieve the results. I was looking for map and append, but no success avail.
Thanks a lot for your time.
Give this a try (alternate implementation of join command which is an expensive command).
index="index1" sourcetype="Sourcetype_A" OR sourcetype="Sourcetype_B" Ticket="C2995*"
| stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line by Ticket
| append [ search index="index1" sourcetype="SourceType_C"| table Ticket Linespecs Linedescription Other]
|stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line values(Linespecs) as Linespecs values(Linedescription) as Linedescription values(Other) as Other by Ticket | where isnotnull(Main_Ticket)
Your join command was not working as your sourcetype_c search was missing field Ticket (which should've been added to the join command as well).
Give this a try (alternate implementation of join command which is an expensive command).
index="index1" sourcetype="Sourcetype_A" OR sourcetype="Sourcetype_B" Ticket="C2995*"
| stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line by Ticket
| append [ search index="index1" sourcetype="SourceType_C"| table Ticket Linespecs Linedescription Other]
|stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line values(Linespecs) as Linespecs values(Linedescription) as Linedescription values(Other) as Other by Ticket | where isnotnull(Main_Ticket)
Your join command was not working as your sourcetype_c search was missing field Ticket (which should've been added to the join command as well).
Just a small question: why stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line by Ticket
before as well as after append?
It's basically replacement of your dedup Ticket command in original query. With status, only the specified fields will be available in search processing thus reducing the memory usage of search. If the values of the fields Main_Ticket, Value, Line may be different in different events of Sourcetype_A/Sourcetype_B, OR you want to take the latest entry for the Ticket from those, you can replace the first two line (base search and stats line) with dedup implementation, like this:
index="index1" sourcetype="Sourcetype_A" OR sourcetype="Sourcetype_B" Ticket="C2995*"
| fields Ticket, Main_Ticket, Value, Line | dedup Ticket
| append...rest of the search
Hi Somesh, thanks a lot for this explanation. However, I am not getting the correct result. My actual query is much bigger, where I have already used append and map for subsearches. Now for the end result, I just want to look for the tickets into the Sourcetype_C and find if the Ticket exists there. If yes, I just need to take the values and include it in my search result. Otherwise, the field can be blank.
Is there any other way (for example Where statement, through which I can just check the ticket in the sourcetype_C and append the results to the original event). I extremely apologize for the inconvenience but your expertize can really save me here.
See if this join version works for your, in terms of accuracy.
index="index1" sourcetype="Sourcetype_A" OR sourcetype="Sourcetype_B" Ticket="C2995*"
| fields Ticket, Main_Ticket, Value, Line | dedup Ticket | join type=left Ticket [ search index="index1" sourcetype="SourceType_C"| table Ticket Linespecs Linedescription Other | dedup Ticket ] |table Ticket, Main_Ticket, Value, Line, Linespecs, Linedescription, Other
Hi, It is giving the results but redundant one. I tried to use, the other query (please note that the query posted here is dummy one due to data sensitivity)
fields Ticket, Main_Ticket, Value, Line | dedup Ticket |append [ search index="index1" sourcetype="SourceType_C"| table Ticket Linespecs Linedescription Other] stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line values(Linespecs) as Linespecs values(Linedescription) as Linedescription values(Other) as Other by Ticket | where isnotnull(Main_Ticket)
which is giving me somehow nearby results. Thanks a lot for your help. Let me dig further here.
It is working perfectly fine now. Thanks 🙂
index="index1" sourcetype="Sourcetype_A" OR
sourcetype="Sourcetype_B" Ticket="C2995*"
| fields Ticket, Main_Ticket, Value, Line
| append [ search index="index1" sourcetype="SourceType_C"| table Ticket Linespecs Linedescription Other]
|stats values(Main_Ticket) as Main_Ticket values(Value) as Value values(Line) as Line values(Linespecs) as Linespecs values(Linedescription) as Linedescription values(Other) as Other by Ticket | where isnotnull(Main_Ticket)