Splunk Search

How to pass a parameter from one search query to another as a search argument?

pramit46
Contributor

Hello,

I have a situation where I want to do the following:
search field_1 from (index_1 and sourcetype_1) and then search field_2 from (index_2 and sourcetype_2) using the field_1

basically I have two different source files which have separate indexes as well as sourcetypes. Only common identifier is field_1. now I want to get the value of field_2.

Can somebody help me achieving this using a single query?

Tags (1)
0 Karma
1 Solution

sideview
SplunkTrust
SplunkTrust

I think there's some ambiguity in the question but I also think this answer cuts both ways.

1) If you want the index_2 search to ultimately run a search that looks like (field1=field1_value1 OR field1=field1_value2 OR field1=field1_value3 OR...), then

index=index_2 sourcetype=sourcetype_2 [search index=index_1 sourcetype=sourcetype_1 | fields field_1]

2) if you want to use the values of field_1 in the first search, to search for those values in the field_2 fields.... then:

index=index_2 sourcetype=sourcetype_2 [search index=index_1 sourcetype=sourcetype_1 | fields field_1 | rename field_1 as field_2]

In the latter, the outside search is ultimately using a searchterm that looks like ( field1=field2_value1 OR field1=field2_value2 OR field1=field2_value3 OR ...)

UPDATE:
Hmm... based on this "Only common identifier is field_1. now I want to get the value of field_2.", I think both the above may be off. It's amazing how precise you have to be to accurately describe a search language problem. 😃

If you can update your question with more details to remove ambiguity I'll update my answer.

View solution in original post

sideview
SplunkTrust
SplunkTrust

I think there's some ambiguity in the question but I also think this answer cuts both ways.

1) If you want the index_2 search to ultimately run a search that looks like (field1=field1_value1 OR field1=field1_value2 OR field1=field1_value3 OR...), then

index=index_2 sourcetype=sourcetype_2 [search index=index_1 sourcetype=sourcetype_1 | fields field_1]

2) if you want to use the values of field_1 in the first search, to search for those values in the field_2 fields.... then:

index=index_2 sourcetype=sourcetype_2 [search index=index_1 sourcetype=sourcetype_1 | fields field_1 | rename field_1 as field_2]

In the latter, the outside search is ultimately using a searchterm that looks like ( field1=field2_value1 OR field1=field2_value2 OR field1=field2_value3 OR ...)

UPDATE:
Hmm... based on this "Only common identifier is field_1. now I want to get the value of field_2.", I think both the above may be off. It's amazing how precise you have to be to accurately describe a search language problem. 😃

If you can update your question with more details to remove ambiguity I'll update my answer.

pramit46
Contributor

martin_mueller, I tried the query you had provided earlier <(index=index_1 sourcetype=sourcetype_1) OR (index=index_2 sourcetype=sourcetype_2) | stats values(bank_account_nbr) by cust_id>; somehow, I feel that it would only consider the latter part (as both cust_id and bank_account_nbr are present in the second part of the query) and ignore the first part since we are using OR. I tried to use AND instead and found no result. Then I removed the first part (which is before 'OR') and got the same result.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Additionally, you could set up field aliases for the various ways to name a customer ID field to one common name and use that without having a large eval in every query. http://docs.splunk.com/Documentation/Splunk/6.0.3/Knowledge/Addaliasestofields

0 Karma

sideview
SplunkTrust
SplunkTrust

You can do a lot of kinds of surgical renames/conversions/normalizations with the eval command and it's long list of useful functions.

Here's a way to rename Customer_ID to cust_id if cust_id is absent but leave it alone if it is already present:

| eval cust_id=if(isnull(cust_id),Customer_ID,cust_id)

and here's a way to create a third field called c_id
| eval c_id=coalesce(cust_id,Customer_ID)

(and in that case you would of course do your stats values(bank_account_nbr) by c_id)

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions

pramit46
Contributor

Awesome!!!... that works. I was also curious about clubbing multiple indexes and sourcetypes (I mean, more than just 2), and your suggestions answer that, too.Thanks a lot to both of you.
Just one question, what if the fieldname is different in both the logs but the value remains same. eg: if in the first log cust_id:001 whereas in the second log, Customer_ID=001. Can I relate them?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Give this a shot:

(index=index_1 sourcetype=sourcetype_1) OR (index=index_2 sourcetype=sourcetype_2) | stats values(bank_account_nbr) by cust_id

That'll create a table of customers with their bank account number.

0 Karma

pramit46
Contributor

Well, I guess, there is an assumption that field_1 (or field_2) is a multivalued field. If it is, then I apologize, it is not. I should have been more clear.

Let's assume that both the logs are stored in the form of XML. One stores customer detail (cust_id is the unique value), and other stores his bank information (which also has the same cust_id field).

Now, I want to retrieve the cust_id from the first log and pass it to the second one to get his bank_account_nbr(I'm assuming that there is only one bank account for each customer). Both the logs have separate indexes and sourcetypes.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Indeed, clarification is needed - this may well end in a stats by field1 or join field1.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...