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
I would appreciate if there is a query optimization
Thanks in Advance.
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?
@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?
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
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