search on multiple indexes

[avitallange]

Hi,

I have two indexes: index1, index2.

index1 has a field 'Message' which index2 doesn't have.
There are duplicated messages that I'd like to dedup by |dedup Message.

I'd like to find the records with text "TextToFind" across the 2 indexes but not to get multiple records for the duplicated 'Message' field.

• index=index1 TextToFind returns 94 results (appear in field Message)
• index=index2 TextToFind returns 8 results (appear in field Content)
• (index=index1) OR (index=index2) TextToFind returns 102 results

(index=index1) OR (index=index2) TextToFind | dedup Message returns 2 results from index1 only although the TextToFind appears on index2 as well (in field Content).

Could you please fix my query?

[samhughe · Accepted solution]

Is the text you are looking for in index1's message field or is it in a different field?
if so you could use the eval and coalesce functions (http://docs.splunk.com/Documentation/Splunk/5.0.3/SearchReference/CommonEvalFunctions) to put them into a new, single field and then dedup. If the text you are looking for isn't in index1's Message field not sure of your best option other than some of the join/subsearch type functions

[avitallange]

It worked with the keepempty=true, thank you!

[samhughe]

Based on your additional comments, try:
(index=index1) OR (index=index2) TextToFind | eval newField=coalesce(Message,Content) | dedup newField

Assuming there are no duplicates of 'Content' you want to keep.

Or probably the better option is to use the keepempty=true parameter for dedup: http://docs.splunk.com/Documentation/Splunk/5.0.3/SearchReference/Dedup

[avitallange]

Yes, the test I am looking for is in index1's Message field.
It also appears on index2's Content field.
I updated my question above.
Your help is welcome!

[Ayn]

If your first search works but your second search returns 0 results, that's because there is no field called Message in your search.

[Ayn]

See hamhughe's answer which I think is spot on.

[avitallange]

I have a field called Message, I see in the left pane with many other fields. You are right, I got results but I still can't query what I want. I updated my question above.
Your help is welcome!