HI All,
I need to reconcile 2 different swift messages from Splunk DB connect
The key pattern should be
<<YYYYMMDD>>#SWIFTRACKER#*#*#*#INFO
The three * are UETR Number, ToSwift/FromIIB and status. Each combination of
UETR Number and FromIIB record must have a corresponding record with UETR
Number and ToSwift combination.
For e.g.,
20200715#SWIFTRACKER#FromIIB#abcdfghif#Accepted#INFO
20200715#SWIFTRACKER#ToSwift#abcdfghif#Accepted#INFO
I have extracted the fields from the table, its a single filed in DB which holds this data
| dbxquery connection=CONN query="select RECID from Schema.table" shortnames=true
| rex field=RECID "(?<date>\d+)#(?<swift>\w+)#(?<source>\w+)#(?<uetr>\w+\-+\w+\-+\w+\-+\w+\-+\w+)#(?<status>\w+)#(?<loglevel>\w+)"
| table date,swift,source,uetr,status,loglevel
I need help with the reconciliation part, way to compare both the records and see if there was a corresponding entry for each ToSwift/FromIIB entry with the same UETR number.
See if this helps.
| dbxquery connection=CONN query="select RECID from Schema.table" shortnames=true
| rex field=RECID "(?<date>\d+)#(?<swift>\w+)#(?<source>\w+)#(?<uetr>\w+\-+\w+\-+\w+\-+\w+\-+\w+)#(?<status>\w+)#(?<loglevel>\w+)"
| fields date,swift,source,uetr,status,loglevel
| stats values(source) as sources, values(*) as * by uetr
| where mvcount(sources) > 1
See if this helps.
| dbxquery connection=CONN query="select RECID from Schema.table" shortnames=true
| rex field=RECID "(?<date>\d+)#(?<swift>\w+)#(?<source>\w+)#(?<uetr>\w+\-+\w+\-+\w+\-+\w+\-+\w+)#(?<status>\w+)#(?<loglevel>\w+)"
| fields date,swift,source,uetr,status,loglevel
| stats values(source) as sources, values(*) as * by uetr
| where mvcount(sources) > 1