Splunk Search

calculate sum of duration between events

ips_mandar
Builder

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.

0 Karma

arjunpkishore5
Motivator

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

0 Karma

woodcock
Esteemed Legend

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")
0 Karma

to4kawa
Ultra Champion
| 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.

ips_mandar
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...