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?
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.
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.
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.
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
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
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?
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.
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.
Indeed, clarification is needed - this may well end in a stats by field1
or join field1
.