How to write a condition for this?

[VijaySrrie]

Hi Team,

We have a field called Status=Start and Status=Success
OrderId is one field

When orderId has the Status=start and if there is no Status=Success for 10 mins it should be considered as failure
May i know how to write a condition for this?

[gcusello]

Hi @VijaySrrie,

please try something like this:

your search (Status=start OR Status=Success) earliest=-20m@m latest=@m
| stats 
   dc(Status) AS Status_count 
   values(Status) AS Status 
   earliest(_time) AS earliest
   latest(_time) AS latest
   BY OrderId
| where (Status_count=1 AND Status="Start" AND earliest>600) OR (Status_count=2 AND latest-earliest>600)

 Ciao.

Giuseppe

[bowesmana · Accepted solution]

So, if you are simple looking for whether there is a Status=Success within 10 minutes of start, use a simple option that just looks for all Status values in the last 10 minutes and if there is only start but no success, then you have a result

your search earliest=-10m@m latest=@m
| stats values(Status) as Statuses by OrderId
| where (Statuses="Start" AND Statuses!="Success")

It can get more nuanced if you need it to, but that should be good to start with 

[VijaySrrie]

Hi @bowesmana 

The query which you shared works fine.
I am bit confused about the timing 

This query should run 60 mins once.

cron_schedule = 01 * * * *
	dispatch.earliest_time = -60m
	dispatch.latest_time = now

May I know the correct earliest and latest time?

[bowesmana]

So if you intend to run the query once every 60 minutes and then look for an orderId with no success after 10 minutes, then the search has to be a little bit different.

For example, if you look at 10 minute periods 9:00, 9:10, 9:20... and there is a start at 9:09 and a success at 9:14, you need to look at the whole event stream as a stream.

Also, be aware that if you run the search at 10:00 am and there is a start at 9:59, but the success does not happen until 10:02, then you will not see the success in the search, but when you run the search at 11, you will not see the start, so you really need to run your search once every hour for 70 minutes.

The search would then be something like this, using streamstats to look at all 10 minute windows

your search 
| streamstats time_window=10m dc(Status) as Statuses by OrderId
| stats max(Statuses) as Statuses by user
| where Statuses!=2

Your earliest and latest times would be

earliest=-70m@m+1s
latest=@m

So, this looks for -69m and 59 seconds to now, just to handle the sliding window across the schedule.

Then it counts the unique values of Status for each OrderId - note, this assumes you only have a Start/Success pairing. It then finds out if you have 2 Statuses.

[bowesmana]

If you run that every minute, it will look back at the previous 10 minutes, however, it is sometimes good, in case of index lag, to run the search over a little older time period, e.g. to look back at -15 to -5 minutes ago.

earliest=-15@m latest=-5m@m