topic Re: How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)? in Splunk Search

How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)?

iamsplunker — Thu, 05 Aug 2021 22:13:08 GMT

Hello Splunk Community

I'm working on a SPL to give _time difference of list of eventTypes as per the algorithm. Currently I'm using the below query.

index=apple source=datapipe AccountNumber=*

eventType=newyork OR

eventType=california OR

eventType=boston OR

eventType=houston OR

eventType=dallas OR

eventType=austin OR

eventType=Irvine OR

eventType=Washington OR

eventType=Atlanta OR

eventType=San Antonio OR

eventType=Brazil OR

eventType=Mumbai OR

eventType=Delhi OR

|fieldformat _time=strftime(_time,"%m/%d/%Y%I:%M:%S %p")

|sort by AccountNumber,_time

|streamstats range(_time) as diff window=2

|eval DifferenceInTimeByEventtime=strftime(diff,"%M:%S")

|table AccountNumber eventType _time DifferenceInTimeByEventtime

The query is working..However I need the time difference as per the algorithm. NOT ONLY as per the previous event .The algorithm is as follows

A eventType=newyork

B eventType=california B-A

C eventType=boston C-B

D eventType=houston D-C

E eventType=dallas E-D

F eventType=dallas F-D

G eventType=Irvine G-E

H eventType=Irvine H-F

I eventType=Atlanta I-H

J eventType=San Antonio J-I

K eventType=San Antonio K-I

L eventType=Mumbai L-I

M eventType=Delhi M-I

I'm looking for a

_time difference according to the algorithm above
Add Avg,Max,Min column to the search

I would appreciate if there is a query optimization

Thanks in Advance.

Re: How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)?

ITWhisperer — Thu, 05 Aug 2021 22:47:29 GMT

Why isn't I I-G and L L-J and M M-L as these would seem to fit a pattern or are these completely arbitrary which would make optimising the search rather difficult?

Re: How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)?

iamsplunker — Thu, 05 Aug 2021 23:18:43 GMT

@ITWhisperer : That's an algorithm .. and need to calculate the difference in that format. I'm thinking ..May be writing a sub search for each event type and give the logic for the calculation may work?

Re: How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)?

ITWhisperer — Fri, 06 Aug 2021 07:45:44 GMT

Try a case function e.g. case(row="B", timefromB-timefromA,row="C",timefromC-timefromB,...) where timefrom can be mvindex of all the times e.g. timefromA is mvindex(times,0). You can generate times with eventstats list(_time) as times

Re: How to find the _time difference in a list of eventTypes by algorithm (non-sequencial order)?

iamsplunker — Tue, 24 Aug 2021 22:43:28 GMT

@ITWhisperer :

I'm able to write the query for the difference ..however I'm not able to map the difference with _time. Is this something you could help?

I used the below query to get the difference as per the algorithm (D1,D2,D3 .. are difference, E as EventType T as _time)

| eval T2=(mvindex(T,2))
| eval T3=(mvindex(T,3))
| eval D2=T2-T1
| fieldformat D2= strftime(D2,"%M:%S")

| eval T4=(mvindex(T,4))
| eval T5=(mvindex(T,5))
| eval D3=T3-T2
| fieldformat D3= strftime(D3,"%M:%S")

---
| table AccountNumber E T D1 D2 D3 D4 D5

My resultes are showing like this

AccountNumber E T D1 D2 D3 D4 D5

123456789 NewYork 1/1/2021:12:30 30:00 30:00 30:00 30:00 30:00
California 1/1/2021:01:00
Boston 1/1/2021:01:30
Houston 1/1/2021:02:00
Dallas 1/1/2021:02:30

I do not want the D1,D2,D3,D4,D5 Columns .. I want Difference Column Mapped with T (side by side)along with AccountNumber E T. Please help