Given two sourcetypes, how to do you extract data from one sourcetype to another?

[zuokun]

Newbie here, would appreciate if anyone can help to answer this little question.

I have two sourcetypes, A and B.

A and B contains different types of data, but have a column called 'ID' that allows them to be linked.

If I want to do something like a lookup from A on B, or to extract a column from B and place it in A based on ID, how do I go about doing so?

For example, based on the type of a particular job (in sourcetype A), I want to retrieve the duration taken to complete the job (in source type B)

The idea is to based on the type of A, look for the ID of the job, then do something like a lookup on B based on the ID column, then retrieve the time taken, here's what I've tried.

sourcetype=A
| join type=left ID [search sourcetype=B elasped_time]
| eval result = IF(Type = "ongoing" OR Type = "finished", elapsed_time, "-")
| table result

I've tried join type =inner/outer/left, append, selfjoin, but none of them works for me. I tried lookup but I can't do a lookup for sourcetype, can i?

Can someone give a helping hand?

Thanks in advance.

[somesoni2]

Try like this

sourcetype=A OR sourcetype=B
| stats values(Type) as Type values(elapsed_time) as elapsed_time by ID
| eval result = IF(Type = "ongoing" OR Type = "finished", elapsed_time, "-")
| table result