Splunk Search

Join is not correlating and appending field data as expected

JHFRDANALYSIS
New Member

This is my query that isn't working as expected.  

index=julie sourcetype!="julie:uat:user_activity" host!="julie-uat.home.location.net:8152" application_id=julie1 policy_id=framework action=session_end "error_code"=9999 "*"

| table julie_date_time, event_name, proxy_id, error_code, session_id, device_session_id, result |rename session_id as JulieSessionId

|join type=left device_session_id [search index=julie sourcetype!="julie:uat:user_activity" host!="julie-uat.home.location.net:8152" application_id=julie1 policy_id="FETCH-DAVESESSION-ID"  action=create_ticket |table timeDave, device_session_id,  session_id |rename session_id as DAVESessionID]

Assume Primary Query returns data like following:

julie_date_time event_name proxy _id error_code Juliesession_id device_session_id result
2024-09-20T23:53:53 Login 199877 9999 1a890963 f5318902 pass
2024-09-19T08:20:00 View Profile 734023 9999 92xy9125 81b3e713 pass
2024-09-17T11:23:45 Change Profile 089234 9999 852rs814 142z7x81 pass

 

Requirement:  I want to add the DAVEsession_ID to the above table when the following query returns something like:

timeDave event_name DAVEsession_id device_session_id
2024-09-20T23:53:50 Login 1a890963 f5318902
2024-09-19T08:19:58 View Profile 92xy9125 81b3e713
2024-09-17T11:23:40 Change Profile 852rs814 142z7x81

 

Expected Outcome:

julie_date_time event_name proxy _id error_code Juliesession_id device_session_id result timeDave DAVEsession_id
2024-09-20T23:53:50 Login 199877 9999 1a890963 f5318902 pass 2024-09-20T23:53:50 1a890963
2024-09-19T08:19:58 View Profile 734023 9999 92xy9125 81b3e713 pass 2024-09-19T08:19:58 92xy9125
2024-09-20T23:53:53 Change Profile 089234 9999 852rs814 142z7x81 pass 2024-09-17T11:23:40 852rs814



Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=julie sourcetype!="julie:uat:user_activity" host!="julie-uat.home.location.net:8152" application_id=julie1
| eval DAVESessionID=if(policy_id="FETCH-DAVESESSION-ID"  AND action="create_ticket",session_id,null())
| eventstats values(DAVESessionID) as DAVESessionID by device_session_id
| where policy_id="framework" AND action="session_end" AND error_code=9999 
0 Karma

antoniolamonica
Explorer

Check here under "Join datasets on fields that have different names".

You may want to test by assigning aliases to see what populates from which side of the join. 
Furthermore, perform an additional table statement after your join to pull in all of the data and troubleshoot from there.
---
If this reply helps you, Karma would be appreciated.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

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