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