Query to get the result from one sourcetype and get other field values based on the output from other sourcetype

[sahil237888]

Need help in creating a query to get the result from one sourcetype and get other field values based on the output from the first sourcetype.

For. e.g

I am having the below output which shows the transaction_id as a result but the username corresponding to that transaction ID should be fetched from another sourcetype.

Result of query should be - 

_time hostname transaction_id username city
1:30AM server1 TEST cron_user US

1:31AM server2 TEST1 cron2_user CA

In above transaction_id is the field present in both sourcetype

hostname and transaction_id is coming from one sourcetype.

matching with specfific transaction_id , username and city should be fetched from sourcetype2

[yuanliu]

Something like

sourcetype IN (sourcetype1, sourcetype2)
| stats max(_time) as _time values(hostname) as hostname values(username) as username values(city) as city by transaction_id

[ITWhisperer]

Your transaction ids don't match, but assuming they did, you could try this

| eval hostname=if(sourcetype="sourcetype1",hostname,null())
| eval user=if(sourcetype="sourcetype2",user,null())
| eval city=if(sourcetype="sourcetype2",city,null())
| stats values(hostname) as hostname values(user) as user values(city) as city by transaction_id