Splunk Search

How to join two searches with different field name but same value ?

damode
Motivator

Table 1
userid, action, IP

Table2
sendername, action, client_IP

Query : select Table1.userid, Table1.action, Table1.ip,Table2.sendername FROM table1 INNERJOIN table2 ON table1.ip=table2.client_ip

What can be the equivalent query in Splunk if index is considered a table ?
below is the actual scenario,

indexA : userid, action, ip
indexB: sendername, action, client_ip

ip and client_ip have same values on which I want to build query
both indexes have same field name - action but different value. I am interested to see only indexA's action field
I want to see userid, ip, action from indexA and sendername from IndexB where indexA.ip=indexB.client_ip

EDIT: actual logs below,
index A:

    action:  deleted    
         anonymous:  0  
         component:  tool_recyclebin    
         contextid:  633444 
         contextinstanceid:  23uu2  
         contextlevel:   50 
         courseid:   099987 
         crud:   d  
         edulevel:   0  
         eventname:  \tool_recyclebin\event\item_deleted    
         ip:     127.0.0.1  
         objectid:   35521  
         objecttable:    tool_recyclebin    
         origin:     cli    
         other:  null   
         target:     xxxxxxxxx  
         timecreated:    1513328455 
         timestamp:  2017-12-15T20:00:55+1100   
         userid:     242425

indexB:

2017-12-13T12:59:31.900Z,,WEB,,WEB,xxxxxxxxx,127.0.0.1,<xxxxxxx.@xxxxx.com>,user@sender.com,,13981,1,,,StudiesEnquiry,no-reply@abc.com,0100016sxsxdwdd3-000000@ozone.com,2017-12-13T12:59:36.859Z;SRV=abc.edu:TOTAL=0;SRV=mail.com:TOTAL=0,Incoming,,,,S:MailName=WBE-BAN;S:DataHealth=2
Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

You've had a lot of help on this one, and several of the queries should have worked. I suspect there may be either a capitalization error, or the IPs do not actually match.

Try this:

index=A  ip=127.0.0.1 | head 1 | table index userid action ip 

and this

index=B  client_ip=127.0.0.1 | head 1 | table index sendername client_ip 

The above should both give a single result with the fields desired.

If the above each give a valid record, then try this:

(index=A  ip=127.0.0.1) OR (index=B  client_ip=127.0.0.1) 
| rename client_ip as ip
| table index userid action ip sendername 
| stats values(*) as * by ip

That should give a single combined record. If that checks out, then remove the ip= and client_ip= clauses.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

You've had a lot of help on this one, and several of the queries should have worked. I suspect there may be either a capitalization error, or the IPs do not actually match.

Try this:

index=A  ip=127.0.0.1 | head 1 | table index userid action ip 

and this

index=B  client_ip=127.0.0.1 | head 1 | table index sendername client_ip 

The above should both give a single result with the fields desired.

If the above each give a valid record, then try this:

(index=A  ip=127.0.0.1) OR (index=B  client_ip=127.0.0.1) 
| rename client_ip as ip
| table index userid action ip sendername 
| stats values(*) as * by ip

That should give a single combined record. If that checks out, then remove the ip= and client_ip= clauses.

damode
Motivator

Hi @DalJeanis,

This worked perfect and I was able to get results as expected. Thanks!

DalJeanis
SplunkTrust
SplunkTrust

Okay well I don't know why the others didn't, but I'm glad you got what you needed.

0 Karma

niketn
Legend

@damode, The event from indexA has userid=242425 however, I do not see 242425 value in the event from indexB. Can you please add sample data from two index that are to be correlated?

Also, do you know whether the field extractions for indexA and indexB been created by you/your team or are they built in based on some built in sourcetype/s?

The IP address in this example 127.0.0.1 seems to be local loopback address, which should be constant for all machines. Is this intentional or just a mock data?

Finally for the situation described by you, you should create Field Aliases for userid and ip fields for index A and index B respectively. This was the fields will be normalized for easier correlation. Let me knof if assistance with Field Aliases is required. Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addaliasestofields

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

damode
Motivator

@niketnilay, the userid is only present in IndexA. The only common factor between both indexes is the IP.
The field extractions in both indexes are built-in. Yes, the data above is not the real data but its just to give an idea how the logs look like. Thanks for the additional Info.

0 Karma

gesman_splunk
Splunk Employee
Splunk Employee
index=indexA OR index=indexB 
| eval ip=coalesce(ip, client_ip), action=if(index=indexA, action, null())
| stats first(userid) as userid, first(action) as action, first(sendername) as sendername by ip
0 Karma

mayurr98
Super Champion

hey @damode,

Try this will work 100 %

index=indexA OR index=indexB | eval clientip=coalesce(ip,client_ip)  | stats dc(index) as index_count values(sendername) as sendername  values(action) as action by clientip | where index_count=2

Let me know the output of the query!

damode
Motivator

Hi @mayurr98, Sadly, Getting lots of event but no results found.

0 Karma

deepashri_123
Motivator

Hi damode,

You can try the query below:

index=A OR index=B | stats values(userid) AS userid values(action) AS action values(ip) AS ip values(sendername) AS sendername values(client_ip) AS client_ip by index | where ip=client_ip | eval Action=if(index="A","A","B") | search Action="A" | fields - Action client_ip 

Let me know if this helps!!!

0 Karma

damode
Motivator

Hi @deepashri_123, Thanks for your response. On running the query, I get 249 event but "No results found".

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @damode,

Can you please try this?

index=indexA | fields userid, action, ip | rename ip as client_ip | append [
index=indexB | fields sendername, client_ip
] | stats values(userid) as userid values(action) as action values(sendername) as sendername by client_ip

Thanks

0 Karma

damode
Motivator

Hi @kamlesh_vaghela, thanks for your help. I am getting results but they are both separate from each indexes. For e.g, userid, client_ip, action from indexA in 2 rows and all other rows are of index B with fields client_ip and sender

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @damode,

As per latest info you provided can you please confirm points?

1) IndexA having each fields in each event.

userid:     242425
action:     deleted 
ip:     127.0.0.1    
timestamp:     2017-12-15T20:00:55+1100  

2) IndexB having all fields in the single event.

2017-12-13T12:59:31.900Z,,WEB,,WEB,xxxxxxxxx,127.0.0.1,<xxxxxxx.@xxxxx.com>,user@sender.com,,13981,1,,,StudiesEnquiry,no-reply@abc.com,0100016sxsxdwdd3-000000@ozone.com,2017-12-13T12:59:36.859Z;SRV=abc.edu:TOTAL=0;SRV=mail.com:TOTAL=0,Incoming,,,,S:MailName=WBE-BAN;S:DataHealth=2

If yes then can you please try this?

index=indexA | stats values(userid) as userid values(action) as action values(sendername) as sendername by _time
| rename ip as client_ip
| append [
index=indexB | fields sendername, client_ip
] | stats values(userid) as userid values(action) as action values(ip) as ip by client_ip

One thing, Is that any field that will make difference each userid's pieces of information.

Thanks

0 Karma

damode
Motivator

Hi @Kamlesh, I can confirm your points and I tried the above query but it populated only client_ip field.

0 Karma

HiroshiSatoh
Champion

Try this!

search A| fields userid, action, IP| join client_IP as IP [search b | fields sendername, client_IP]

OR
There is also a way to use STATS. The efficiency is better with STATS.

0 Karma

damode
Motivator

Hi @HiroshiSatoh, thanks for your response. Sadly, I am not getting any results with this query.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...