How to find the time taken by a field in certain time interval?

[dyapasrikanth]

I have set of events like below

SessionID="F4E22EFDB35791C879400BABAD77879C",TransactionID="9885533d-b9a3-48ba-a6a1-fefa2aca2c8d",TimeStamp="2016-12-05 21:08:51,560", EventType="ProductFulfilment", LogType="INFO " EventDetails="INFO : Product Fulfilment Acknowledgment : DB Operations - Start",User="2745", SearchSpace="123", OrderID="161205-PB031-00144", ProductType="P1"

SessionID="F4E22EFDB35791C879400BABAD77879C",TransactionID="9885533d-b9a3-48ba-a6a1-fefa2aca2c8d",TimeStamp="2016-12-05 21:09:43,356", EventType="ProductFulfilment", LogType="INFO " EventDetails="INFO: latest to be merged  Orders: [] marked to complete",User="2745", SearchSpace="123", OrderID="161205-PB031-00144", ProductType="P1"

SessionID="F4E22EFDB35791C879400BABAD77879C",TransactionID="9885533d-b9a3-48ba-a6a1-fefa2aca2c8d",TimeStamp="2016-12-05 21:10:43,356", EventType="ProductFulfilment", LogType="INFO " EventDetails="INFO: latest to be merged  Orders: [161205-PB031-00144, 161219-PB031-0000W] marked to complete",User="2745", SearchSpace="123", OrderID="161205-PB031-00144", ProductType="P1"

I have same type of events for other ProductType and OrderID. I am trying to find he time difference between first event and last event group by OrderID (which is unique) for a month

Finding last event is tough for me, OrderID should be in array [] ([161205-PB031-00144, 161219-PB031-0000W] ), which will occur only once in its life cycle. But i have another event which has OrderID as separate property.

Can anyone tell me how can i find the time taken by each Order in certain time interval?? I tried below one but it not working.

index="vss" source="/logs/app-event.log" EventType="ProductFulfilment" | transaction startswith="INFO : Product Fulfilment Acknowledgment : DB Operations - Start"  endswith="INFO: latest to be merged  Orders: [*"OrderID"*] marked to complete"  | transaction OrderID

[aaraneta_splunk]

@dyapasrikanth - Did the answer provided by niketnilay help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

[niketn]

While transaction might also work for you stats might work better in the above scenario...

To ensure that the transaction command is picing up the events (1) Filter only the events you need in the base query (2) Create transaction with all keys (ID fields) (3) Remove startswith and endswith criteria to see if events are getting correlated (4) Run the command for shorter duration (or turn on the argument keepevicted=true to the transaction).

index="vss" source="/logs/app-event.log" EventType="ProductFulfilment" EventDetails="INFO : Product Fulfilment Acknowledgment : DB Operations - Start" OR EventDetails="INFO: latest to be merged  Orders:*" OR EventDetails="INFO: latest to be merged  Orders:*"
| transaction SessionID TransactionID OrderID
| table _time, SessionID, TransactionID, OrderID, EventDetails, eventcount, duration

However, stats should suit your use case better so try something similar to the following instead (for a result similar to above transaction query):

index="vss" source="/logs/app-event.log" EventType="ProductFulfilment" EventDetails="INFO : Product Fulfilment Acknowledgment : DB Operations - Start" OR EventDetails="INFO: latest to be merged  Orders:*" OR EventDetails="INFO: latest to be merged  Orders:*"
| stats count as eventcount min(_time) as MinTime max(_time) as MaxTime values(EventDetails) as EventDetails by SessionID TransactionID OrderID
| eval duration=MaxTime-MinTime
| eval _time=MinTime
| table _time, SessionID, TransactionID, OrderID, EventDetails, eventcount, duration