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

[iamsplunker]

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

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

I would appreciate if there is a query optimization

Thanks in Advance.

[ITWhisperer]

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?

[iamsplunker]

@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?

[ITWhisperer]

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

[iamsplunker]

@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)

| sort by _time,AccountNumber
| stats list(eventType) as E list(_time) as T by AccountNumber
| eval T0=(mvindex(T,0))
| eval T1=(mvindex(T,1))
| eval D1=T1-T0
| fieldformat D1= strftime(D1,"%M:%S")

| 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