Splunk Search
Highlighted

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

New Member

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,SESSIONID,USERIP,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 sessionid of the a particular session with filename,time , userip, ACTION.

What i'm trying to get is FileNAME,USER,SESSIONID,datehour,USERIP,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??

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma
Highlighted

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

New Member

Hi Somesoni2,

I got that query executed . But [search index=sftp SESSIONID=* datehour=* 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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

New Member

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust
0 Karma
Highlighted

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

Influencer

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

Highlighted

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

SplunkTrust
SplunkTrust

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 SESSIONID and the datehour 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?

0 Karma