How to edit my search to find orphaned transactions older than 1 minute?

[recurse]

Hello. I have a search that looks for orphaned transactions, as follows:

[...main search...]
| transaction request_id keeporphans=true maxspan=1m 
| search _txn_orphan=1

It currently works, but sometimes yields false negatives if a transaction happens to be in the middle of processing within the last minute. I'm trying to amend my search to only return orphans that occurred at least a minute ago, but am not having any luck:

[...main search...]
| transaction request_id keeporphans=true maxspan=1m 
| search _txn_orphan=1 latest=-1m

Anyone have any ideas on how to accomplish this? I've tried several variations of date math without any luck.

[somesoni2 · Accepted solution]

Try like this

Updated comparison operator of where clause

[...main search...]
 | transaction request_id keeporphans=true maxspan=1m 
 | where _txn_orphan=1 AND _time<relative_time(now(),"-1m@m")

[recurse]

Thanks for the reply - I hadn't tried a variation with the '@m' suffix. Unfortunately, it excludes all of my results, including those which are older than 1m ago. Baffling.

[recurse]

Also, I switched > to < to align with what I'm looking for, still no luck.

[somesoni2]

The comparison operator should've been <, updated the same. Try with just -1m instead of -1m@m. Let us know if it doesn't work and what the problem is?

[recurse]

Still no luck. I find 4 orphaned transactions (from over an hour ago) without the additional constraint and none with it. Let me know if there's any additional information I can provide.

[somesoni2]

I guess we need more information on how things are logged (with samples) in your data. How can we differentiate between an orphan and in-progress transaction? I believe that's where you're getting false +ve and that's what we need to fix.

[recurse]

Oh, I missed your addition of the 'where' clause in the edit. I added that and it's working great. Thank you!