Splunk Search

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

srinivasup
Explorer

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 :
alt text

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*

0 Karma

cmerriman
Super Champion

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
0 Karma

srinivasup
Explorer

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.

0 Karma

srinivasup
Explorer

its not giving proper results any otherway

0 Karma

srinivasup
Explorer

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

0 Karma

cmerriman
Super Champion

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.

0 Karma

cmerriman
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...