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

[redlose]

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

[lguinn2 · Accepted solution]

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]

Hey Iguinn

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

[sundareshr]

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"