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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...