Splunk Search

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

Bhanus1
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

sundareshr
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

Bhanus1
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

sundareshr
Legend

In that case, try the updated answer

0 Karma

Bhanus1
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

sundareshr
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
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...