We need to find out the Ids along with DispatchTime which are not dispatched in correct sequence.
ID DispatchTime
1 05/22/17 02:13:01
1 05/22/17 02:17:13
1 05/22/17 02:15:33 --- It is dispatched before 2nd event dispatch,
1 05/22/17 02:22:03
2 05/22/17 02:25:56
2 05/22/17 02:26:18
2 05/22/17 02:24:37 ------ it is dispatched before above 2 events.
3 05/22/17 02:31:19
3 05/22/17 02:50:03
3 05/22/17 02:53:29 ----- it is dispatched in wrong sequence
3 05/22/17 02:51:52
4 05/22/17 03:01:34
4 05/22/17 22:01:30
4 05/22/17 22:01:30
Like this:
Your Base Saerch
| eval time = "Dispatch Time"
| rex field=time mode=sed "s/://g"
| streamstats current=f last(time) AS prev_time BY ID
| eval Mispatch = if((time < prev_time), "TRUE", null())
| where isnotnull(Mispatch)
As far as your query is giving the above result in the same sequence, you can use streamstats to get the previous value of "Dispatch Time" and then compare that Previous Value should not be greater than current:
<Your Base Search>
| streamstats current=f global=f window=1 last("Dispatch Time") as Last_Dispatch_Time by ID
| eval status=if(isnull(Last_Dispatch_Time) OR Last_Dispatch_Time<='Dispatch Time',"Correct","Incorrect")
| table ID "Dispatch Time" Last_Dispatch_Time status
PS: isnull(Last_Dispatch_Time) is for the first event since it will not have any data.
Streamstats works based on how data is sorted, so make sure your base query is returning results in the sequence you expect (as mentioned in your question)
@srinivasup, besides the Dispatch Time do you have event time (i.e. _time) which confirms that Dispatch Times are out of sequence? Also what are the field Names for ID, Dispatch Time?
Hi,
After all calculation i have got this ouput, so now we need to compare Dispatch time sequence for each ID.
If dispatch time sequence is not in order, out of order id and dispatch time should be displayed.