Splunk Search

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

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)
1 Solution
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.

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.

Motivator

Hi @DalJeanis,

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

SplunkTrust

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

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

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

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!

Motivator

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

Motivator

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

SplunkTrust

Hi @damode,

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

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

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

Motivator

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

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.

Motivator

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

Get Updates on the Splunk Community!