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.
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
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")
| 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.
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.