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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

ak9092
Path Finder

@thambisetty this helps.. Thank You!!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...