Splunk Search

How to create a time average of a timeframe overarching 00:00?

redlose
New Member

Hi everybody

I'm going crazy because of a "timeproblem" which sounds not hard to handle, but i don't get it... My hopes are one of you can help me.

I have plenty of User-Logs with a timestamp. The goal is to find suspicious user behavior like user-actions during a specific time.

First I have to define the "normal" time a user is working. This time-interval has to be between 19:00 day1 and 07:00 day2.
My problem here is that the command "earliest" is not really useful, because if someones first action is 23:00 and the last action is 01:00 it will give me 01:00 as an earliest-output and 23:59 as his latest.

Example:

Day1: first action-19:00     last action-2030
Day2: first action-20:00     last action-2200
Day3: first action-23:00     last action-0100
Day4: first action-01:00     last action-0130

The "normal" first action would be the average of the first action: 21:45
The "normal" last action would be the average of the last action: 23:30

My first problem is to define a time-interval which starts at 19:00 day1 and ends 07:00 day2
My second problem is that i don't know how to write the search to get a list like the one in the example.

Thanks for answering,
redlose

0 Karma
1 Solution

lguinn2
Legend

The timestamp, _time, always contains the full epoch time. So you aren't limited to just the hours and minutes.
It would be really helpful to actually see your search, but I would try this:

yoursearchhere earliest=-7d@d+19h latest=@d+1h
| addinfo
| eval Day = ceiling((_time - info_min_time)/86400) 
| stats earliest(action) as "First Action" earliest(_time) as et latest(action) as "Last Action" latest(_time) as lt by user Day
| eval FirstTime = strftime(et,"%x %X")
| eval LastTime  = strftime(lt,"%x %X")
| table user Day "First Action" FirstTime "Last Action" LastTime

There are other ways to do this, but I think this is pretty clean. Note that it depends on setting the earliest time of the search so that it starts at 19:00 on some day. I set it for 19:00 a week ago, but you can choose whatever day you want.

View solution in original post

lguinn2
Legend

The timestamp, _time, always contains the full epoch time. So you aren't limited to just the hours and minutes.
It would be really helpful to actually see your search, but I would try this:

yoursearchhere earliest=-7d@d+19h latest=@d+1h
| addinfo
| eval Day = ceiling((_time - info_min_time)/86400) 
| stats earliest(action) as "First Action" earliest(_time) as et latest(action) as "Last Action" latest(_time) as lt by user Day
| eval FirstTime = strftime(et,"%x %X")
| eval LastTime  = strftime(lt,"%x %X")
| table user Day "First Action" FirstTime "Last Action" LastTime

There are other ways to do this, but I think this is pretty clean. Note that it depends on setting the earliest time of the search so that it starts at 19:00 on some day. I set it for 19:00 a week ago, but you can choose whatever day you want.

redlose
New Member

Hey Iguinn

Wow, that was very fast!! And it worked 🙂 Thanks a lot for helping me!!

0 Karma

sundareshr
Legend

Try this

index=foo earliest=-1d@d+19h latest=@d+7h | stats earliest(_time) as firstaction latest(_time) as lastaction by user | convert ctime(*action) timeformat="%I%M"
0 Karma
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 ...