I am probably making this more confusing for myself than it needs to be, but its a simple concept. Here is the scenario. If an invite is emailed and no confirmation is received within 1 day from email being sent then it is "In Progress" otherwise its a failure. Please help formulate, basically if no confirmation is received within 1 day its in progress. I would like to keep my times all in epoch. Thank You in advance
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")