topic calculate sum of duration between events in Splunk Search

calculate sum of duration between events

ips_mandar — Fri, 25 Oct 2019 12:20:54 GMT

I have events like below

2019-10-21 04:17:54.968, rev=true
2019-10-21 04:17:55.968, rev=true
2019-10-21 04:17:56.968, rev=false
2019-10-21 04:17:57.968, rev=false
2019-10-21 04:17:58.968, rev=true
2019-10-21 04:17:59.968, rev=true
2019-10-21 04:18:00.968, rev=true

Here I want to calculate duration starts from rev=true till rev=false and then sum these duration. from above events I would like to calculate duration from 2019-10-21 04:17:54.968 till 2019-10-21 04:17:56.968 and again from 2019-10-21 04:17:58.968 till
2019-10-21 04:18:00.968. I tried transaction command but unable to succeed .
kindly help.

Re: calculate sum of duration between events

to4kawa — Fri, 25 Oct 2019 14:51:58 GMT

| stats count
 | eval raw="2019-10-21 04:17:57.968, rev=false
 2019-10-21 04:17:54.968, rev=true
 2019-10-21 04:17:55.968, rev=true
 2019-10-21 04:17:56.968, rev=false
 2019-10-21 04:17:57.968, rev=false
 2019-10-21 04:17:58.968, rev=true
 2019-10-21 04:17:59.968, rev=true
 2019-10-21 04:18:00.968, rev=true"
 | makemv delim="
 " raw
 | mvexpand raw
 | rex field=raw "^(?<time>[^,]+), rev=(?<rev>\w+)"
 | eval _time=strptime(time,"%Y-%m-%d %H:%M:%S.%3Q")
 | table _time rev
 `comment("this is sample data")`
 | streamstats count(eval(rev=="true")) as count  max(_time) as new min(_time) as old  reset_after="("rev==\"false\"")"
 | eventstats count as event_count
 | streamstats count as stream_count
 | eval duration = if(rev == "false" OR event_count==stream_count , new - old , NULL)

Hi, this is sample query.
How about it?

If you have multiple sources:

| stats count
| eval raw="2019-10-21 04:17:54.968, rev=true
2019-10-21 04:17:55.968, rev=true
2019-10-21 04:17:56.968, rev=false
2019-10-21 04:17:57.968, rev=false
2019-10-21 04:17:58.968, rev=true
2019-10-21 04:17:59.968, rev=true
2019-10-21 04:18:00.968, rev=true"
| eval source="sourceA
sourceB
sourceC"
| makemv delim="
" source
| mvexpand source
| makemv delim="
" raw
| mvexpand raw
| rex field=raw "^(?<time>[^,]+), rev=(?<rev>\w+)"
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S.%3Q")
| table _time rev source
 `comment("this is sample data")`
| sort 0 source _time
| streamstats count(eval(rev=="true")) as count  max(_time) as new min(_time) as old  reset_after="("rev==\"false\"")" by source
| eventstats count as event_count by source
| streamstats count as stream_count by source
| eval duration = if(rev == "false" OR event_count==stream_count , new - old , NULL)

If you put the source on the table, it will move.

Re: calculate sum of duration between events

woodcock — Sat, 26 Oct 2019 00:45:25 GMT

Like this:

| makeresults 
| eval raw="_time=2019-10-2T04:17:54.968,rev=true _time=2019-10-21T04:17:55.968,rev=true _time=2019-10-21T04:17:56.968,rev=false _time=2019-10-21T04:17:57.968,rev=false _time=2019-10-21T04:17:58.968,rev=true _time=2019-10-21T04:17:59.968,rev=true _time=2019-10-21T04:18:00.968,rev=true" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| fields - _time 
| kv 
| eval _time = strptime(time, "%Y-%m-%dT%H:%M:%S") 
| sort 0 - _time 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| streamstats count(eval(rev="false")) AS sessionID 
| eventstats last(rev) AS first_rev first(rev) AS last_rev BY sessionID
| search first_rev = "true"
| stats min(_time) AS _time range(_time) AS duration BY sessionID
| fieldformat duration = tostring(duration, "duration")

Re: calculate sum of duration between events

ips_mandar — Mon, 28 Oct 2019 06:06:26 GMT

Thanks @to4kawa. It is working if I separately mention source but I want to work it for all source.
so I tried ... | streamstats count(eval(rev=="true")) as count max(_time) as new min(_time) as old reset_after="("rev==\"false\"")" global=false by source...
but it is not working as expected .Please help here.

Re: calculate sum of duration between events

arjunpkishore5 — Tue, 29 Oct 2019 10:52:23 GMT

If I understand your question right, this is fairly simple, try this

your base search
| sort _time
| eval row_num=1
| streamstats sum(row_num) as row_num by rev reset_on_change=true
| delta _time as timediff
| eval timediff=if(row_num==1, 0, timediff)
| streamstats sum(timediff) as timediff by rev reset_on_change=true
| streamstats max(timediff) as max_timediff by rev reset_on_change=true
| where timediff=max_timediff and rev=="true"
| stats sum(timediff)

Let me know if this works for you. If yes, please upvote and mark as answer

Cheers