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!
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
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!
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 😞
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
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!
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
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.
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