topic Re: How do I join/combine my two search searches to get my expected result in a single table? in Splunk Search

How do I join/combine my two search searches to get my expected result in a single table?

Bhanus1 — Wed, 24 Aug 2016 17:54:05 GMT

join/combine two searches into single table, duplicate records override with the first value.

Search1:

host=test* sourcetype=coner | rex "(?\w+) typecode=" | table id, type, code

Result:

ID    Type     Code
1111    MethodA 201
1111    MethodA 200
1111    MethodA 201
2222    MethodA 200

Search2:

host=test* sourcetype=coner | rex "(?\w+) status=" | table id, staus

Result:

ID    Status
1111    POST
1111    PRE
1111    POST
2222    PRE

join Search:

host=test* sourcetype=coner | rex "(?\w+) typecode=" | table id, type, code | join id [ search host=test* sourcetype=coner | rex "(?\w+) status=" | table id, staus] | table id, type, code, staus

Result:

ID    Type     Code   Status
1111    MethodA 201 POST
1111    MethodA 200 POST
1111    MethodA 201 POST
2222    MethodA 200 PRE

I want to combine Search1 & Search2, and expecting the table as:

Result:

ID    Type     Code   Status
1111    MethodA 201 POST
1111    MethodA 200 PRE
1111    MethodA 201 POST
2222    MethodA 200 PRE

Please suggest me the best solution to achieve this.

Re: How do I join/combine my two search searches to get my expected result in a single table?

sundareshr — Wed, 24 Aug 2016 18:35:38 GMT

How about this

host=test* sourcetype=coner | rex "(?<id>\w+) typecode=" | rex "(?<state>\w+) status=" | table id, typecode, state, status

*OR*

host=test* sourcetype=coner | rex "(?<id>\w+) typecode=" | rex "(?<state>\w+) status=" | eventstats values(status) as status by id code |  table id, typecode, state, status

*OR*

host=test* sourcetype=coner | rex "(?<id>\w+) typecode=" | rex "(?<state>\w+) status=" | stats values(state) as state by id, typecode, status

Re: How do I join/combine my two search searches to get my expected result in a single table?

Bhanus1 — Thu, 25 Aug 2016 06:07:43 GMT

If the data is in one log we can get this.
But i have two log events fetching different data and joining.
Ex:
Query1:
host=test* sourcetype=coner | rex "(?<\id>\w+) typecode=" | table id, type, code
2016-08-24 15:08:56.347 13278031 INFO com.utils.Utils - MethodA() 1111 typecode=201 type=MethodA

Query2:
host=test* sourcetype=coner | rex "(?<\id>\w+) status=" | table id, staus
2016-08-24 15:08:56.347 13278031 INFO com.utils.Utils - MethodA() 1111 status=POST

Re: How do I join/combine my two search searches to get my expected result in a single table?

sundareshr — Thu, 25 Aug 2016 12:31:47 GMT

In that case, try the updated answer

Re: How do I join/combine my two search searches to get my expected result in a single table?

Bhanus1 — Fri, 26 Aug 2016 09:27:18 GMT

Thanks Sundar for quick response.
But still no luck :(.
Two log events looks like:
2016-08-24 15:08:56.344 13278031 INFO com.utils.Utils - MethodA() 1111 status=POST
2016-08-24 15:08:56.347 13278031 INFO com.utils.Utils - MethodA() 1111 typecode=201 type=MethodA

Re: How do I join/combine my two search searches to get my expected result in a single table?

sundareshr — Fri, 26 Aug 2016 14:14:36 GMT

How about this

host=test* sourcetype=coner | rex "\s+(?<id>\d+)\s+[status|typecode]" | eventstats values(status) as status by id | search typecode=* | table id, typecode, state, status type

*OR*

host=test* sourcetype=coner | rex "\s+(?<id>\d+)\s+[status|typecode]" | stats values(status) as status by id, typecode type