Splunk Search

timespan between earliest and latest per day per user

zendataCH
Explorer

Hi all,
I am looking for a solution to show for every day of a week the time of the first activity of a user and the time of its last activity (and the time span between them).
I wanted to use transactions, but from what I understood, I need to know the exact event name to use it... but without transaction it does not seem to work:

source="WinEventLog:Security" | eval User = if(isnull(Account_Name), User_Name, mvindex(Account_Name,1)) | stats earliest(_time) as start, latest(_time) as stop by User

any advice or help would be very appreciated,

Steven

0 Karma
1 Solution

Unhacker
Explorer

I could be misinterpreting you zendataCH, but this is what I came-up with - for data, I used a source that contains its own 'time' field, and found it convenient to operate on those instead. If your events contain their own timestamp, that might work for you too.

I used the 'min' and 'max' stats commands, but grouped them by both the username /and/ the day of the week.

Sample Syntax:

sourcetype=juniper | stats min(time), max(time) BY user,date_wday

This returns approximately the following:

ALTOID thursday 2014-06-19 14:14:00 2014-06-19 20:23:06
ANYPAY monday 2014-06-23 06:40:17 2014-06-23 07:03:05
APEFAN thursday 2014-06-19 14:10:44 2014-06-19 14:22:31
ANKLES friday 2014-06-20 09:59:33 2014-06-20 15:58:59
(...)

I haven't figured-out calculating the difference in time between the two events yet, but I bet an eval is the answer for that.

Your mileage, of course, may vary.

Good luck!

View solution in original post

mike_lebrun
Explorer

The "range" command should give you the difference between the earliest and latest times.

sourcetype=juniper | stats min(time), max(time), range(time) BY user,date_wday

Unhacker
Explorer

I could be misinterpreting you zendataCH, but this is what I came-up with - for data, I used a source that contains its own 'time' field, and found it convenient to operate on those instead. If your events contain their own timestamp, that might work for you too.

I used the 'min' and 'max' stats commands, but grouped them by both the username /and/ the day of the week.

Sample Syntax:

sourcetype=juniper | stats min(time), max(time) BY user,date_wday

This returns approximately the following:

ALTOID thursday 2014-06-19 14:14:00 2014-06-19 20:23:06
ANYPAY monday 2014-06-23 06:40:17 2014-06-23 07:03:05
APEFAN thursday 2014-06-19 14:10:44 2014-06-19 14:22:31
ANKLES friday 2014-06-20 09:59:33 2014-06-20 15:58:59
(...)

I haven't figured-out calculating the difference in time between the two events yet, but I bet an eval is the answer for that.

Your mileage, of course, may vary.

Good luck!

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...