topic How to search millions of events between two sourcetypes based on common/matching criteria? in Splunk Search

How to search millions of events between two sourcetypes based on common/matching criteria?

pradjswl — Mon, 08 Aug 2016 20:30:28 GMT

I have tried the following search, but it doesn't work correctly.

Option 1) Using following join command, it works great for 1Hr of window. But when increasing the window, it starts giving me incomplete/partial results as it's scanning millions of events for 24hr of duration.

index=sl_logs sourcetype=sl_myworld | Join a_xf_BestSessionID [search index=teb x_EventID=10577 DISPLAYCODE=SVCMS03 | rename x_BESTSessionID as a_xf_BestSessionID | table a_xf_BestSessionID ]

Option 2) I have created following different search referring to the post https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html
These searches returns all the x_BESTSessionID which are result of

index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03

However, it lists all events from index=sl_logs sourcetype=sl_myworld, and there is not filtering done based on the command where tealeaf_eventbus=sl_myworld

1st search:

(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03) OR (index=sl_logs sourcetype=sl_myworld)
| eval Test-{sourcetype}=a_xf_BestSessionID
| stats values(Test-*) AS *
| mvexpand tealeaf_eventbus
| mvexpand sl_myworld
| where tealeaf_eventbus=sl_myworld
| rename sl_myworld AS a_xf_BestSessionID
| fields -tealeaf_eventbus

2nd search:

(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03) OR (index=sl_logs sourcetype=sl_myworld)
| eval Test-{sourcetype}=x_BESTSessionID
| stats values(Test-*) AS *
| mvexpand tealeaf_eventbus
| mvexpand sl_myworld
| where tealeaf_eventbus=sl_myworld
| rename sl_myworld AS x_BESTSessionID
| fields -tealeaf_eventbus

3rd search:

(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03 | rename x_BESTSessionID AS a_xf_BestSessionID) OR (index=sl_logs sourcetype=sl_myworld)
| eval Test-{sourcetype}=a_xf_BestSessionID
| stats values(Test-*) AS *
| mvexpand tealeaf_eventbus
| mvexpand sl_myworld
| where tealeaf_eventbus=sl_myworld
| fields -tealeaf_eventbus

Gives me error:

Error in 'search' command: Unable to parse the search: unbalanced parentheses.

Really appreciate your feedback.

Re: How to search millions of events between two sourcetypes based on common/matching criteria?

somesoni2 — Mon, 08 Aug 2016 21:39:59 GMT

How about this

(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03) OR (index=sl_logs sourcetype=sl_myworld)
| stats dc(sourcetype) as sts by a_xf_BestSessionID | where sts=2 | fields - sts

Re: How to search millions of events between two sourcetypes based on common/matching criteria?

MuS — Mon, 08 Aug 2016 21:41:56 GMT

Hi pradjswl,

Regarding 3rd search; there is and error in
(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03 | rename x_BESTSessionID AS a_xf_BestSessionID) OR (index=sl_logs sourcetype=sl_myworld)
could this be more like
(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03) OR (index=sl_logs sourcetype=sl_myworld) | rename x_BESTSessionID AS a_xf_BestSessionID ?

Regarding 1st and 2nd searches; try to remove everything after the first stats - if the events do make sense add ONE additional step in the search pipe and re-check the events... continue until it breaks and you have the step in the search pipe which needs to be fixed.

cheers, MuS

Re: How to search millions of events between two sourcetypes based on common/matching criteria?

pradjswl — Mon, 08 Aug 2016 21:45:08 GMT

Great thanks @samesoni2 for quick response, I tried the query you shared, unfortunately ts returning all the events from sl_myworld.

Re: How to search millions of events between two sourcetypes based on common/matching criteria?

pradjswl — Tue, 29 Sep 2020 10:32:28 GMT

Hey Mus - Great thanks for correction. I tried following query
(index=teb sourcetype=tealeaf_eventbus x_EventID=10577 DISPLAYCODE=SVCMS03) OR (index=sl_logs sourcetype=sl_myworld) | rename x_BESTSessionID AS a_xf_BestSessionID | eval Test-{sourcetype}=a_xf_BestSessionID
| stats values(Test-*) AS *
| mvexpand tealeaf_eventbus
| mvexpand sl_myworld
| where tealeaf_eventbus=sl_myworld

In stats tab it give me list of xf_BestSessionID whichc matches with x_BESTSessionID, However Event tab is listing all the event. How do i make sure that Event tab only display those event which are same as the one listed in Stats tab.
Ps : Query is not yet in finalized status, almost 15% completed. However displaying all the events in Event tab wont server a purpose for me, as I wanted to slice/dice & make analysis by viewing the values of fields in verbose mode.

I feel that Event tab is displaying all events from (index=sl_logs sourcetype=sl_myworld) & its not comparing /where tealeaf_eventbus=sl_myworld/ which was technically suppose to work as /a_xf_BestSessionID=x_BESTSessionID/

Is there a way we can setup a meetup or screen share to walk through it, if thats not too much to ask for. Its not very urgent now 🙂

Re: How to search millions of events between two sourcetypes based on common/matching criteria?

pradjswl — Tue, 29 Sep 2020 10:32:31 GMT

I accidentally deleted my previous comment.

Great thank you @MuS for the correction in 3rd query.

I feel that Event tab is displaying all events from (index=sl_logs sourcetype=sl_myworld) returning thousands of event & its not comparing /where tealeaf_eventbus=sl_myworld/ which was technically suppose to work as /a_xf_BestSessionID=x_BESTSessionID/ returning around 50-100s of event.

In other word I am looking for join between two sourcetype and display only those event which having matching field value.(sourcetype=sl_myworld-> Field=a_xf_BestSessionID & sourcetype=tealeaf_eventbus->Field=x_BESTSessionID)

If thats not too much to ask, Is there away I can setup a meeting to walk you through my splunk screen ?