Splunk Search

How to find duration between multiple timestamps of different users?

limalbert
Path Finder

Hi all,

The boundary of the logs: date and user. Total logs is more than 1000 logs.

  1. How should I list the date? I am thinking to use stats and list the timestamps per date and user. User will have multiple timestamps, varies from 2 to 10 timestamps per day. Sample format like below. currentDate  User    list(time) 3/30/17    user1   9:00:00, 10:00:00, 11:00:00 3/30/17    user2   9:00:00, 12:00:00, 14:00:00 3/31/17    user1   7:00:00, 14:00:00, 19:00:00 3/31/17    user2   13:00:00, 17:00:00, 20:00:00 ...
  2. Then, on a new column, find duration of the first and the second timestamps, second to third timestamps, etc. Sample format like below. currentDate  User   list(time)            Duration in hour 3/30/17    user1   9:00:00, 10:00:00, 11:00:00      1, 1 3/30/17    user2   9:00:00, 12:00:00, 14:00:00      3, 2 3/31/17    user1   7:00:00, 17:00:00, 19:00:00      10, 2 3/31/17    user2   13:00:00, 17:00:00, 20:00:00      4, 3 ...
  3. Lastly, count users who have duration of 10 hours.
0 Karma
1 Solution

somesoni2
Revered Legend

Assuming you got field _time with full timestamp and field User, give this a try. Query 3 before last where clause actually gives data for all three. You just need to add appropriate table command afterwards.

For 1

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| stats list(currentTime) as "List Times" by currentDate User

For 2

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" by currentDate User

For 3

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" sum(duration) as "Total Duration"  by currentDate User | where 'Total Duration'>10 
| stats count

View solution in original post

0 Karma

somesoni2
Revered Legend

Assuming you got field _time with full timestamp and field User, give this a try. Query 3 before last where clause actually gives data for all three. You just need to add appropriate table command afterwards.

For 1

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| stats list(currentTime) as "List Times" by currentDate User

For 2

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" by currentDate User

For 3

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" sum(duration) as "Total Duration"  by currentDate User | where 'Total Duration'>10 
| stats count
0 Karma

limalbert
Path Finder

Hi,

Do you mind explaining the sort and streamstats that you did there? It works, but I still don't understand what you did.

Thanks!

0 Karma

somesoni2
Revered Legend

The sort command does sort the event based on specified field, so for every day, every users, it'll list the events in ascending order of time for that day. This is required so that in streamstats, I would be able to grab the value of timestamp of first event in the second event (you can just see what it does by executing search before streamstats, see how the data looks and then add streamstats command). Basically if you're data was like this (after sort)

_time user
00:00 A
01:00 A
02:00 A

Steamstats will give output like this (adding a new column to result set

_time user prev_time
00:00 A <<it will be blank/null>>
01:00 A 00:00
02:00 A 01:00
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...