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
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.
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.
Hi @DalJeanis,
This worked perfect and I was able to get results as expected. Thanks!
Okay well I don't know why the others didn't, but I'm glad you got what you needed.
@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
@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.
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
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!
Hi @mayurr98, Sadly, Getting lots of event but no results found.
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!!!
Hi @deepashri_123, Thanks for your response. On running the query, I get 249 event but "No results found".
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
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
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
Hi @Kamlesh, I can confirm your points and I tried the above query but it populated only client_ip field.
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.
Hi @HiroshiSatoh, thanks for your response. Sadly, I am not getting any results with this query.