Splunk Search

How to join data from three sources without using join?

kmcarrol
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
1 Solution

woodcock
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

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

0 Karma

kmcarrol
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

woodcock
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

kmcarrol
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

woodcock
Esteemed Legend

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

0 Karma

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