How to get specified events from 2 indices.

[jsven7]

SITUATION:
- I use indices "A" and "B" to come to answer the same question but for different environments.
- Each index has unique fields names but said fields represent the same data in each index.
- I want a table to answer the same question in one table using both indices.
- I'm starting with SPL similar to this:

(index=A AND  indexA_unqiue_field=x) OR (index=B AND sourcetype=v indexB_unique_field=y)
| rename indexA_unqiue_field AS field
| rename indexB_unique_field AS field
| table _time, field, index

PROBLEM:
- With the above SPL I am failing to answer the same question using the 2 indices. It appears to work fine for index B but "field" does not populate for Index A.

QUESTION:
- How do I get all fields to populate?
- Or how may I achieve the goal?

[richgalloway · Accepted solution]

I think the two renames to the same field name are causing the problem. Try coalesce, instead. It will set 'field' either indexA_unique_field or indexB_unique_field, whichever is present in the event.

(index=A AND  indexA_unqiue_field=x) OR (index=B AND sourcetype=v indexB_unique_field=y)
| eval field = coalesce(indexA_unqiue_field, indexB_unique_field)
| table _time, field, index

[iainsmart]

The COALESCE command might be your friend here: https://docs.splunk.com/Documentation/Splunk/8.0.3/SearchReference/ConditionalFunctions#coalesce.28X...