working with timestamps and need to pull the records which are matching conditions

[srinivasup]

I wrote a Splunk search and it's giving my expected results:

index=main sourcetype="log" 
| rename SERVICE_ID AS SUB_SERVICEID, SERVER_NAME AS SUB_SERVER_NAME, SERVERTIME AS SUB_SERVERTIME, SERVER_TIMESTAMP AS SUB_SERVER_TIMESTAMP
| join type=inner appendcols TRACKINGID_TEXT [ search index=main sourcetype="log"
| rename SERVICE_ID AS PUB_SERVICEID, BUSINESSKEY_TEXT AS PUB_BUSINESSKEYTEXT, SERVER_NAME AS PUB_SERVER_NAME, SERVERTIME AS PUB_SERVERTIME, SERVER_TIMESTAMP AS PUB_SERVER_TIMESTAMP  ]
| rex field=BUSINESSKEY_TEXT (?\d+)\|(?\d+)\|(?.+)\|(?.{19})
| eval SUB_SERVER_TIMESTAMP=strftime(SUB_SERVER_TIMESTAMP ,"%m/%d/%y %H:%M:%S")| sort  SUB_SERVER_TIMESTAMP 
| eval PUB_SERVER_TIMESTAMP=strftime(PUB_SERVER_TIMESTAMP,"%m/%d/%y %H:%M:%S")| rename Shipment as sh SAPtime as st PUB_SERVER_TIMESTAMP as PST SUB_SERVER_TIMESTAMP as SST
| table Shipment SAPtime PUB_SERVER_TIMESTAMP SUB_SERVER_TIMESTAMP 

Output :

Here the question is that: sh field is the id, st is the dispatch time, and SST is the delivered time. Now we need identify the count of delivered sh id which are not in order they dispatched.

Example:
-First line has dispatched at 2017-03-29 19:11:17 and delivered at 03/30/17 07:41:30
-Third line has dispatched at 2017-03-29 19:11:20 and delivered at 03/30/17 07:41:51 (these two are proper because they followed the order they delivered.
-Second line has dispatched at 2017-03-29 19:11:24 and delivered at 03/30/17 07:41:36, here order is missed. We need to find the count of such events for each st*

[cmerriman]

i think streamstats might be what you need. something like below maybe

...|sort 0 + SST|streamstats window=1 current=f values(st) as previousST|fillnull previousST value=0|eval incorrectDispatch=if(st-previousST>0,0,1)|stats sum(incorrectDispatch) as incorrectDispatch by st

[srinivasup]

I have used below query

...| streamstats current=f window=1 values(SST) as prevFrom | where SST < prevFrom

This is work for individual events not for many events. any suggestio.

[srinivasup]

its not giving proper results any otherway

[srinivasup]

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

[cmerriman]

alright, i just plugged in all of those data points in a makeresults.

 | makeresults |eval data="order=1 id=1 dt=05/22/17 02:13:01,order=2 id=1 dt=05/22/17 02:17:13,order=3 id=1 dt=05/22/17 02:15:33,order=4 id=1 dt=05/22/17 02:22:03,order=1 id=2 dt=05/22/17 02:25:56,order=2 id=2 dt=05/22/17 02:26:18,order=3 id=2 dt=05/22/17 02:24:37,order=1 id=3 dt=05/22/17 02:31:19,order=2 id=3 dt=05/22/17 02:50:03,order=3 id=3 dt=05/22/17 02:53:29,order=4 id=3 dt=05/22/17 02:51:52,order=1 id=4 dt=05/22/17 03:01:34,order=2 id=4 dt=05/22/17 22:01:30,order=3 id=4 dt=05/22/17 22:01:30"|makemv data delim=","|mvexpand data|eval _raw=data|kv|rex field=data "dt=(?<dt>.*)"|eval _time=strptime(dt,"%m/%d/%y %H:%M:%S")|sort id +order | streamstats current=f window=1 values(_time) as prevST by id  |eval prevST=if(isnull(prevST),_time,prevST)| where _time >= prevST

I think if you add ...| streamstats current=f window=1 values(st) as prevST by id |eval prevST=if(isnull(prevST),_time,prevST)| where st >= prevST it should work. just make sure that st is in epoch and isn't a string.

[cmerriman]

sorry, i misread the original post, but i think i might understand it better now.

...|sort 0 sh + st | streamstats current=f window=1 values(SST) as prevFrom by sh | where SST < prevFrom

from there you can count up how many there are with |stats count