How to get single row table output with fields from 2 log files using common field to join

[poddura]

Hi,

I am trying to join to log files under same index & sourcetype having a common field between them. 

log event where type=dte2_fios has below fields

TRANID, ANALYSIS, WPID  (this is common field), COMMAND

log event where type=dte4_fios has below fields

REQID, FT, WPID (this is common field), DIP, DTE,FTC,ERR_MSG

I need a table output with below fields and its corresponding value in each row

TRANID  WPID, REQID,ANALYSIS ,COMMAND,FT,DIP,DTE,FTC,ERR_MSG

I used below query, but it is giving me multiple values for REQID & FT in single row as one WPID will have multiple REQID & FTs. I need separate row with all the above fields

index=delb_np sourcetype="app_kafka_np_east" AND (function_name="dte2_fios" OR function_name="dte4_fios")
| table _time @timestamp function_name log_message.WPID log_message.CID log_message.TN log_message.TRANID log_message.REQID log_message.ANALYSIS log_message.COMMAND log_message.DIP log_message.FT
| stats values(log_message.TRANID) as TRANID, values(log_message.REQID) as REQID, values(log_message.ANALYSIS) as ANALYSIS, values(log_message.COMMAND) as COMMAND, values(log_message.DIP) as DIP, values(log_message.FT) as FT by log_message.WPID, log_message.CID

[ITWhisperer]

You probably need to combine REQID & FT into a single field with a suitable delimiter before you join with the stats command getting values for the combined field instead of REQID & FT, then use mvexpand on the combined field to get your separate rows, then split the combined field back into REQID & FT.