I have events like
Event EndDateTime
Launch 2017-05-16 13:00:00
.
.
.
Open 2017-05-16 13:00:30
I want to subtract time between these two events.
I want to implement something like
index="myindex" sourcetype="mysourcetype" | transaction host startswith="Launch" endswith="Open"|convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime)| eval difference=[subtract EndDateTime where Event=Open - EndDateTime where Event=Launch| chart avg(difference)
I just can't understand how can I work with the eval part about calculating difference.
So I basically did this and got what I wanted:
transaction host startswith="Launch" endswith="Open" | streamstats sum(TimeSec) as dur window=1 |
Thank you for your support. I had been such a stupid to not observe this earlier.
So I basically did this and got what I wanted:
transaction host startswith="Launch" endswith="Open" | streamstats sum(TimeSec) as dur window=1 |
Thank you for your support. I had been such a stupid to not observe this earlier.
@pranaynanda - I've converted your comment to an answer. Thanks for posting it.
Please accept your answer so the question will show as closed.
O I basically did this and got what I wanted:
transaction host startswith="Launch" endswith="Open" | streamstats sum(TimeSec) as dur window=1 |
Thank you for your support. I had been such a stupid to not observe this earlier.
You can try creating a TRANID manually and get the difference -
index="myindex" sourcetype="mysourcetype" "Launch" OR "Open" | eval TRANID=if(like(EVENT,"%Launch%"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"%Launch%"),EndDateTime,0) | eval end_time=if(like(EVENT,"%Open%"),EndDateTime,0) | stats sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time=start_time
Did not work! 😕
It says: Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr]).
My bad, in the last eval mistakenly gave "=" instead of "-"
index="myindex" sourcetype="mysourcetype" "Launch" OR "Open" | eval TRANID=if(like(EVENT,"%Launch%"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"%Launch%"),EndDateTime,0) | eval end_time=if(like(EVENT,"%Open%"),EndDateTime,0) | stats list(EVENT) as EVENTS,sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time-start_time
It only returns all values TRANID, start_time, end_time and diff as '0'
Can you provide few sample data for a single transaction?
Launch 5/18/2017 14:00:37 14 5/18/2017 14:01 2.98235E+11 2.98331E+11 0.51 30.87 30865.67
Login 5/18/2017 14:01:34 14 5/18/2017 14:03 2.98401E+11 2.98741E+11 1.74 104.41 104412.77
Search 5/18/2017 14:04:08 14 5/18/2017 14:04 2.98854E+11 2.98884E+11 0.17 10.24 10244.62
CheckOut 5/18/2017 14:04:48 14 5/18/2017 14:04 2.98969E+11 2.98977E+11 0.05 2.71 2713.89
Expand 5/18/2017 14:06:47 14 5/18/2017 14:07 2.99316E+11 2.99476E+11 0.92 54.97 54971.75
LaunchApp 5/18/2017 14:08:36 14 5/18/2017 14:20 2.99636E+11 3.01742E+11 12.01 720.87 720872.82
Open 5/18/2017 14:21:32 14 5/18/2017 14:31 3.01903E+11 3.03665E+11 10.05 603.24 603235.27
The Stop Date/Time column is extracted in Splunk as EndDateTime.
I tested with your data this way and it is working for me this way -
basesearch | rename "Stop Date/Time" as EndDateTime,"Test Name" as EVENT | eval EndDateTime=round(strptime(EndDateTime,"%m/%d/%Y %H:%M")) | eval TRANID=if(like(EVENT,"Launch"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"Launch"),EndDateTime,0) | eval end_time=if(like(EVENT,"Open"),EndDateTime,0) | stats list(EVENT) as EVENTS,sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time-start_time
Thanks for your support. I just relaized that when I have the RunTime in seconds for each event, I can also get a sum of all of them by each Transaction. The thing is that sum(TimeSec) add all of them where I want it by transaction.
looks like you are looking for the duration between events
the "duration" field is extracted with the transaction command
you can just | table duration after your transaction command and you can see the "difference in time"
hope i understand your question correctly
The _time and EndDateTime values are not same. duration won't work in that case. Also, I did try what you're telling before posting this question and there were differences in answers which makes sense as _time did not add the time taken by last event in duration.
The transaction command should already be giving you duration field, and it would be correct as long as your _time field was extracted based of values of EndDateTime field (both _time and EndDateTime values are same).
That's the thing. They are not.