All Apps and Add-ons

Help with Sideview Utils Dashboard, ValueSetter, ArrayValueSetter and running 2nd search over a list of values?

jpass
Contributor

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 1 - run a search using the textfield input and get a list of refids to retrieve with search 2
    index=myindex | eval refids=mvappend(refid,related_events) | mvexpand refids | stats values(refids) AS refids

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

  • use custom javascript to parse out the ORs and ANDs and then provide the search with the values to be used with the HIGHLIGHT command

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 'related_events' where each value of 'related_events' 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"
2013-02-10 12:54:011234456
789
323
foonameprimary
2012-01-11 13:01:01456666myusernamerelated
2012-12-01 04:01:22789foonamerelated
2012-12-12 15:22:12323ausernamerelated
2012-11-11 00:10:100201320
312
ausernameprimary
2012-11-05 02:11:05320012ausernamerelated
2012-11-02 09:01:02312myunamerelated

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

1 Solution

sideview
SplunkTrust
SplunkTrust

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:

uname unified_id related_events type
barname 0201 312
320
primary
barname 312 related
barname 320 related
fooname 323 related
fooname 456 related
fooname 789 related
fooname 1234 323
456
789
primary

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.

View solution in original post

sideview
SplunkTrust
SplunkTrust

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:

uname unified_id related_events type
barname 0201 312
320
primary
barname 312 related
barname 320 related
fooname 323 related
fooname 456 related
fooname 789 related
fooname 1234 323
456
789
primary

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.

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

jpass
Contributor

Very nice. Thanks for this I really appreciated it.

0 Karma

jpass
Contributor

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.

0 Karma

sbsbb
Builder

Could you post an extract of your "source" Data, and en example of what you want as result ?

0 Karma

jpass
Contributor

update: the join is still pretty slow so I like the method I described in my question.

0 Karma

jpass
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...