I didn't really know how to phrase this question so I just included the SideView modules I'm using.
I have a set of events that have a unique identifier field. Let's call it 'refid'. Then, sometimes, the events will have a field called 'related_events' which is a multi-value field containing 'refid' values that refer to other events in the same index.
So, my goal is to basically JOIN all 'related_events' to the same result set as the primary events. I tried using the JOIN command but it's pretty slow search because the JOIN search has to run over all time.
Since this is in a Dashboard I can break up the process into two seaches:
- Search 2 - Using ResultsValueSetter, ValueSetter & ArrayValueSetter I create a string from the list of refids from search 1 which looks like this:
refid="123" OR refid="456" OR refid="567" OR refid="8910" etc.
I really have two problems but only one is really necessary to solve right now.
Problem - How could I create a field in search two that tells me if the result is 'primary' or 'related' ?
EDIT: Source Data Example w/ User Search Example
Let's say a user does a search like this:
index=myindex sourcetype=st1 xxxkeywordherexxx
_raw - these two events are returned because they contain the keyword xxxkeywordherexxx
timestamp: 2013-02-10 12:54:01 refid: 1234 related_events: 456 789 323 uname: fooname summary: a bunch of text summarizing the event and users keyword search mainly this text and it has ips domains xxxkeywordherexxx timestamp: 2012-11-01 04:51:43 refid: 0201 related_events: 320 312 uname: barname summary: blah blah blah xxxkeywordherexxx blah blah blah
What I want is to achieve a JOIN on the multi-value field 'relatedevents' where each value of 'relatedevents' references another event's refid.
So the end goal is that the search would return the initial search results with a new field detailing whether the result was primary or related depending if the event was the result of the primary search or if it was fetch because it was referenced as 'related':
| eval search_type="primary" | eval search_type="related"
It would be nice to dive even deeper and retrieve one more level of 'related events' by looking at the 'related_events' field of the related events as well. For example, the events: 012 & 666
FYI...it seems I was using the JOIN command wrong. It seems to be working fine but I'm still interested in an answer to this question.
update: the join is still pretty slow so I like the method I described in my question.
Could you post an extract of your "source" Data, and en example of what you want as result ?
It was kind of dumb to ask two questions I guess. I've found the solution to my first problem:
Problem 1 - How could I create a field in search two that tells me if the result is 'primary' or 'related' ?
Solution: In search one, include the list of primary refids as a field. Then use the resultsetter to push the value of the field down to the second search. In the second search, use eval & match() to set a value for "search_type"
<mysearch>| eval primary="$primary$" | search_type=if(match(primary,refid,"primary","related")
Now I can use this field along with the TABLE MODULE to colorize the row so all related events are easily differentiated from the primary search results.
I think there's a good pure search language solution to this problem. I've mocked up your two sample events in a lookup so I could test it with inputlookup, and this search seems to produce the desired table:
* | eval related_events=split(related_events," ") | eval unified_id=mvappend(related_events,refid) | mvexpand unified_id | eval is_related=if(isnotnull(mvfind(related_events,unified_id)),1,0) | stats values(related_events) as related_events max(is_related) as is_related by uname unified_id | eval type=if(is_related="1","related","primary") | fields - is_related | eval related_events=if(type=="primary",related_events,null())
and with no join or any other square-bracket syntax or subsearches, and with nothing terribly smart at the UI level besides a table to render it.
Here is the output:
UPDATE: I added the table showing what the results look like above, and also if you want a ime column, you would just put something like
min(_time) as _time into the stats clause.
Also I added a final eval to trim off the vestigial related_events field when the row isn't a "primary" row.
Very nice. Thanks for this I really appreciated it.
No problem. While it's nice to see you pushing the envelope on advanced Sideview Utils foo, in this case it's a job best done by the search language.