Splunk Search

Getting results from multiple searches without append or join

Chandras11
Communicator

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.

0 Karma
1 Solution

HiroshiSatoh
Champion

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

View solution in original post

woodcock
Esteemed Legend

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
Communicator

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.

0 Karma

HiroshiSatoh
Champion

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
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...