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

[pramit46]

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?

[sideview · Accepted solution]

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]

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.

[martin_mueller]

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

[sideview]

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]

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?

[martin_mueller]

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.

[pramit46]

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.

[martin_mueller]

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