How to create a search with inner join subsearch with later time range?

[loudc95]

Hi Splunk Team,

I'm trying to create a query that uses the payment IDs from one table, and only keeps the payment IDs that have a completed status from another table.

The completed status can happen at a later date so I would like the subsearch to search within 10 days after the original search.

My query seems to work when I search for a specific ID in the subsearch, but when I remove it it returns no results.

I'm also open to not using a join/making this more efficient but I wasn't sure how else to do it!

auditSource="open-banking" auditType="PaymentResponse"
| fields detail.ecospendPaymentId, detail.amount
| convert num(detail.amount) as amount
| table detail.ecospendPaymentId, amount
| join type=inner detail.ecospendPaymentId [ search auditSource="open-banking-external-api" auditType="PaymentStatusUpdate" detail.status="Completed" latest=+10d 
| fields detail.paymentId
| rename detail.paymentId as "detail.ecospendPaymentId" ]
| dedup "detail.ecospendPaymentId"
| table "detail.ecospendPaymentId", amount

Thank you!

[gcusello]

Hi @loudc95,

One general hint: use join only when you haven't any other solution, Splunk isn't a DB!

Please try to think in a different way, using stats to correlate different searches, something like this

((auditSource="open-banking" auditType="PaymentResponse") OR (auditSource="open-banking-external-api" auditType="PaymentStatusUpdate" detail.status="Completed" latest=+10d ))
| rename 
   detail.amount AS amount 
   detail.paymentId AS paymentId
| stats 
   values(amount) AS amount 
   dc(auditSource) AS dc_auditSource 
   values(auditSource) AS auditSource 
   BY paymentId
| where dc_auditSource=2
| rename paymentId AS "detail.ecospendPaymentId"
| table "detail.ecospendPaymentId" amount

In addition, this approach hasn't the limitation of 50,000 results that subsearches have.

Then I hint to use always index in your searches: you'll have faster searches.

Ciao.

Giuseppe

[loudc95]

Hi Giuseppe,

Thanks for your fast response! Unfortunately this returns no results for me, and upon doing some digging there are now zero results from the second table (auditSource open-banking-external-api). Any ideas?

Thanks!

[loudc95]

This one worked:

((auditSource="open-banking" auditType="PaymentResponse") OR (auditSource="open-banking-external-api" auditType="PaymentStatusUpdate" detail.status="Completed" latest=+10d ))
| rename
detail.paymentId AS paymentId
detail.ecospendPaymentId AS paymentId
| convert num(detail.amount) as amount
| stats
values(detail.amount) AS amount
dc(auditSource) AS dc_auditSource
values(auditSource) AS auditSource
BY paymentId
| where dc_auditSource=2

but this is missing a lot of the paymentIds that should be in there 😞

[gcusello]

Hi @loudc95,

if the condition you need is that the paymentIds is in both the searches results, this is the condition, not missing results, if instead you want a larger condition (e.g. both the searches and only the first) you could modify the where condition like the following

((auditSource="open-banking" auditType="PaymentResponse") OR (auditSource="open-banking-external-api" auditType="PaymentStatusUpdate" detail.status="Completed" latest=+10d ))
| rename
   detail.paymentId AS paymentId
   detail.ecospendPaymentId AS paymentId
| convert num(detail.amount) as amount
| stats
   values(detail.amount) AS amount
   dc(auditSource) AS dc_auditSource
   values(auditSource) AS auditSource
   BY paymentId
| where dc_auditSource=2 OR (auditSource="open-banking" auditType="PaymentResponse")

Ciao.

Giuseppe

[loudc95]

Hi @gcusello ,

No I am looking for an inner join, but am definitely missing results. 

When I ran this search, I get 2 results:

(auditSource="open-banking" auditType="PaymentResponse") OR (auditSource="open-banking-external-api" auditType="PaymentStatusUpdate" detail.status="Completed" latest=+10d )
| eval id="12345"
| stats dc(auditSource) by id

But when I run the original search , with a where clause of id="12345", there is only the 1 result from open-banking and not open-banking-external-api.

Thanks!

[gcusello]

Hi @loudc95,

debug the two result you have to understand where is the problem:

maybe one result has some char in upprecase and you have to trasndorm both the fields in lower case

view the other fields in the events with two results, to understand where is the problem,

maybe you have to redefine the filtering criteria, I connot know you data, but anyway I hint you to understand and take my approach to use in the next searches, becsuase join (and transforms) is a command very slow and heavy for your infrastructure.

Ciao.

Giuseppe

[loudc95]

Hi @gcusello,

Figured it out! The stats values(detail.amount) AS amount was causing problems.

Unfortunately this seems to work for everything that has got the completed status in the same time range as the first search, but it isn't finding the ones that change status at a later date.

Again, when I search for the ID explicitly then it works, but without it it doesn't appear in the table.

[gcusello]

Hi @loudc95,

if it runs explicitly using the ID, you should debug the results of the second search because maybe there' something strange.

Ciao.

Giuseppe