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!

Monitoring MariaDB and MySQL

In a previous post, we explored monitoring PostgreSQL and general best practices around which metrics to ...

Financial Services Industry Use Cases, ITSI Best Practices, and More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...