I can see how a seemingly simple problem becomes confusing when you have multiple factors to consider. Let me try to understand the question with two different assumptions. If elapsed time since email_sent is the only factor, all you need to know is the function now(): | eval status = if(now() - email_sent > time_passed_no_confirmation, "fail", "in progress") However, I suspect that your problem is not as simple, because your data emulation includes another variable, confirmation_remains_null. I suspect that your use case calls for a 3-state outcome, fail, in progress, and completed, the "completed" state being reached when confirmation_remains_null is no longer "null" within 1 day. If this this the case, the following simulates the 3 possible states | makeresults count=3
| eval time_passed_no_confirmation=86400
| streamstats count
| eval email_sent = now() - count * 30000
| eval confirmation_remains_null=if(count==2, "received", "null")
``` calculate difference between now and email_sent, also check confirmation state ```
| eval status = case(now() - email_sent > time_passed_no_confirmation, "fail", confirmation_remains_null == "null", "in progress", true(), "confirmed") _time confirmation_remains_null count email_sent status time_passed_no_confirmation 2021-12-01 22:52:34 null 1 1638397953 in progress 86400 2021-12-01 22:52:34 received 2 1638367953 confirmed 86400 2021-12-01 22:52:34 null 3 1638337953 fail 86400
... View more
