Splunk Search
Highlighted

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

New Member

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.

0 Karma
Highlighted

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

Legend

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
0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Legend

In that case, try the updated answer

0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Legend

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
0 Karma