Splunk Search
Highlighted

Filter out events based on other events WITHOUT using JOIN/Subsearch

Explorer

I have a index named Events

Example events:

AccountCreated

{
   "AccountId": 1234,
   "EventName": "AccountCreated",
   "SomeOtherProperty": "Some value"
}

FavoriteCreated

{
   "AccountId": 1234,
   "EventName": "FavoritesCreated
}

Let's say that I have a bunch of these events, like millions.

Now, I want to create a query that returns the AccountCreated event IF 1 (or more) FavoriteCreated event exists with the same AccountId.

I've tried the following query and it works

index=events EventName=AccountCreated 
  [search index=events EventName=FavoriteCreated | dedup AccountId | fields AccountId]
| table AccountId, SomeOtherProperty

The only problem with that one is that it's using a subsearch and im hitting the 10000 results limit.

So then I tried using a JOIN instead (this also works)

index=events EventName=AccountCreated AccountId=*
   | stats count by AccountId, EventName
   | fields - count
| join AccountId 
    [ | search index=events EventName=FavoriteCreated AccountId=*
        | stats count by AccountId ]
| fields - count
| table AccountId, EventName

...but now im facing the 50K events limit (JOIN subsearch).

So, I need to be able to write the query without using JOIN and/or subsearch, do you guys have any tips?

Labels (3)
Tags (1)
0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

SplunkTrust
SplunkTrust

Hi joseftw,

try something like this:

index=events (EventName=AccountCreated OR EventName=FavoriteCreated) AccountId=*
| eval EventName=case(isnotnull(EventName), EventName, 1=1, "unknown")
| stats count by AccountId, EventName
| fields - count
| table AccountId, EventName

If this does not work please modify it 😉 But you will get a starting point and should you need more help please read this post https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

cheers, MuS

0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

Explorer

Hello!
Thank you for your answer! I tried it but it does not work for me. It returns BOTH AccountCreated and FavoriteCreated events, I only want the query to return AccountCreated events. This is my first day with Splunk so please bare with me... 🙂

If you have time, please look at my two examples I provided (they work but are using subsearch/join). I basically want to translate one of those queries to something that does not use join/subsearch.

Thank you for your help!

0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

Explorer

Basically "Return this AccountEvent IF 1 or more FavoriteCreated event exists with the same AccountId"

0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

Ultra Champion
index=events (EventName=AccountCreated OR EventName=FavoriteCreated) AccountId=*
| stats estdc(EventName) as flag values(*) as * by AccountId
| where flag > 1

please modify * to appropriate field.

0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

Explorer

Managed to solve it like this:

 index=events AccountId=* (EventName=AccountCreated OR EventName=FavoriteCreated)
    | stats 
        values(Brand) as Brand,
        values(DeviceType) as DeviceType,
        values(Email) as Email,
        values(EventName) as EventName
        values(EventTime) as EventTime,
        values(Locale) as Locale,
        values(ClientIp) as ClientIp
      by AccountId
    | where mvcount(EventName)>1
    | eval EventName=mvindex(EventName,0)
    | eval EventTime=mvindex(EventTime,0)
    | eval ClientIp=mvindex(ClientIp,0)
    | eval DeviceType=mvindex(DeviceType,0)

View solution in original post

0 Karma
Highlighted

Re: Filter out events based on other events WITHOUT using JOIN/Subsearch

Moderator
Moderator

Hi @joseftw,

Thanks for asking a question! If you were able to resolve this issue, please click “Accept” directly below the answer to resolve the post.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.