topic Re: How to get an output for two different searches using join? in Splunk Search

How to get an output for two different searches using join?

sujith0311 — Tue, 29 Sep 2020 12:37:33 GMT

Hi ,

Search 1:

index="sftp" USER=gradydftsftpdata | table USER, SESSION_ID,USER_IP,date_hour | dedup SESSION_ID,USER_IP

with this search I'm able to get USER,SESSION_ID,USER_IP,date_hour

Search 2:

index="sftp" SESSION_ID=9666 date_hour=3 ACTION != session | table FILE_NAME, _time, USER_IP, ACTION.

with this search i'm able to get the session_id of the a particular session with file_name,_time , user_ip, ACTION.

What i'm trying to get is File_NAME,USER,SESSION_ID,date_hour,USER_IP,ACTION and the search that i'm using is

index=sftp USER=gradydftsftpdata SESSION_ID=*  | join sftp[search index=sftp  SESSION_ID=* date_hour=* ACTION != session | table FILE_NAME, _time, USER_IP, ACTION] |  table FILE_NAME,USER, SESSION_ID,USER_IP,date_hour,_time,ACTION | dedup SESSION_ID,USER_IP

Is this the correct search that i'm using to get output??

Re: How to get an output for two different searches using join?

somesoni2 — Mon, 30 Jan 2017 15:18:17 GMT

For a join to work, both result set should return a common column and you need to specify the column in join command (see Join command's document for more details). So your query if using join should be like this.

index=sftp USER=gradydftsftpdata SESSION_ID=* | table USER, SESSION_ID,USER_IP,date_hour | dedup SESSION_ID,USER_IP| join type=left SESSION_ID [search index=sftp SESSION_ID=* date_hour=* ACTION != session | table SESSION_ID FILE_NAME, _time, USER_IP, ACTION] | table FILE_NAME,USER, SESSION_ID,USER_IP,date_hour,_time,ACTION

Re: How to get an output for two different searches using join?

jplumsdaine22 — Mon, 30 Jan 2017 15:21:36 GMT

It will depend upon what your events look like. For example, do all your events contain a common field? Or do you have different event types within the index. It would help if you could post an example

Here is a handy flowchart on when to use join that may be of use:

http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation

Re: How to get an output for two different searches using join?

DalJeanis — Tue, 29 Sep 2020 12:40:00 GMT

It seems like you are overcomplicating things. You are searching in the first search and specifying your user, and in the second you are specifying session ID and hour.

If what you are trying to do makes any sense, then you must be trying to find two different related records in the same hour, and query 1 is getting the session record while query 2 gets the non-session record. If that is true, though, then you need to be specifying ACTION = session in the first query.

The problem is that you need all common fields for a unique join.

Can you please show an example of the results of each query?

index="sftp" USER=gradydftsftpdata 
| table USER, SESSION_ID,USER_IP,date_hour 
| dedup SESSION_ID,USER_IP

Then, to properly join with that, you'll need to keep at least the SESSION_ID and the date_hour for the second query.

index="sftp" SESSION_ID=9666 date_hour=3 ACTION != session 
| table FILE_NAME, _time, SESSION_ID,  date_hour, USER_IP, ACTION

Not knowing more about your data, I can't tell you which of the above queries should be the "left" part of the join.

Can you give more information about what you think each of your queries is doing?

Re: How to get an output for two different searches using join?

sujith0311 — Tue, 29 Sep 2020 12:37:39 GMT

USER SESSION_ID USER_IP date_hour
gradydftsftpdata 20716 10
gradydftsftpdata 15931 9
gradydftsftpdata 11034 8
gradydftsftpdata 6597 7
gradydftsftpdata 2127 6
gradydftsftpdata 28354 5
gradydftsftpdata 23974 4
gradydftsftpdata 19498 3
gradydftsftpdata 14957 2

This is the result for the first query

FILE_NAME _time USER_IP ACTION
/datafeed/EL/xyz_EL_201705136.txt 2017-01-27 03:15:04 close
/datafeed/EL/xyz_EL_201705136.txt 2017-01-27 03:15:04 open

This is the output for the second query.

when i use this query

index=sftp USER=gradydftsftpdata SESSION_ID=* | join sftp[search index=sftp SESSION_ID=* date_hour=* ACTION != session | table FILE_NAME, _time, USER_IP, ACTION] | table FILE_NAME,USER, SESSION_ID,USER_IP,date_hour,_time,ACTION | dedup SESSION_ID,USER_IP

I get the output as

FILE_NAME USER SESSION_ID USER_IP date_hour _time ACTION
/datafeed/ gradydftsftpdata 20716 10 2017-01-30 10:15:05 forced
/datafeed/ gradydftsftpdata 15931 9 2017-01-30 09:15:03 forced

What result i'm looking for is , when we enter the above query I need to get the ACTION as closed and open for particular FILE_NAME as we got in query 2

Re: How to get an output for two different searches using join?

sujith0311 — Tue, 29 Sep 2020 12:37:41 GMT

Hi Somesoni2,

I got that query executed . But [search index=sftp SESSION_ID=* date_hour=* ACTION != session. I edited ACTION !=session to ACTION = open or close i mean one at a time and i'm able to get the exact output what i'm expecting for. But where can i initialize if i want to see open and close together in that query.

Re: How to get an output for two different searches using join?

somesoni2 — Mon, 30 Jan 2017 17:16:56 GMT

Try like this ACTION="open" OR ACTION="close", instead of ACTION != session.

Re: How to get an output for two different searches using join?

sujith0311 — Mon, 30 Jan 2017 17:25:10 GMT

when i replace it with the above one it displays all the closed content. I think the files which having the filename need to be verified.

Re: How to get an output for two different searches using join?

somesoni2 — Mon, 30 Jan 2017 17:41:23 GMT

The thing is the join is doing one-on-one match with your session logs. So, even if you're using ACTION="open" OR ACTION="close", the latest records, which I assume is the one with ACTION=close, is getting returned. If you want data from both ACTION to be returned, try like this

index=sftp USER=gradydftsftpdata SESSION_ID=* | table USER, SESSION_ID,USER_IP,date_hour | dedup SESSION_ID,USER_IP| join type=left max=2 SESSION_ID [search index=sftp SESSION_ID=* date_hour=* ACTION="open" OR ACTION="close" | table SESSION_ID FILE_NAME, _time, USER_IP, ACTION] | table FILE_NAME,USER, SESSION_ID,USER_IP,date_hour,_time,ACTION

Re: How to get an output for two different searches using join?

sujith0311 — Mon, 30 Jan 2017 17:55:53 GMT

It works somesoni. Can I know what's the best practice for splunk quries. Can you suggest me any kind of documentation or something like that

Re: How to get an output for two different searches using join?

somesoni2 — Mon, 30 Jan 2017 18:08:59 GMT

I would read these

http://docs.splunk.com/Documentation/Splunk/latest/Search/Writebettersearches
https://wiki.splunk.com/Things_I_wish_I_knew_then (Searches UI section)
https://wiki.splunk.com/Community:More_best_practices_and_processes (searching and reporting section)