Splunk Enterprise Security

How to calculate for what duration the status was in a particular state

ak9092
Path Finder

Hi,

I have a transaction that goes through multiple Status before its completed.

Now the challenge I am facing here is , one status can be mapped multiple time before the transaction is completed and in some cases the same status can keep repeated until its move to the next state -

For example ,my logs look something like below for one transaction ID -

(Note-there can be many more statuses other than the one's below)

2020-08-27 08:00:40.000, ID="20",  STATUS="CREATE"

2020-08-27 08:01:11.000, ID="20",  STATUS="POST"

2020-08-27 08:01:42.000, ID="20",  STATUS="POST"

2020-08-27 08:02:24.000, ID="20",  STATUS="POST"

2020-08-27 08:03:46.000, ID="20",  STATUS="REPAIR"

2020-08-27 08:03:56.000, ID="20",  STATUS="PENDING"

2020-08-27 08:04:00.000, ID="20",  STATUS="UPDATE"

2020-08-27 08:04:12.000, ID="20",  STATUS="UPDATE"

2020-08-27 08:04:30.000, ID="20",  STATUS="POST"

2020-08-27 08:04:46.000, ID="20",  STATUS="COMPLETE"

2020-08-27 08:04:56.000, ID="20",  STATUS="COMPLETE"

Now , What i want to do is calculate the total duration of time a transaction spent in a particular status.

So the final results should look something like below - 

ID STATUS max(_time) duration (sec)

20CREATE2020-08-27 08:00:40.48731
20POST2020-08-27 08:02:24.265155
20REPAIR2020-08-27 08:03:46.52910
20PENDING2020-08-27 08:03:56.0974
20UPDATE2020-08-27 08:04:12.71530
20POST2020-08-27 08:04:30.36616
20COMPLETE2020-08-27 08:04:56.517 

 

As of now, with below query I am able to map the status according to time but the duration is not being calculated accurately. Can someone please help me figure this out.

my search ... | sort 0 _time
| streamstats current=false last(STATUS) as newstatus by ID
| reverse
| streamstats current=false last(_time) as next_time by ID
| eval duration=next_time-_time
| reverse
| streamstats count(eval(STATUS!=newstatus)) as order BY ID | stats max(_time) as _time, sum(duration) as "duration(sec)" BY ID order STATUS

Thanks in advance.

 

Labels (1)
0 Karma
1 Solution

thambisetty
Super Champion

had to use sort many times.

| stats count as count  by _time, ID,STATUS
| streamstats count   reset_on_change=true  by ID, STATUS
| eventstats max(count) as max_count by ID,STATUS
| where (count=max_count) OR (count=1)
| sort 0 - _time
| streamstats last(_time) as prev_time by ID current=false
| eval duration=prev_time-_time, time=_time
| sort 0 _time
| streamstats sum(duration) as sum_duration reset_on_change=true by ID, STATUS
| sort 0 - _time
| dedup ID,STATUS consecutive=true
| sort 0 _time 
| table ID STATUS _time sum_duration
| eval sum_duration=if(STATUS=="COMPLETE","",sum_duration)
————————————
If this helps, give a like below.

View solution in original post

thambisetty
Super Champion

had to use sort many times.

| stats count as count  by _time, ID,STATUS
| streamstats count   reset_on_change=true  by ID, STATUS
| eventstats max(count) as max_count by ID,STATUS
| where (count=max_count) OR (count=1)
| sort 0 - _time
| streamstats last(_time) as prev_time by ID current=false
| eval duration=prev_time-_time, time=_time
| sort 0 _time
| streamstats sum(duration) as sum_duration reset_on_change=true by ID, STATUS
| sort 0 - _time
| dedup ID,STATUS consecutive=true
| sort 0 _time 
| table ID STATUS _time sum_duration
| eval sum_duration=if(STATUS=="COMPLETE","",sum_duration)
————————————
If this helps, give a like below.

View solution in original post

ak9092
Path Finder

@thambisetty this helps.. Thank You!!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!