topic Re: Join two table in Splunk in Splunk Search

Join two table in Splunk

cykuan — Tue, 14 Jul 2015 13:37:16 GMT

HI All,

Query1:

(FAILED) COM source="/home/test/test.log"  | rex field=_raw "^(?:[^,\n]*,){3}(?P<sender>\+\d+)" | dedup sender | table sender

Then the output will become
111
112
113
123

Query2:

login COM  | rex field=_raw "(?<number>\+\d+)"   | rex field=_raw "(?<Version>\w+\/\d+\.\d+\.\d*)"   | dedup number | search Version="1.0.4" | table number

Then the output will become
101
111
123
124

I would like to Join these two tables and generate the output as below.
111
123

I have tried this command by combining these two queries, but there is no result at all. Any suggestions?

(FAILED) COM source="/home/test/test.log"  | rex field=_raw "^(?:[^,\n]*,){3}(?P<sender>\+\d+)" | dedup sender | table sender | JOIN sender [search login COM  | rex field=_raw "(?<number>\+\d+)"   | rex field=_raw "(?<Version>\w+\/\d+\.\d+\.\d*)"   | dedup number | search Version="1.0.4" | table number]

Re: Join two table in Splunk

pradeepkumarg — Tue, 14 Jul 2015 14:03:07 GMT

| set intersect [...your search 1 | fields number] [...your search 2 | fields number]

http://docs.splunk.com/Documentation/Splunk/6.2.4/SearchReference/Set

Re: Join two table in Splunk

emiller42 — Tue, 14 Jul 2015 15:51:31 GMT

Don't think of your data as tables, because they aren't. Using JOIN or INTERSECT actually runs multiple searches over the same data. It's likely you can accomplish what you want in a single search, but what you've provided is pretty vague so I can't really provide a good example. Something like this:

COM (FAILED AND source="/home/test/test.log") OR login
| rex "^(?:[^,\n]*,){3}(?P<sender>\+\d+)"
| rex "(?<sender>\+\d+)"
| eval login=if(searchmatch("login"), 1, 0)
| eval failed=if(searchmatch("FAILED"), 1, 0)
| stats max(login) as login max(failed) as failed by sender
| search login=1 AND failed=1
| table sender

The problem with the specific thing you attempted is that you're telling it to join on the 'sender' field, but the subsearch only outputs one field: 'number'. So there is no 'sender' for it to join on.