How to edit my search to calculate the time difference between two events?

[vikramyerneni]

Hello Splunk'all,

I am trying to derive a simple chart from the data I got here within a Splunk Index. The data consists of epoch time (field name is "transactiontime") and the transaction values. The transactions contains success and failures and every transaction has an epoch timestamp for the transaction occurred.
I am simply trying to create a graph with the data for the time between every failure. For example: in a day, if there are two failures, one at 3 AM and one at 8 PM, I am trying to create a graph and show the time between these two failures (which is 17 Hrs). SO I took some help from a colleague of mine and got to this stage (please see below) and I am able to take two transactions into one field, but the time difference between those epoch values of two transactions are showing wrong.
Please see the search below and let me know for any missing logic I need to incorporate here:

index="dynatrace" transactionname="*"
sort _time| 
search tpf > 0 |
eval cvtime=strftime(transactiontime/1000, "%H:%M:%S %d-%m-%Y") |
transaction host maxevents=2 |
eval mdiff=round(duration)|
table mdiff,cvtime,ttime

Thanks
Vikram Y

[sundareshr · Accepted solution]

Try this

*UPDATED*

    index=dynatrace "failed events" transactioname=* |  stats earliest(_time) as start latest(_time) as end by host transactionname | eval duration=tostring(round(end-start, 0), "duration")

[vikramyerneni]

Actually I forgot to notice this before:
eval delta=tostring(_time-prevtime, "duration") is not outputting any value.. Looking more into it.

Thanks
Vikram Yerneni

[sundareshr]

Try this runanywhere sample

index=_internal | streamstats window=1 current=f earliest(_time) as nextTime by host sourcetype | eval duration=nextTime-_time | table _time nextTime duration

[vikramyerneni]

Hey Sundar, I was off for last two days. Let me validate it and will get back to you.
Thanks man..

[vikramyerneni]

Hi Sundar,

Yes the "runanywhere sample" worked with output (Duration as "0"). However the earlier query you provided actually is not providing output to eval delta=tostring(_time-prevtime, "duration") |. I do see the logic here taking the previous provided time and subtracting it from _time (current time) which will give the time in between these two events.
Not sure why I did multiple combinations but still its not outputting it.

Any thoughts here..?

Thanks
Vikram Y

[sundareshr]

Try the updated search..

[vikramyerneni]

Same response Sundar. Still its showing "Delta" field with blank values. I cant upload a screenshot to this ticket yet as I dont have enough Karma points..

Thanks
Vikram Y

[sundareshr]

What do you get when you run this

index=dynatrace "failed events" | reverse | streamstats window=1 current=f earliest(_time) as prevTime by host transactionname | table host transactionname  _time prevTIme

[sundareshr]

Lets try a different approach

index=dynatrace "failed events" transactioname=* |  stats earliest(_time) as start latest(_time) as end by host transactionname | eval duration=tostring(round(end-start, 0), "duration")

[vikramyerneni]

Now it worked.. "Reverse" made differnece.. Plus one thing I noticed is the query is Case-sensitive.. Atleast now I am getting right data into the Statistics page..
Let me validate the data and will get back to you soon Sundar..
Thanks man..

[sundareshr]

Awesome. Once validated, please accept/upvote the answer

[sundareshr]

@vikramyerneni, just add fillnull value=0 delta that will fill null values with 0, in your case, the first row

[vikramyerneni]

Alright the data looks good.
One last point I noticed here is the logic provided here will take the first _time and subtracts from the next one and because of that the first column in the result the "delta" output is blank which dosen't make the visualization graphs a blank.
Is there a way to remove this first line from the output or fill it with "0" for any blank values?
Let me know sir..

Thanks
Vikram Yerneni

[vikramyerneni]

Absolutely sir.. will do..
Thanks again dude..

[vikramyerneni]

Hi sundar,
I do see your logic here. You took the "_time" and calculated the output based on the parameter we need ("failed events"in this case). I am getting the full list individually now in the output. Thats really good..
I am actually trying to achieve to get the time difference between two individual events and put them into the Visualization in Splunk.

Thanks
Vikram Y