Splunk Search

How to Calculate the total duration?

knalla
Path Finder

Hi, I'm trying to get the total duration of events  for each user from access logs with time gap. 

sample event:

_time user 

2021-06-30 00:00:26   user1

22021-06-30 01:00:26 user1

22021-06-30 01:00:26 user1

22021-06-30 01:20:26 user1

and then there are no events for 4 hours

 

22021-06-30 05:00:26 user1

22021-06-30 05:30:26 user1

22021-06-30 06:02:26 user1

I'm trying to calculate the total duration of day. Any ideas how to achieve this?

 

 

Labels (2)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@knalla 

You can get diff between events using this.

| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff

 

If you want to filter differences which are more than 3 hours then add this.

| where diff< (3*60*60)

 

You can validate your results by executing one by one step.

By adding below you can get total duration per day.

| bin _time span=d
| stats sum(diff) as total_duration by _time

 

This is full search. You can change as per your requirement.

YOUR_SEARCH
| fields _time user
| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff
| where diff< (3*60*60)
| bin _time span=d
| stats sum(diff) as total_duration by _time,user

 

My Sample Search :

| makeresults | eval _raw="time,user
2021-06-30 00:00:26,user1
2021-06-30 01:00:26,user1
2021-06-30 01:00:26,user1
2021-06-30 01:20:26,user1
2021-06-30 05:00:26,user1
2021-06-30 05:30:26,user1
2021-06-30 06:02:26,user1"| multikv forceheader=1
| eval _time= strptime(time,"%Y-%m-%d %H:%M:%S")
| fields _time user
| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff
| where diff< (3*60*60)
| bin _time span=d
| stats sum(diff) as total_duration by _time,user

 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...