Splunk Search

How to use a subsearch to search across two indexes with no common field?

Upas02
Path Finder

I have one ID in a particular index and using that I want to find events in another index.

My search looks like this -

index=abc_test [ search index=xyz_test 12345 | stats latest(xyzID) as xyzID | fields xyzID ] | table _time, _raw

Basically, in my index abc_test, I have the value of xyzID, but with a different field name. So here I just want to see all events that contain the value of xyzID. But this search is giving me no results found. When I run these two commands individually I am getting results:

index=xyz_test 12345 | stats latest(xyzID) as xyzID | fields xyzID gives me xyzID=56789 

And when I search index=abc_test 56789 I am getting events. But in the subsearch format it is not working.
Can someone please suggest what is going wrong here?

0 Karma
1 Solution

datasearchninja
Communicator

Because you want to search on the field value, not the kv pair, you need to use the special column name "search" in the field name in the subsearch.

So:

index=abc_test [ search index=xyz_test 12345 | stats latest(xyzID) as xyzID | fields xyzID | rename xyzID as search ] | table _time, _raw

This will evaluate to:

index=abc_test ((56789))

rather than

index=abc_test ( ( xyzID="56789" ) )

See http://docs.splunk.com/Documentation/Splunk/7.1.3/Search/Changetheformatofsubsearchresults for more details.

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi Upas02,
if you have a different filename but the same values you have to rename it in the sub search:

index=abc_test [ search index=xyz_test 12345 | stats latest(xyzID) as abcID | fields abcID ] 
| table _time, _raw

In other words: you must have the same filename in main and subsearch.

If instead you want to search the xyzID values in the all the main search results and not in a defined field:

index=abc_test [ search index=xyz_test 12345 | stats latest(xyzID) as xyzID | fields xyzID | rename xyzID as query] 
| table _time, _raw

Bye.
Giuseppe

0 Karma

Upas02
Path Finder

Thanks Giuseppe, I understood you need to rename the search result to either search or query to get the result from outer query.

0 Karma

datasearchninja
Communicator

Because you want to search on the field value, not the kv pair, you need to use the special column name "search" in the field name in the subsearch.

So:

index=abc_test [ search index=xyz_test 12345 | stats latest(xyzID) as xyzID | fields xyzID | rename xyzID as search ] | table _time, _raw

This will evaluate to:

index=abc_test ((56789))

rather than

index=abc_test ( ( xyzID="56789" ) )

See http://docs.splunk.com/Documentation/Splunk/7.1.3/Search/Changetheformatofsubsearchresults for more details.

Upas02
Path Finder

Thank Colin, It worked perfect..and thanks fo rsharing the docs link as well.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...