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!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...