Splunk Search

reconciliation of field value from Splunk DB connect query

ashrafsj
Path Finder

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.

 

 

Labels (2)
Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...