topic Re: Using Join (or similar command) for One to Many Relationship in Splunk Search

Using Join (or similar command) for One to Many Relationship

ezajac — Mon, 28 Sep 2020 14:16:10 GMT

I have a log file that is writing session data for users using an application in a csv format. The session data provides information about each transaction using "Meta" events and action information (Page Loads, Application Initialization). The sessionId is common to both types of events and is used to link the events. There is a one to many relationship between the action events and the meta events. (Each Action Event will have many Meta Events associated with it.)

I want to write a search that will add the information provided from the Meta information to the action information. I started using a join, but after running the search it looks like the search is only pulling back the meta information from the first meta tag. I have been able to push this data to Hadoop and run Ruby to "Sessionize" the data, but I want to be able to do this directly in Splunk.

Below is the join search. Is "Join" the right search to create a one to many relationship? Can I create a Subtable of Meta Data to each action event?

index=prod_ui sourcetype=ui_instrumentation Type=init OR Type=view | rename Type AS Type1 | rename SubjectName AS SubjectName1 | rename DataValue AS DataValue1 | table _time, SessionId, Type1, SubjectName1, DataValue1, Duration | join SessionId [search index=prod_ui sourcetype=ui_instrumentation Type=meta | table SessionId, DataValue, SubjectName, Type]

Re: Using Join (or similar command) for One to Many Relationship

cpeteman — Mon, 28 Sep 2020 14:16:22 GMT

I had a vaguely similar problem a few weeks ago. The best solution seems to be using append and selfjoin instead of join. Try the following

index=prod_ui sourcetype=ui_instrumentation Type=init OR Type=view | rename Type AS Type1 | rename SubjectName AS SubjectName1 | rename DataValue AS DataValue1 | table _time, SessionId, Type1, SubjectName1, DataValue1, Duration | append [search index=prod_ui sourcetype=ui_instrumentation Type=meta | table SessionId, DataValue, SubjectName, Type] | selfjoin SessionId

That doesn't work right let me know what it does versus what you want I'll be glad to take another look.

Re: Using Join (or similar command) for One to Many Relationship

cpeteman — Fri, 05 Jul 2013 20:27:23 GMT

Assuming the search and subsearch were correct of course. 🙂

Re: Using Join (or similar command) for One to Many Relationship

darshildave — Mon, 28 May 2018 11:29:55 GMT

The join command contains an option called max=int that is used to specify how many subsearch results can join with main search results.

In your query, just write join max=0 SessionId in place of join SessionId.
When max is set to 0 there is no limit.