Splunk Search
Highlighted

How to join data from three sources without using join?

Path Finder

I'm slowly digesting the posts that describe how to use stats to retrieve aligned data from multiple sources but I'm having a little trouble with how to extend the concept to a three source scenario where not all three sources share one common field. For example...

User: Name, City
Activity: Id, Name, Action, Target
Product: Id, Title, Type

I want to select Name, City, Action, Title, Type where User.Name=Activity.Name and Activity.Target=Product.Id

Can this be done with stats?

Ultimately what I'm trying to do is to show information about the Top 10 Products for a specific City and Action.

Tags (3)
0 Karma
Highlighted

Re: How to join data from three sources without using join?

Path Finder

Sorry, forgot to mention that the three sources are setup as different indexes. For example, index=user. All three indexes contain more than 50,000 events.

0 Karma
Highlighted

Re: How to join data from three sources without using join?

Esteemed Legend

This should get you most of the way there:

index=User OR index=Activity OR index=Product | eventstats values(Title) values(Type) BY Id | eventstats values(City) by Name | search index=Activity

This augments the "Activity" events with the data from the other events.

View solution in original post

0 Karma
Highlighted

Re: How to join data from three sources without using join?

Path Finder

Had to do quite a bit of tweaking but that moved me in the right direction...
1) renaming Target as Id successfully overwrote the unnecessary field with the needed data
2) eventstats doesn't seem to want to write the field to an event unless that field is non-null to begin with
3) needed to do an mvexpand on the Title and then search where Title!=""

Ended up with something more like this...

index=User OR index=Activity OR index=Product
| rename Target as Id
| fillnull value="" Title, Type, City
| eventstats values(Title) as Title, values(Type) as Type by Id
| eventstats values(City) as City by Name
| mvexpand Title
| search index=Activity Title!=""

Now I'm just running into the infamous Bad Allocation error. I may have to generate a couple of summary indexes and search that data. Any other ideas?

0 Karma
Highlighted

Re: How to join data from three sources without using join?

Esteemed Legend

Buy more RAM (and "Accept" my answer)!

0 Karma
Highlighted

Re: How to join data from three sources without using join?

Esteemed Legend

The problem you had with eventstats that necessitated using fillnull sounds like a bug to me and I am sure it only happened because you were overwriting field values by recycling names (e.g. values(Title) AS Title) and furthermore that some events had the field while others did not. I have never had to use fillnull for this purpose before.

0 Karma
Highlighted

Re: How to join data from three sources without using join?

Path Finder

No, actually I was originally using unique names like TitleValues and if the event had no field called Title, it wouldn't add a value to TitleValues. That does sound like a bug to me as well.

0 Karma