Splunk Search

How to search and extract values from multiple sourcetypes?

Chandras11
Communicator

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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).

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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).

Chandras11
Communicator

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

Chandras11
Communicator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

Chandras11
Communicator

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.

0 Karma

Chandras11
Communicator

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)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...