Hello,
We are currently working with two sets of data that have similar fields. We would like to align matching events in one row (payment amount, category/source and account number) while also maintaining the values that do not match for failed processing. Below are some screenshots of what the data looks like now in four rows, as well as what we're hoping to visualize in 3 rows. Any assistance would be greatly appreciated!
Below is our current search:
index="index1" Tag="Tag1"
| stats values(PaymentAmount) as PaymentAmount by PaymentChannel,AccountId,PaymentCategory,ResponseStatus,StartDT
| rename AccountId as AccountNumber
| rename PaymentChannel as A_PaymentChannel
| rename PaymentCategory as A_PaymentCategory
| rename ResponseStatus as A_ResponseStatus
| rename StartDT as A_Time
| append
[search index="index2" sourcetype="source2"
| rename PaymentAmount as M_PayAmt
| eval PayAmt = tonumber(round(M_PayAmt,2))
| rex field=source "M_(?<M_Source>\w+)_data.csv"
| rename "TERMINAL ID" as M_KioskID
| rename "ResponseStatus" as "M_ResponseStatus"
| rename "KIOSK REPORT TIME" as M_Time
| eval _time =strptime(M_Time,"%Y-%m-%d %H:%M:%S.%3Q")
| addinfo
| where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")
| stats values(PayAmt) as M_PayAmt latest(M_Time) by AccountNumber, M_Source, M_ResponseStatus,M_KioskID
| rename latest(M_Time) as M_Time
| table M_PayAmt,AccountNumber, M_Source, M_KioskID,M_ResponseStatus,M_Time
| mvexpand M_PayAmt]
| eval A_PaymentTotal = "$" + PaymentAmount
| eval M_PayAmt = "$" + M_PayAmt
| eval joiner = AccountNumber
| table AccountNumber,A_PaymentChannel,M_KioskID,A_PaymentCategory,M_Source,A_PaymentTotal,M_PayAmt,A_ResponseStatus,M_ResponseStatus,A_Time,_Time
| eval M_PayAmt=if(isnull(M_PayAmt),"Unknown",M_PayAmt)
| eval A_PaymentTotal=if(isnull(A_PaymentTotal),"Unknown",A_PaymentTotal)
| eval A_Time=if(isnull(A_Time), M_Time, A_Time)
| eval M_Time=if(isnull(M_Time), A_Time, M_Time)
| sort by M_Time desc
... View more