topic Re: How to join data from three sources without using join? in Splunk Search

How to join data from three sources without using join?

kmcarrol — Fri, 29 May 2015 19:02:47 GMT

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.

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

kmcarrol — Fri, 29 May 2015 19:03:51 GMT

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.

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

woodcock — Fri, 29 May 2015 20:16:32 GMT

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.

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

kmcarrol — Fri, 29 May 2015 21:48:44 GMT

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

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?

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

woodcock — Fri, 29 May 2015 22:06:05 GMT

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

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

woodcock — Fri, 29 May 2015 22:09:47 GMT

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.

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

kmcarrol — Fri, 29 May 2015 23:32:52 GMT

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.