Splunk Search

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

ezajac
Path Finder

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]

Tags (1)
0 Karma

darshildave
Explorer

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.

,

cpeteman
Contributor

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.

cpeteman
Contributor

Assuming the search and subsearch were correct of course. 🙂

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