reconciliation of field value from Splunk DB connect query

[ashrafsj]

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.

[richgalloway · Accepted solution]

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