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