How to combine two searches with common value into one table

[alexandrebas]

I need help regarding a join from events based on two different indexes that are related by the same value in one specific field.

Below a simple example:

index=source1 | table device.hostname,device.serialnumber

Results:

device.hostname	device.serialnumber
host1	ABC
host2	DEF

index=source2 | table hostname,user

Results:

hostname	user
host1	john
host2	mary

I would like to join these two searches in order to get the following results:

device.hostname	device.serialnumber	user
host1	ABC	john
host2	DEF	mary

Thank in advance for your help.

[alexandrebas]

It worked @ITWhisperer . 

Thank you very much.

[ITWhisperer]

index=source1 OR index=source2
| eval hostname=coalesce(hostname,device.hostname)
| stats values(device.serialnumber) as serialnumber values(user) as user by hostname

[alexandrebas]

Hi @ITWhisperer 

Thank you for your help. However, the field serialnumber wasn´t populated.  It seems the two searches weren´t merged.

Regards,

Alexandre

[ITWhisperer · Accepted solution]

Try with the name in single quotes

| eval hostname=coalesce(hostname,'device.hostname')